Filter records in a DataSet/DataTable
There are times when working with disconnected data that your DataSet can be quite large and you need some way of filtering the records you are working with, much as you would use a SELECT statement to filter the rows you want to return from a database. Thankfully the .NET framework contains a Filter mthods that can filter the records in a DataTable to achieve just such an outcome.
To illustrate this we will create a DataSet containing one DataTable and populate the DataTable with some dummy data. We won't be connecting to a database in this example, but as you know form previous posts, it is very easy to create your DataSets from SQL Server, Access or XML-based data sources.
//create Dataset and DataTable
DataSet ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("FirstName", string.Empty.GetType());
dt.Columns.Add("LastName", string.Empty.GetType());
//create some dummy data
DataRow dr = dt.NewRow();
dr["FirstName"] = "Kim";
dr["LastName"] = "Abercrombie";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["FirstName"] = "Gustavo";
dr["LastName"] = "Achlong";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["FirstName"] = "Catherine";
dr["LastName"] = "Abel";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["FirstName"] = "Humberto";
dr["LastName"] = "Acevedo";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["FirstName"] = "Robert";
dr["LastName"] = "O'Hara";
dt.Rows.Add(dr);
//add the DataTable to our DataSet
ds.Tables.Add(dt);
Once we have created our dummy DataSet, we can now perform some filtering. The .Filter method of the DataTable object returns an array of DataRow objects.
//perform some filtering
string name = "O'Hara";
DataRow[] filtered = ds.Tables[0].Select("LastName ='" + name.Replace("'", "''") + "'");
if (filtered.Length > 0)
{
Console.WriteLine("Found " + filtered.Length + " records.");
}
The filtering all happens with just one line of code, which is pretty straight forward. The only gotcha that you may encounter especially when dealing with names is the apostrophe. If you don't replace the single apostrophe with a double, the code will throw an error Syntax error: Missing operand after 'Hara' operator when trying to carry out the Filter method.
You can filter using a range of valid expressions, including using the LIKE operator. The following code returns two records.
string name = "Ac";
DataRow[] filtered = ds.Tables[0].Select("LastName Like'" + name.Replace("'", "''") + "%'");