C# DataTable Select Example

DataTable select. DataTable has a Select method.

This method receives a string expression that specifies what rows you want to handle. Select makes DataTables act more like small databases. We explore further this method.

Example. The first part of this program simply adds five DataRows to a DataTable with two DataColumns. Each Player is added with a Size and Sex field. Next, we invoke Select with the parameter "Size >= 230 and Sex = 'm'".

And: This results in an array of DataRow instances where every Player has those matching characteristics.

DataRow

Based on:

.NET 4.5

C# program that uses DataTable Select method

using System;
using System.Data;

class Program
{
    static void Main()
    {
	// Create a table of five different people.
	// ... Store their size and sex.
	DataTable table = new DataTable("Players");
	table.Columns.Add(new DataColumn("Size", typeof(int)));
	table.Columns.Add(new DataColumn("Sex", typeof(char)));

	table.Rows.Add(100, 'f');
	table.Rows.Add(235, 'f');
	table.Rows.Add(250, 'm');
	table.Rows.Add(310, 'm');
	table.Rows.Add(150, 'm');

	// Search for people above a certain size.
	// ... Require certain sex.
	DataRow[] result = table.Select("Size >= 230 AND Sex = 'm'");
	foreach (DataRow row in result)
	{
	    Console.WriteLine("{0}, {1}", row[0], row[1]);
	}
    }
}

Output

250, m
310, m

DateTime. The Select method can be used with dates. In this example, we create a DataTable. Each row has a DateTime cell. We then query with the Select method. Please notice how the date string is surrounded by pound "#" symbols.

DateTime

Result: Select queries the DataTable and finds two matching rows. Both of them have a date more recent than 6/1/2001.

C# program that uses DateTime, Select method

using System;
using System.Data;

class Program
{
    static void Main()
    {
	//  Create table.
	// ... Add two columns and three rows.
	DataTable table = new DataTable("Widgets");
	table.Columns.Add(new DataColumn("ID", typeof(int)));
	table.Columns.Add(new DataColumn("Date", typeof(DateTime)));
	table.Rows.Add(100, new DateTime(2001, 1, 1));
	table.Rows.Add(200, new DateTime(2002, 1, 1));
	table.Rows.Add(300, new DateTime(2003, 1, 1));

	// Select by date.
	DataRow[] result = table.Select("Date > #6/1/2001#");

	// Display.
	foreach (DataRow row in result)
	{
	    Console.WriteLine(row["ID"]);
	}
    }
}

Output

200
300

Syntax. Select uses SQL-style syntax but because it is inside a string literal, you sometimes need to escape quotation marks. Some values, like characters, may need to be quoted. The "AND" and "OR" operators can be used as in SQL.

Tip: There is an example of Select with DateTime filters on MSDN. The hash character "#" surrounds the DateTime.

DataTable.Select: MSDN

EvaluateException. Many exceptions are thrown by DataTable and its methods. One exception, EvaluateException, is caused when an invalid expression is passed to the Select method. The expression must have a Boolean result.

First: This example uses an invalid expression in Select. The expression "A" does not evaluate to true or false.

And: The program causes a nasty error when it is executed. After this example, we fix the error.

C# program that causes EvaluateException

using System.Data;

class Program
{
    static void Main()
    {
	// Create simple DataTable.
	DataTable table = new DataTable();
	table.Columns.Add("A", typeof(int));
	table.Rows.Add(1);
	table.Rows.Add(2);
	table.Rows.Add(3);

	// Call Select.
	DataRow[] rows = table.Select("A");
	System.Console.WriteLine(rows.Length);
    }
}

Output

Unhandled Exception: System.Data.EvaluateException:
Filter expression 'A' does not evaluate to a Boolean term.
   at System.Data.Select.AcceptRecord(Int32 record)
   at System.Data.Select.GetLinearFilteredRows(Range range)
   at System.Data.Select.SelectRows()
   at System.Data.DataTable.Select(String filterExpression)

Fixing the error. Here we change the argument to Select to be in the form of a predicate. The statement A > 1 returns true for two rows in the DataTable—where the column A has cell values of 2 and 3. No EvaluateException is triggered.

Note: Programs that use DataTable are vulnerable to many exceptions. All the code must be exactly correct or an error will occur.

Note 2: The expression in Select must evaluate to true or false—otherwise, an EvaluateException is encountered.

Statements that work correctly

// Call Select.
DataRow[] rows = table.Select("A > 1");
System.Console.WriteLine(rows.Length);

Output

2

Summary. The Select method receives a predicate expression in the SQL special form and returns an array of DataRow instances. If you specify an invalid column name, it will throw an exception. It also throws EvaluateException.

Review: Select provides another way of filtering DataTables, separate from imperative testing with if-statements and loops.

Related Links