ADO.NET 3.5 Cookbook, 2nd Edition

4.5. Filtering and Sorting Data in a DataTable

Problem

You have a DataTable 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 DataTable without requerying the data source.

Solution

Use either the Sort and RowFilter properties of the DataView or the Select() method of the DataTable.

Choose from the two techniques shown in the solution to filter and sort records from table according to user-specified criteria.

The solution creates a DataSet containing data from the Production.Product table in AdventureWorks. Solutions are presented to illustrate how to use two different techniques—the RowFilter property of the DataView and the DataTable.Select() method—to filter and sort records.

The C# code in Program.cs in the project FillSortData that uses the Sort and RowFilter properties of the DataView is shown in Example 4-5.

Example 4-5. File: Program.cs for FillSortData solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace FilterSortData
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT * FROM Production.Product";

            // Fill the DataSet, mapping the default table name
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.TableMappings.Add("Table", "Product");
            DataSet ds = new DataSet();
            da.Fill(ds);

            DataView dv = ds.Tables["Product"].DefaultView;
            dv.RowFilter = "DaysToManufacture >= 4";
            dv.Sort = "Name";

            foreach (DataRowView dvr in dv)
                Console.WriteLine(
                    "ProductID = {0}, Name = {1}, DaysToManufacture = {2}",
                    dvr["ProductID"], dvr["Name"], dvr["DaysToManufacture"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

Partial output is shown in Figure 4-6.

Figure 4-6. Output for FilterSortData solution

The C# code in Program.cs in the project FillSortDataSelect that uses the DataTable.Select() method is shown in Example 4-6.

Example 4-6. File: Program.cs for FillSortDataSelect solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace FilterSortDataSelect
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT * FROM Production.Product";

            // Fill the DataSet, mapping the default table name
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.TableMappings.Add("Table", "Product");
            DataSet ds = new DataSet();
            da.Fill(ds);

            // Filter and sort using the Select() method of the DataTable
            string rowFilter = "DaysToManufacture >= 4";
            string sort = "Name";
            DataRow[] rows = ds.Tables["Product"].Select(rowFilter, sort);

            foreach (DataRow row in rows)
                Console.WriteLine(
                    "ProductID = {0}, Name = {1}, DaysToManufacture = {2}",
                    row["ProductID"], row["Name"], row["DaysToManufacture"]);

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }
    }
}

The output is the same as for the previous solution and is shown in Figure 4-6.

Discussion

The DataView filters and sorts the data in DataTable objects in the DataSet. The RowFilter property of the DataView accesses the expression that filters the view. The Sort property of the DataView sorts the view on single or multiple columns in either ascending or descending order.

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 Select() method of the DataTable class has an overload that takes two arguments specifying the filter criteria and sort order and returns an array of matching DataRow objects. The filter criteria argument's syntax corresponds to a T-SQL WHERE clause while the sort order argument syntax corresponds to a T-SQL ORDER BY clause. For information about converting a DataRow array to a DataTable, see Recipe 2.14.