ADO.NET Cookbook

3.1. Filtering and Sorting Data

Problem

You have a DataSet filled with data, but you need to work with only a subset of the records and also to sort them. You need a way to both filter and sort the records in your DataSet without requerying the data source.

Solution

Use DataViewManager and DataView objects to filter and sort a DataSet.

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataSet containing the Customers and Orders tables from the Northwind sample database and a relation between them. The default view for the Customers table is bound to the data grid on the form.

Refresh Button.Click

Applies the filters and sort order specified by the user to the data views for the tables accessed through the DataViewManager object.

The C# code is shown in Example 3-1.

Example 3-1. File: FilterSortForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Data; using System.Data.SqlClient; // Table name constants private const String CUSTOMERS_TABLE = "Customers"; private const String ORDERS_TABLE = "Orders"; // Relation name constants private const String CUSTOMERS_ORDERS_RELATION = "Customers_Orders_Relation"; // Field name constants private const String CUSTOMERID_FIELD = "CustomerID"; private const String ORDERID_FIELD = "OrderID"; private const String CONTACTNAME_FIELD = "ContactName"; private DataSet ds; // . . . private void FilterSortForm_Load(object sender, System.EventArgs e) { ds = new DataSet( ...