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";

// 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( );
    
    SqlDataAdapter da;

    // Fill the Customers table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM Customers",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable customersTable = new DataTable(CUSTOMERS_TABLE);
    da.Fill(customersTable);
    ds.Tables.Add(customersTable);

    // Fill the Order table and add it to the DataSet.
    da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    DataTable orderTable = new DataTable(ORDERS_TABLE);
    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;

    // Filter to Orders view for the employee.
    String employeeIdFilter = "";
    if (orderEmployeeIdTextBox.Text != "")
    {
        try
        {
            employeeIdFilter = "EmployeeId = " +
                Int32.Parse(orderEmployeeIdTextBox.Text);
        }
        catch (FormatException)
        {
            orderEmployeeIdTextBox.Text = "";
        }
    }
    dvm.DataViewSettings[ORDERS_TABLE].RowFilter = employeeIdFilter;

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

Discussion

The DataView filters and sorts the data in DataTable objects in the DataSet. The DataViewManager can simplify working with multiple views within a DataSet, but is not required. The DataViewManager object exposes a DataViewSettingCollection object through the DataViewSettings property. The collection contains a single DataViewSetting object for each table in the DataSet. The object is accessed using the name or ordinal of the table by using an indexer in C# or by using the Item( ) property in VB.NET. The DataViewSetting object allows access to the ApplyDefaultSort, RowFilter, RowStateFilter, and Sort properties of a DataView created from the DataViewManager for the table. Accessing these properties is identical to accessing the same properties directly through the DataView.

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.