Select Subset Of Rows In AnADO.NET DataTable

By Robbe D. Morris

Printer Friendly Version


Robbe & Melisa Morris
Here's a little tip demonstrating how ADO.NET provides us with the ability to dynamically change filter criteria and sort order in our DataTable without making another trip to the database.  This is great for applications that need to query large tables to return a smaller subset of records to work with.  In many of today's applications, if we need to change the sort order of the results or only work with an even smaller subset, we have to requiry the database.  This will undoubtedly have a negative impact on performance.
The code sample below shows how to run a single query against the Northwind database and create a disconnected DataTable.  Then, it utilizes the .Select method of the DataTable to change the sort order as well as filter the resultset down to only a couple of records.



Sample Console Application Code
  
using System;
using System.Data;
using System.Data.SqlClient;

namespace DataTableArticle
{
	 
 class Class1
 {
		 
  [STAThread]
  static void Main(string[] args)
  {
    SqlConnection oConn = new SqlConnection();
    DataTable oTable = new DataTable();
    DataRow[] oRows;

    string sConnectionString = "Data Source=(local);User ID=username;Password=pwd;Initial Catalog=Northwind";	
	 
    try
    {   
      oConn.ConnectionString = sConnectionString;
      oConn.Open();	  
      SqlDataAdapter oDA = new SqlDataAdapter("select CustomerID,ContactName from Customers order by ContactName ASC",oConn);
      oDA.Fill(oTable);
      oConn.Close();
 
      Console.WriteLine("Start Series 1\n");

      foreach(DataRow oRow in oTable.Rows)
      {
        Console.WriteLine(oRow["CustomerID"].ToString() + ": " + oRow["ContactName"].ToString());
      }

      Console.WriteLine("End Series 1\n\n");

      Console.WriteLine("Start Series 2\n");

      oRows = oTable.Select(null,"ContactName DESC");

      foreach(DataRow oRow in oRows)
      {
        Console.WriteLine(oRow["CustomerID"].ToString() + ": " + oRow["ContactName"].ToString());
      } 
                 
      Console.WriteLine("\nEnd Series 2\n\n");

      Console.WriteLine("Start Series 3\n");

      oRows = oTable.Select("CustomerID in ('ALFKI','ANATR')","ContactName ASC");

      foreach(DataRow oRow in oRows)
      {
        Console.WriteLine(oRow["CustomerID"].ToString() + ": " + oRow["ContactName"].ToString());
      } 

      Console.WriteLine("\nEnd Series 3\n");

      }
      catch (Exception e) { Console.WriteLine(e.Message); }  
      finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } } 
      Console.ReadLine(); 
    } 
 }
}


Robbe is a 2004-2008 Microsoft MVP for C# and the .NET Evangelist for Alinean Inc..  He is also the co-founder of EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.