Home   |   QuickStart Welcome   |   ASP.NET   |   Web Services   |   How Do I...?   
  |   I want my samples in...      

How Do I...? Common Tasks QuickStart Tutorial

ADO.NET: Filter Data

There are many ways to filter data. One way is to filter data at the database command level, using a where clause on your query. A second way is to filter the data after it is in the DataSet. This topic covers filtering in the DataSet.

Once data is in a DataSet you can use methods on the DataSet to get at subsets of that data.
Filtering with the Select method

Consider a DataSet with Customers and Orders tables. To filter on Data for Customers with the FirstName of Kelly, you can use the Select method, which returns an array of rows.

    myRowArray = dsCustomers.Select("ContactName like 'Kelly%'");

Notice that the statements are ANSI-SQL like. They are essentially a subset of ANSI-SQL, with the following difference: because the DataSet can hold multiple related tables, the filter can also filter over these related tables. In the following example, use the Child keyword to filter the orders and the customers.

    myRowArray = dsCustomers.Select("ContactName like 'Kelly%' AND [child].OrderDate = '7/26/68'");

These functions return an array of rows. You can iterate through the array using the foreach statement.
Filtering and Sorting with DataViews

With the DataView object, you can place multiple filters on a DataSet, set up databinding to them, and so forth. A DataView can be filtered using the same query language rules as in the Select, but the filter is dynamic. Therefore, if a row is added to the data, and it qualifies with the filter, it is visible in the view. Views can be sorted and filtered. Filters include data filters and version filters (Current, New, Modified, Deleted). To set up a DataView, construct it by handing it a DataTable within a DataSet:

    DataView myDataView = new DataView(myDataSet.Tables["Customers"]);

You can set up a filter with the RowFilter property using the same query filter language as in the example for the Select method above. To set up a sort, use a comma-delimited list of column sorts, followed by ASC (default and optional) or DESC for ascending and descending order.

    // Sort the view based on the FirstName column
    myDataView.Sort = "CustomerID";
    // Filter the dataview to only show customers with the CustomerID of ALFKI
    myDataView.RowFilter = "CustomerID='ALFKI'";

You can also filter based on the state of the rows (deleted, new, and so forth). Here is an example of a Filter on Deleted rows:

    myDataView.RowStateFilter = DataViewRowState.Deleted;

The following sample demonstrates filling a DataSet, and then filtering it with a DataView. Note that a DataView can easily be data bound. See the databinding topics in Windows Forms or ASP.NET.

VB FilterDataTable.aspx
Run Sample View Source

Microsoft .NET Framework SDK QuickStart Tutorials Version 2.0
Copyright � 2004 Microsoft Corporation. All rights reserved.