Sorting and Filtering Data

Let's look at one more topic concerned with DataTable and DataView objects. Once you've loaded a DataTable or a DataView object with data, you wouldn't really want to go back to the data store and reload it every time you want to sort the rows into a different order, or filter the rows so that only a subset is displayed. Thankfully, both the DataTable and DataView provide sorting and filtering features, even if the way they work is fundamentally different for each object. We'll look at both next .

Sorting and Filtering in a DataTable

The Sorting and Filtering in a DataTable Object example page ( select-in-table.aspx ) demonstrates how you can sort and filter data that is stored in a DataTable object. For example, in Figure 9-19, we've loaded the page and then clicked the By Date button to sort the rows into order by the values in the PublicationDate column and then by the values in the Title column. You can see from the message in the page that your code executed the Select method of the DataTable object with two parameters: an empty string, and a string containing the two column names for the sort operation:

Figure 9-19:

Figure 9-20 demonstrates how the page also provides a Search feature. We've entered ASP into the textbox and clicked the Find button to give the result shown. The DataTable.Select method was executed again, and this time the first parameter is the expression Title LIKE '*ASP*' , while the second is an empty string. Only the rows containing ASP anywhere within the Title column appear in the page.

Figure 9-20:

In the example page, you'll notice that code is recreating the DataSet and its content each time you change the sort order or perform a search. This is only because you don't persist the data elsewhere between requests .

If you remote the DataSet to another tier of your application, or receive it from something like a web service, you do not need to recreate it each time. Instead, simply execute the Select method on the table within your cached DataSet object.

The DataTable.Select Method

It's clear from the example that the DataTable object's Select method takes two parameters “ an expression that filters the rows and a list of column names by which the rows are sorted:

 DataTable.Select(  filter-expression  ,  sort-order  ) 

The example page only uses one of these parameters at a time, but you can use both together:

  objDataTable.Select("Title LIKE '*ASP*'", "PublicationDate DESC, Title")  

Notice that the syntax for the sorting parameter is the same as for most other data access techniques “ you add DESC to force reverse sort order. There is a second overload of this method that only accepts a parameter for the fiter expression, so you can use the following code if you want to filter the rows but don't want to sort them:

  DataTable.Select("Title LIKE '*ASP*'")  

Specifying the DataRowState

There is also an overload of the Select method that accepts a third parameter. This is used to specify the DataViewRowState of the rows that will be included. By using the value of each row's RowState property, this allows you to include all rows, or select only rows that are unchanged, new, modified, or deleted. For example, the following code will only include rows that have not been changed or deleted:

  DataTable.Select("Title LIKE '*ASP*'", "Title", DataViewRowState.Unchanged)  

The default for this parameter, if you don't specify a different value, is DataViewRowState.None , and the result reflects the current state of all the rows in the table. The full set of values for the DataViewRowState enumeration is shown in the following table:

Value	Description
CurrentRows	Includes unchanged, new, and modified rows
OriginalRows	Includes only unchanged and deleted rows
ModifiedCurrent	Includes the current values of any modified rows
ModifiedOriginal	Includes the original values of any modified rows
Unchanged	Includes only unchanged rows
Added	Includes only new rows added to the table
Deleted	Includes only deleted rows
None	No filtering on the RowState of the rows is applied

The Filter Expression Syntax

In general, the expression syntax used in the first parameter of the Select method is much as you'd expect “ following the expression syntax of the .NET languages. For example:

Simple comparison expressions are:

  "Lastname = 'Jones'"                'string literals in single quotes   "StockQty > 1000"                   'numbers are not in quotes   "PublicationDate > #10/10/99#"      'special syntax for date/time  

The supported comparison operators are: < , > , <= , >= , <> , = , IN , and LIKE .

For numeric column values, the operators that can be used are: + , - , * , / , % ( modulus ).

String concatenation is always with the ' + ' character. Case-sensitivity during string comparisons depends on the current setting of the parent DataSet object's CaseSensitive property. This property value can be changed in code as required.

The LIKE operator supports wildcards for string comparisons . The ' * ' or ' % ' character (these characters are equivalent) can be used to represent any series of characters , and can be used at the start, at the end, or at both the start and end of other literal text. They cannot be used within the text of a search string:

  "Lastname LIKE 'Peter%'"  is the same as  "Lastname LIKE 'Peter*'"   "Lastname LIKE '%Peter'"  is the same as  "Lastname LIKE '*Peter'"   "Lastname LIKE '%Peter%'"  is the same as  "Lastname LIKE '*Peter*'"  

To include the wildcards where they are part of the literal text in the search string, they must be escaped with a preceding backslash:

  "WebWord LIKE '\*BOLD\*'" 'filters on the string "*BOLD*"  

The AND , OR , and NOT operators are supported, with AND having precedence unless you use parentheses to force a different evaluation order:

  "Title LIKE '*ASP*' AND StockQty > 1000"   "(LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'"  

The following characters cannot be used directly in a column name within a filter expression:

 ~ () # \ / = > < +  * % &  ^ ' " [ ] 

If a column name contains one of these characters or a space, it must be enclosed in square brackets:

  "[Stock#] > 1000" 'column named "Stock#"  

If a column name contains a closing square bracket , this must be escaped with a preceding backslash:

  "[Number[\]Cols] > 1000" 'column named "Number[]Cols"  

There is also a range of functions supported in filter expressions, including:

 Sum, Avg, Min, Max, Count, StDev, Var, Convert, Len, IsNull, IIF, SubString 

The Code for the DataTable Sorting and Filtering Example

Look at the code for the example page you saw a little earlier. The HTML section of the page, shown in the following code, contains a <form> that holds the command buttons and textbox for the filter expression and this is followed by a <div> where the results are displayed:

  <form runat="server">   Sort records:   <input type="submit" id="cmdTitle" value="By Title" runat="server" /> &nbsp;   <input type="submit" id="cmdISBN" value="By ISBN" runat="server" /> &nbsp;   <input type="submit" id="cmdDate" value="By Date" runat="server" /><p />   Search within titles:   <input type="text" id="txtFind" size="20" runat="server" />   <input type="submit" id="cmdFind" value="Find" runat="server" />   </form>   <div id="outResult" runat="server"></div>  

The code that makes it work is in the Page_Load event handler of the page. It first collects a subset of rows from the original data store “ the example WroxBooks database “ and places them in a table named Books within a DataSet . This table contains all the books you saw in Figure 9-19.

Then, as shown in the code that follows , you can create the filter and sort expressions based on the details provided by the user . In the case of a filter expression, you can add a preceding and trailing asterisk wildcard character so that it will match column values containing this text.

If this is the first time that the page has been loaded, there will be no values from the <form> in the request, and so no filter or sort expression will be created. Otherwise, after the previous code is executed, you'll have a non-empty value in either the strSortString or strFilterExpr string.

The values of these two strings are displayed in another <div> element placed before the <form> section of the page:

  'create the Sorting expression     Dim strSortString As String = ""   If Len(Request.Form("cmdTitle")) > 0 Then strSortString = "Title"   If Len(Request.Form("cmdISBN")) > 0 Then strSortString = "ISBN"   If Len(Request.Form("cmdDate")) > 0 Then strSortString = _   "PublicationDate DESC, Title"     'or create the Filter expression     Dim strFilterExpr As String = ""   If Len(Request.Form("cmdFind")) > 0 Then   strFilterExpr = "Title LIKE '*" & txtFind.Value & "*'"   End If     'display the parameters we're using     outMessage.innerHTML = "Called DataTable.Select(""" & strFilterExpr _   & """, """ & strSortString & """)"  

Executing the Select Method

Finally, you can apply the filter or sort to the table. As shown in the following code, you first get a reference to the DataTable object. The Select method returns an array of DataRow objects that match the applied filter and sort, so the next step is to create a suitable array variable. Then you can execute the Select method and assign the result to this variable ready for display:

  Dim objTable As DataTable = objDataSet.Tables("Books")     'create an array to hold the results then call the Select method     Dim objResults() As DataRow   objResults = objTable.Select(strFilterExpr, strSortString)  

Displaying the Results

To display the results, you have to iterate through the array of DataRow objects that is returned by the Select method “ you can't just bind it to a DataGrid as in earlier examples. The following code shows how you build an HTML table containing the column values for each DataRow in the array and then display this table in the <div> element named outResult :

  'the result is an array of DataRow objects not a DataTable object     'so we have to iterate through to get the row contents     Dim objRow As DataRow   Dim strResult As String = "<table>"   For Each objRow In objResults   strResult &= "<tr><td>" & objRow(0) & "</td><td>&nbsp; " & objRow(1) _     & "</td><td>&nbsp; " & objRow(2) & "</td></tr>"   Next   strResult &= "</table>"   outResult.InnerHtml = strResult 'and display the results  

Sorting and Filtering in a DataView Object

Another opportunity for sorting and filtering rows for display is within a DataView object. It's common to create a DataView based on a DataTable when using server-side data binding; you've been doing this throughout these chapters with a DataGrid server control.

The example page Sorting and Filtering Records in a DataView object ( sort-find-in-dataview.aspx ) demonstrates how easy it is to sort and filter the rows in a DataView .

This example page looks similar to the previous one (sorting and filtering a DataTable ). However, rather than using a Select method (like was done with the DataTable ), specify the filter and sort order for a DataView by setting its properties. Set the Sort property to change the sorting order of the rows, and the RowFilter property to apply a filter.

Figure 9-21 shows the rows being sorted. Notice the code that has been executed after clicking the By Date button this time. You can simply assign the column names (and optionally the DESC keyword) to the Sort property of the DataView .

Figure 9-21:

Meanwhile, as shown in Figure 9-22, the result of entering the search text ASP and clicking the Find button is to set the RowFilter property of the DataView :

Figure 9-22:

Note

As you saw earlier in the example of using the RowUpdated event, the DataView object also has a RowStateFilter property. This works just the same as with the DataTable object, and you can also use this to filter the rows.

The Code for the DataView Sorting and Filtering Example

As you'll expect, most of the code for this example is the same as that used in the previous example. It uses the same HTML form and the same code to create and fill a DataSet with some book details. However, the next step is to get a reference to the DataView object that you'll be working with. Create a new DataView based on the Books table in the DataSet :

  'create a DataView object for the Books table in the DataSet   Dim objDataView As New DataView(objDataSet.Tables("Books"))  

Of course, if you already have a DataTable object available, perhaps as the result of some other code you've used to create it specifically , you can simply access the DefaultView property of the table to get back a DataView object.

Collecting the User's Values and Applying the Sort and Filter

Now you can check for user input and build the appropriate string for the Sort and RowFilter properties of the DataView , as shown in the following code. If the user clicked a 'sort' button, you can simply build the sort expression as one or more column names (including DESC for a descending sort order) and set the Sort property of the DataView object.

Following a postback where the user clicked the Find button, the code in the page builds a filter expression (using the same syntax as the previous example), and assigns it to the RowFilter property of the DataView object. As in the previous example, a preceding and trailing asterisk wildcard character is added so that it will match column values containing this text. The expression used is also displayed in the page. Finally, the sorted or filtered DataView object is assigned to the DataSource property of an ASP.NET DataGrid control declared elsewhere in the page to display the contents:

  'sort the records into the correct order   If Len(Request.Form("cmdTitle")) > 0 Then   objDataView.Sort = "Title"   outMessage.innerHTML = "DataView.Sort = " & objDataView.Sort   End If   If Len(Request.Form("cmdISBN")) > 0 Then   objDataView.Sort = "ISBN"   outMessage.innerHTML = "DataView.Sort = " & objDataView.Sort   End If   If Len(Request.Form("cmdDate")) > 0 Then   objDataView.Sort = "PublicationDate DESC, Title"   outMessage.innerHTML = "DataView.Sort = " & objDataView.Sort   End If   'or find matching records   If Len(Request.Form("cmdFind")) > 0 Then   objDataView.RowFilter = "Title LIKE '*" & txtFind.value & "*'"   outMessage.innerHTML = "DataView.RowFilter = " & objDataView.RowFilter   End If   'assign the DataView object to the DataGrid control   dgrResult.DataSource = objDataView   dgrResult.DataBind() 'and bind (display) the data