Filtering a DataTable with the DataView
There are many scenarios when you have your data in a DataTable but then need to filter that data.
There are many ways to achieve this but two very quick and easy methods are as follows:
- Use the Select method of the DataTable which returns an array of DataRows
- Use a DataView which is to a DataTable as a database query or view is to a database Table
The code fro this is pretty simple and the console application below shows both of these techniques in action.
Note that the filter is basically just like SQL in a database. Also note the slightly different technique for iterating over rows in the DataView.
This code is very simple and the example below is well commented so no more explanation is needed. Give the code a whirl and see how you . As always comments are welcome via the form at the bottom of this page.
Once executed, you should get results as below:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace Audacs.ConsoleApp
{
class FilterDataTable
{
/// <summary>
/// The main entry point of the program
/// </summary>
public static void Main()
{
//Create a new DataTable instance from our GetData method
DataTable myTable = GetData();
//Output a heading for the table as it is initially
OutputHeading("Initial Table");
//Iterate over the rows in the table and output them to screen
foreach (DataRow row in myTable.Rows)
{
Console.WriteLine(row["PersonName"] + "\t" + row["VisitDate"]);
}
//Create an array of DataRows by using the Select method of the DataTable
DataRow[] filteredRows = myTable.Select("PersonName = 'Dave Amour'");
//Create a DataView relating to the DataTable
DataView view = new DataView(myTable);
//Set the fileter or query for the DataView
view.RowFilter = "PersonName Like '%Sarah%'";
//Output a blank line for ease of reading
Console.WriteLine();
//Output a heading for the Filtered array of DataRows
OutputHeading("Filtered array of DataRows");
//Iterate over the rows in the array of DataRows and output them to screen
foreach (DataRow row in filteredRows)
{
Console.WriteLine(row["PersonName"] + "\t" + row["VisitDate"]);
}
//Output a blank line for ease of reading
Console.WriteLine();
//Output a heading for the Filtered DataView
OutputHeading("Filtered DataView");
//Iterate over the rows in the Filtered DataView and output them to screen
foreach (DataRowView row in view)
{
Console.WriteLine(row["PersonName"] + "\t" + row["VisitDate"]);
}
//Wait for a key to be pressed
Console.WriteLine();
Console.WriteLine("Finished. Press any key to exit");
Console.Read();
}
/// <summary>
/// A simple factory type of method to give us a populated DataTable
/// </summary>
/// <returns></returns>
public static DataTable GetData()
{
//Create a new instance of a DataTable
DataTable myTable = new DataTable();
//Define the columns in the DataTable
myTable.Columns.Add("PersonName", typeof(string));
myTable.Columns.Add("VisitDate", typeof(DateTime));
//Add some rows to the DataTable
myTable.Rows.Add("Dave Amour", DateTime.Now);
myTable.Rows.Add("Fred Bloggs", DateTime.Now);
myTable.Rows.Add("Sarah Smith", new DateTime(2009, 3, 14));
myTable.Rows.Add("Louise Reed", DateTime.Now);
myTable.Rows.Add("Dave Boldman", new DateTime(2009, 3, 15));
myTable.Rows.Add("Bill Flint", new DateTime(2009, 3, 16));
myTable.Rows.Add("Sarah Smith", new DateTime(2009, 3, 16));
myTable.Rows.Add("Bill Williams", new DateTime(2009, 3, 16));
myTable.Rows.Add("Bill Brown", new DateTime(2009, 3, 16));
//retun the instance of the DataTable
return myTable;
}
/// <summary>
/// Outputs a heading in Yellow and then sets the screen colour back to white
/// for ease of reading
/// </summary>
/// <param name="heading"></param>
private static void OutputHeading(string heading)
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine(heading);
Console.ForegroundColor = ConsoleColor.White;
}
}
}