Creating a DataTable Index

Home > Posts > Creating a DataTable Index

Download DataTableIndex.cs (Right Click->Save Target As...).

While trying to perform different data loads and ETL transforms you might come across the need to query a DataTable for corresponding rows for a particular column value. This DataTableIndex class can be used to create an index against one or multiple DataColumns and provides a Find method that returns corresponding DataRows for a given value.

The class can be used against untyped DataTables like below:

DataTable users = new DataTable();

users.Columns.Add("name");
users.Columns.Add("gender");

DataTableIndex genderIndex = new DataTableIndex(users, users.Columns["gender"]);

users.Rows.Add("John", "male");
users.Rows.Add("Mary", "female");
users.Rows.Add("George", "male");
users.Rows.Add("Lisa", "female");

foreach (DataRow female in genderIndex.Find("female"))
{
	Console.WriteLine(female["name"]);
}

It can also be used against a typed DataTable. Using code behind of the DataTable an index property can even be exposed.

public partial class PurchasesDataSet
{
	public partial class OrderDataTable
	{
		private DataTableIndex stateIndex = null;

		public DataTableIndex StateIndex
		{
			get
			{
				if (stateIndex == null)
				{
					stateIndex = new DataTableIndex(this, this.StateColumn);
				}

				return stateIndex;
			}
		}
	}
}

You can then use code like below:

PurchasesDataSet purchases = new PurchasesDataSet();

purchases.Order.AddOrderRow(
	1, // OrderID
	"Missouri", // State
	50.25 // Total
	);
purchases.Order.AddOrderRow(
	2, // OrderID
	"Kansas", // State
	21.20 // Total
	);

foreach (PurchasesDataSet.OrderRow order in purchases.Order.StateIndex.Find("Missouri"))
{
	Console.WriteLine("Order ID " + order.OrderID + ": " + order.Total.ToString("c"));
}

Please send any questions or comments to Bruce Dunwiddie.