Sorting and Filtering a DataSet

Sorting and Filtering a DataSet

If you know how to write SQL, you know that you can sort and filter data with ORDER BY and WHERE clauses. However, this approach requires that you requery the database each time the view changes. Such requerying is an expensive and impractical operation, especially when you have thousands of simultaneous users and the data has already been queried.

With ADO.NET you have better alternatives. You can read data into a DataSet and change the view and order of the data without requerying the database. You can change the order and rows with the DataView.RowFilter and Sort properties or with the DataTable.Select method. The subsections that follow contain examples of each of these.

Filtering and Sorting a DataView

The DataView class has RowFilter and Sort properties. RowFilter and Sort are both string properties. You indicate the rows you want displayed in a DataView by modifying the RowFilter property with expressions that are similar to WHERE predicates in SQL, and you specify order by assigning string expressions that are similar to ORDER BY predicates in SQL. Here are three filter expressions followed by two sort expressions.

 Address like '*Obere*' City = 'Portland' Region is null CustomerID ASC ContactName DESC 

The first statement checks for Address fields containing the letters "Obere," as in "Obere Str." (an abbreviation for Strasse , the German word for street ). The second expression performs a simple equality test, and the third example demonstrates how to find null fields. The fourth statement orders the DataView by CustomerID in ascending order, and the fifth statement orders the DataView by ContactName from Z to A.

Changing the DataView with RowFilter and Sort expressions does not alter the actual data nor does it require a connection to the database. As you might imagine, this reduces the traffic back and forth between the client and server, which can dramatically improve performance over a network or on the Internet. Listing 11.12 (from CommandBuilderDemo.sln ) shows how we could use TextBox and CheckBox controls to facilitate dynamic ordering and sorting. The user can add expressions as desired and use the checkboxes to turn filtering and ordering on or off.

Listing 11.12 Ordering and Filtering Dynamically

 1:   Private Sub CheckBox1_CheckedChanged( _ 2:     ByVal sender As System.Object, _ 3:     ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged 4: 5:     If (Customers Is Nothing) Then Return 6:     Try 7:       SetFilter(TextBoxFilter.Text, CheckBox1.Checked) 8:     Catch x As Exception 9:       MessageBox.Show(x.Message) 10:    End Try 11: 12:  End Sub 13: 14:  Private Sub SetFilter(ByVal Filter As String, _ 15:    ByVal ApplyFilter As Boolean) 16: 17:    If (ApplyFilter) Then 18: 19:      Customers.Tables(0).DefaultView().RowFilter = _ 20:        Filter 21:      DataGrid1.Invalidate() 22: 23:    Else 24:      Customers.Tables(0).DefaultView().RowFilter = "" 25:      DataGrid1.Invalidate() 26: 27:    End If 28:  End Sub 29: 30:  Private Sub CheckBox2_CheckedChanged( _ 31:    ByVal sender As System.Object, _ 32:    ByVal e As System.EventArgs) Handles CheckBox2.CheckedChanged 33: 34:    If (Customers Is Nothing) Then Return 35:    Try 36:      SetSort(TextBoxSort.Text, CheckBox2.Checked) 37:    Catch x As Exception 38:      MessageBox.Show(x.Message) 39:    End Try 40: 41:  End Sub 42: 43:  Private Sub SetSort(ByVal Expression As String, _ 44:    ByVal ApplyFilter As Boolean) 45: 46:    If (ApplyFilter) Then 47: 48:      Customers.Tables(0).DefaultView().Sort = _ 49:        Expression 50:      DataGrid1.Invalidate() 51: 52:    Else 53:      Customers.Tables(0).DefaultView().Sort = "" 54:      DataGrid1.Invalidate() 55: 56:    End If 57:  End Sub 

The code listing builds on the code in Listing 11.10. We assume that Customers is a DataSet containing at least one table. In Listing 11.12, the two CheckBox Changed event handlers make sure that Customers is not null (lines 5 and 34) before proceeding. If Customers is initialized , the values of the related TextBox and CheckBox controls are passed to appropriately named methods , SetSort and SetFilter .

Both SetSort and SetFilter work in similar ways. If the CheckBox control was cleared, the expressions are set to empty strings and DataGrid1 is invalidated (lines 24, 25, 53, and 54). If the Boolean argument ApplyFilter is True , the expression is assigned to the DefaultView property and the grid is invalidated. (Invalidating the grid will cause it to reflect the changes. You also have the option of reassigning the DataGrid.DataSource property.)

In our example we request the DefaultView of a table, which returns a reference to the DataView representing the DataTable . With the DataView we can set the Sort expression (lines 48 and 49) to change the order of the data and the DataView.RowFilter expression (lines 19 and 20) to change the rows that displayed in the view.

You are welcome to download CommandBuilderDemo.sln to experiment with RowFilter and Sort expressions. Refer to the Visual Studio .NET help documentation for specific information about the grammar, including more examples.

Filtering and Sorting Rows with a DataTable

A DataTable can be filtered and sorted by invoking the Select method. Select is overloaded to accept up to two string arguments. The first string argument represents the filtering expression, and the second string argument represents the sorting expression. When you invoke DataTable.Select with a filter, a sort, or both, you get an array of DataRow objects representing the rows that matched the filter in the order specified by the Sort expression. Each DataRow is a reference to a row in the DataTable , so changes to values in the rows will be reflected back in the DataTable . One drawback is that you cannot bind a DataRow to controls like a DataGrid . As a result, using the DataTable.Select statement is not a convenient way to change the appearance of data in a DataGrid . Continuing with the content of our earlier examples, the following code fragment demonstrates how to retrieve filtered and sorted rows from a DataTable .

 Dim Rows() As DataRow = Customers.Tables(0).Select("City                       = 'Portland'") 

This single statement assumes that the DataSet Customers contains at least one table with a schema that describes a City column. This statement retrieves all rows whose fields in the City column contain the value 'Portland' (as in Portland, Oregon, or Portland, Maine).

If you want to specify a sort order too, pass the Sort expression as the second argument to the DataTable.Select method.