Chapter 8: Sorting, Searching, and Filtering

Working with DataView Objects in Code

The DataView object offers functionality similar to that of the DataTable object's Select method. Let's take a closer look at this functionality and compare it to the Select method as we go.

Creating DataView Objects

To use a DataView object to view data in a DataTable, you must associate it with a DataTable object. You can specify the DataTable that the DataView will use in one of two ways: by using the DataView object's Table property or by using the DataView object's constructor. The following code snippets are equivalent:

Visual Basic .NET

Dim tbl As New DataTable("TableName") Dim vue As DataView vue = New DataView() vue.Table = tbl vue = New DataView(tbl)

Visual C# .NET

DataTable tbl = new DataTable("TableName"); DataView vue; vue = new DataView(); vue.Table = tbl; vue = new DataView(tbl);

	If you set the DataView object's Table property to a DataTable, the DataTable must have its TableName property set to something other than an empty string (the default). This restriction is not enforced in the DataView object's constructor. I don't claim to know why.

The DataView object also has a constructor whose signature more closely matches the DataTable object's Select method. This more advanced constructor sets the Table, RowFilter, Sort, and RowStateFilter properties of the DataView in a single line of code. Thus, the following code snippets are equivalent:

Visual Basic .NET

Dim tbl As New DataTable("Customers") Dim dvrs As DataViewRowState dvrs = DataViewRowState.ModifiedOriginal Or DataViewRowState.Deleted Dim vue As DataView vue = New DataView vue.Table = tbl vue.RowFilter = "Country = 'USA'" vue.Sort = "City DESC" vue.RowStateFilter = dvrs vue = New DataView(tbl, "Country = 'USA'", "City DESC", dvrs)

Visual C# .NET

DataTable tbl = new DataTable("Customers"); DataViewRowState dvrs; dvrs = DataViewRowState.ModifiedOriginal Or DataViewRowState.Deleted; DataView vue; vue = new DataView; vue.Table = tbl; vue.RowFilter = "Country = 'USA'"; vue.Sort = "City DESC"; vue.RowStateFilter = dvrs; vue = new DataView(tbl, "Country = 'USA'", "City DESC", dvrs);

Using the RowStateFilter Property

The RowStateFilter property accepts values from the DataViewRowState enumeration. (See Table 8-1.) You can think of the enumeration as a combination of the RowState property of the DataRow object and the DataRowVersion enumeration.

The property acts as a dual filter. For example, setting the DataView object's RowStateFilter property to ModifiedOriginal means that only modified rows will be visible through the DataView and that you'll see the original values of those rows.

Table 8-1 DataViewRowState Enumerations

Value	Description
Added	Added rows are included.
CurrentRows	Nondeleted rows are included. (Default)
Deleted	Deleted rows are included.
ModifiedCurrent	Modified rows are included; current values are visible.
ModifiedOriginal	Modified rows are included; original values are visible.
None	No rows are included.
OriginalRows	Deleted, modified, and unmodified rows are included; original values are visible.
Unchanged	Unmodified rows are included.

Using the DataRowView Object

If you use the DataTable object's Select method and specify ModifiedOriginal, the method will return only modified rows. However, as you saw in the earlier code snippet illustrating the Select method, we still had to specify that we wanted to retrieve original values from the row in calls to the DataRow objects returned.

This extra step is not required when you use the DataView because the DataView returns data using its own specialized object: a DataRowView. The DataRowView offers much of the same functionality as the DataRow. It exposes a default Item property that you can use to access the contents of a column by supplying either a column name or the index of a column. You can examine and modify the contents of a row using the Item property, but only one version of the row's data is available through the DataRowView—the version you specify in the DataView object's DataRowVersion property.

The following code snippet shows how to use the DataView object to return a DataRowView object and how to use the DataRowView object to examine data from a row:

Visual Basic .NET

Dim tbl As New DataTable("Customers")  Dim vue As DataView vue = New DataView(tbl) Dim row As DataRowView = vue(0) Console.WriteLine(vue("CompanyName"))

Visual C# .NET

DataTable tbl = new DataTable("Customers");  DataView vue; vue = new DataView(tbl); DataRowView row = vue[0]; Console.WriteLine(vue["CompanyName"]);

If you find that the DataRowView object doesn't give you the power you need, you can use the DataRowView object's Row property to access the corresponding DataRow object.

Examining All Rows of Data Available Through a DataView

Using a DataView to access data in a DataTable is slightly different from accessing the DataTable directly. The DataTable exposes its rows of data through the Rows property, which allows you to scroll through its contents with a For Each loop. The DataView object does not expose data via such an easily enumerable collection.

The DataView object exposes a Count property that returns the number of rows visible through the DataView. You can use this property to construct a simple For loop to examine all of the rows.

The DataView also exposes a GetEnumerator method that returns an IEnumerator object. The IEnumerator object, which resides in the System.Collections namespace, offers navigation functionality similar to what the DataReader object offers through its MoveNext method.

The following code snippet shows how to examine the contents of a DataView—once using the Count property and once using the GetEnumerator method:

Visual Basic .NET

Dim tbl As New DataTable("Customers") 'Retrieve data into the DataTable, and modify some rows.  'Create a DataView that contains only modified rows 'and returns the original contents of those rows. Dim vue As DataView vue = New DataView(tbl, "", "", DataViewRowState.ModifiedOriginal) Dim row As DataRowView 'Use a simple For loop to examine the contents of the DataView. Dim intCounter As Integer For intCounter = 0 To vue.Count - 1     row = vue(intCounter)     Console.WriteLine(row("CompanyName")) Next intCounter 'Use an enumerator to loop through the contents of the DataView. Dim objEnum As IEnumerator = vue.GetEnumerator Do While objEnum.MoveNext()     row = CType(objEnum.Current, DataRowView)     Console.WriteLine(row("CompanyName")) Loop

Visual C# .NET

DataTable tbl = new DataTable("Customers"); //Retrieve data into the DataTable, and modify some rows.  //Create a DataView that contains only modified rows //and returns the original contents of those rows. DataView vue; vue = new DataView(tbl, "", "", DataViewRowState.ModifiedOriginal); DataRowView row; //Use a simple for loop to examine the contents of the DataView. for (int intCounter = 0; intCounter < vue.Count; intCounter++) {     row = vue[intCounter];     Console.WriteLine(row["CompanyName"]); } //Use an enumerator to loop through the contents of the DataView. IEnumerator objEnum = vue.GetEnumerator(); while (objEnum.MoveNext()) {     row = (DataRowView) objEnum.Current;      Console.WriteLine(row["CompanyName"]); }

Searching for Data in a DataView

You've already seen how the DataView object supports filtering using the RowFilter and RowStateFilter properties. It also supports searching using the Find and FindRows methods. These methods are similar to the Find method of the DataTable object's Rows collection.

The Find Method

Once you've set the Sort property on a DataView object, you can call its Find method to locate a row based on the columns specified in the Sort property. As with the Find method of the DataRowCollection object, you can supply a single value or an array of values.

The DataView object's Find method does not, however, return a DataRow or a DataRowView object. Instead, it returns an integer value that corresponds to the index of the desired row in the DataView. If the DataView cannot locate the desired row, the Find method returns a value of -1.

The following code snippet shows how to use the DataView object's Find method to locate a customer based on the value of the ContactName column. It also uses the return value of the Find method to determine whether the Find method located the desired row.

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim strSQL As String = "SELECT CustomerID, CompanyName, ContactName, " & _                        "Phone, City, Country FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim tbl As New DataTable("Customers") da.Fill(tbl) Dim vue As New DataView(tbl) vue.Sort = "ContactName" Dim intIndex As Integer = vue.Find("Fran Wilson") If intIndex = -1 Then     Console.WriteLine("Row not found!") Else     Console.WriteLine(vue(intIndex)("CompanyName")) End If

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                   "Initial Catalog=Northwind;Trusted_Connection=Yes;"; string strSQL = "SELECT CustomerID, CompanyName, ContactName, " +                 "Phone, City, Country FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataTable tbl = new DataTable("Customers"); da.Fill(tbl); DataView vue = new DataView(tbl); vue.Sort = "ContactName"; int intIndex = vue.Find("Fran Wilson"); if (intIndex == -1)     Console.WriteLine("Row not found!"); else     Console.WriteLine(vue[intIndex]["CompanyName"]);

The FindRows Method

The DataRowCollection object's Find method performs a search based on the column(s) specified in the DataTable object's PrimaryKey property. Because a primary key is also associated with a unique key constraint, one row at most will satisfy the criteria specified in the DataRowCollection object's Find method.

The DataView object's Find method performs searches based on the column(s) specified in the DataView object's Sort property. Multiple rows of data might have the same values for the columns used to sort the data in the DataView. For example, you can sort customers based on the Country column, and multiple rows have a value of Spain in the Country column. But you can't use the DataView object's Find method to locate all customers in Spain because the Find method returns only an integer.

Thankfully, the DataView object also exposes a FindRows method. You call the FindRows method just as you call the DataView object's Find method, but the FindRows method returns an array of DataRowView objects that contains the rows that satisfied the criteria you specified.

The following code snippet shows how to use the FindRows method and checks whether the method found any rows:

Visual Basic .NET

... Dim vue As New DataView(tbl) vue.Sort = "Country" Dim aRows As DataRowView() = vue.FindRows("Spain") If aRows.Length = 0 Then     Console.WriteLine("No rows found!") Else     Dim row As DataRowView     For Each row In aRows         Console.WriteLine(row("City"))     Next row End If

Visual C# .NET

... DataView vue = new DataView(tbl); vue.Sort = "Country"; DataRowView[] aRows = vue.FindRows("Spain"); if (aRows.Length == 0)     Console.WriteLine("No rows found!"); else     foreach (DataRowView row in aRows)         Console.WriteLine(row["City"]);

Modifying DataRowView Objects

Modifying a row of data using a DataRowView object is similar to modifying the contents of a DataRow object. The DataRowView object exposes BeginEdit, EndEdit, CancelEdit, and Delete methods, just as the DataRow object does.

Creating a new row of data using a DataRowView object is slightly different from creating a new DataRow. The DataView has an AddNew method that returns a new DataRowView object. The new row is not actually added to the underlying DataTable until you call the EndEdit method of the DataRowView object.

The following code snippet shows how to create, modify, and delete a row of data using the DataRowView object:

Visual Basic .NET

Dim tbl As New DataTable("Customers")  Dim vue As New DataView(tbl) 'Add a new row. Dim row As DataRowView = vue.AddNew() row("CustomerID") = "ABCDE" row("CompanyName") = "New Company" row("ContactName") = "New Contact" row("Phone") = "[phone]" row.EndEdit() 'Modify a row. row.BeginEdit() row("CompanyName") = "Modified" row.EndEdit() 'Delete a row. row.Delete()

Visual C# .NET

DataTable tbl = new DataTable("Customers");  DataView vue = new DataView(tbl); //Add a new row. DataRowView row = vue.AddNew(); row["CustomerID"] = "ABCDE"; row["CompanyName"] = "New Company"; row["ContactName"] = "New Contact"; row["Phone"] = "[phone]"; row.EndEdit(); //Modify a row. row.BeginEdit(); row["CompanyName"] = "Modified"; row.EndEdit(); //Delete a row. row.Delete();