C# DataTable Select

Select method call

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.

DataTable

Example

Steps

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
Program that uses DataTable Select method: C#

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

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:The Select method queries the DataTable and finds two matching rows. Both of them have a date more recent than 6/1/2001.

Program that uses Select, date: C#

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

Programming tip

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

Summary

C# programming language

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.

EvaluateException

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


C#: Data