ADO.NET 3.5 Cookbook, 2nd Edition

4.3. Filtering Rows in a DataTable or DataView

Problem

You need to filter a DataTable or DataView for rows meeting certain criteria.

Solution

Use one of the two techniques shown in the solution to locate data in the table meeting user-specified criteria.

The solution creates a DataTable containing data from the Person.Contacts table in AdventureWorks. The solution uses two different techniques—the DataTable.Select() method and the DataView.RowFilter property—to filter and display rows.

The C# code in Program.cs in the project FilterRows is shown in Example 4-3.

Example 4-3. File: Program.cs for FilterRows solution

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

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

            string sqlSelect = "SELECT * FROM Person.Contact";

            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.Fill(dt);

            string filter = "LastName LIKE 'AG*'";

            // Filter the rows using Select() method of DataTable
            DataRow[] rows = dt.Select(filter);

            Console.WriteLine("---Filtering using DataTable.Select()---"); foreach (DataRow row in rows) Console.WriteLine("{0}\t{1}, {2}", row["ContactID"], row["LastName"], row["FirstName"]); Console.WriteLine(); // Filter the rows using RowFilter property of a DataView DataView dv = dt.DefaultView; dv.RowFilter = filter; Console.WriteLine("---Filtering using ...