[ Team LiB ]Recipe 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";


da.Fill(orderTable);
ds.Tables.Add(orderTable);

// Create a relation between the tables.
ds.Relations.Add(CUSTOMERS_ORDERS_RELATION,
ds.Tables[CUSTOMERS_TABLE].Columns[CUSTOMERID_FIELD],
ds.Tables[ORDERS_TABLE].Columns[CUSTOMERID_FIELD],
true);

// Bind the DataViewManager to the grid.
dataGrid.SetDataBinding(ds.DefaultViewManager, CUSTOMERS_TABLE);
}

private void refreshButton_Click(object sender, System.EventArgs e)
{
DataViewManager dvm = new DataViewManager(ds);

String countryFilter = "";
if (customerCountryTextBox.Text != "")
countryFilter = "Country = '" +
customerCountryTextBox.Text + "'";

// Sort on the contact name, as appropriate.
if(contactSortCheckBox.Checked)
dvm.DataViewSettings[CUSTOMERS_TABLE].Sort =
CONTACTNAME_FIELD;

// Filter the Customers view for the country.
dvm.DataViewSettings[CUSTOMERS_TABLE].RowFilter = countryFilter;


The RowFilter property of the DataView accesses the expression that filters the view.
The Sort property of the DataView sorts the view on a single or multiple columns in
either ascending or descending order.
In the sample, a filter field is provided on both the Orders and Order Details table (the
Country and EmployeeID fields, respectively). Additionally, the sample allows the data
grid to be optionally sorted on the ContactName column. The filter and sort properties are
controlled by setting the RowFilter and Sort properties of the DataViewSetting for the
appropriate table.
[ Team LiB ]