C# DataTable Select Example

Use Select on a DataTable instance. Understand the syntax and EvaluateExceptions.

DataTable, Select. DataTable has a Select method. This method receives a string expression that specifies what rows we want to handle. Select() makes DataTables act like small databases.DataTable

Some warnings. With Select, we must specify valid SQL-like strings. Otherwise an EvaluateException will be thrown. Select() is a powerful method, but not easy to use.

First example. We create a DataTable with 5 rows and 2 columns. Each Player is added with a Size and Team field. Notice how there are 2 Team "a" rows and 3 Team "b" rows.

Argument: We call Select and pass a string argument. We use the parameter "Size >= 230 and Team = m" for the selection.

Result: Select() returns an array of DataRow instances where every Player has those matching characteristics.

DataRow

Console: We print the results to the Console with Console.WriteLine and a format string.

Console

C# program that uses DataTable Select method using System; using System.Data; class Program { static void Main() { // Create a table of 5 different players. // ... Store their size and team. DataTable table = new DataTable("Players"); table.Columns.Add(new DataColumn("Size", typeof(int))); table.Columns.Add(new DataColumn("Team", typeof(char))); table.Rows.Add(100, 'a'); table.Rows.Add(235, 'a'); table.Rows.Add(250, 'b'); table.Rows.Add(310, 'b'); table.Rows.Add(150, 'b'); // Search for players above a certain size. // ... Require certain team. DataRow[] result = table.Select("Size >= 230 AND Team = 'b'"); 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.DateTime

Syntax: Please notice how the date string is surrounded by pound "#" symbols. This syntax is important.

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

EvaluateException. Many exceptions are thrown by DataTable. An EvaluateException is caused when an invalid expression is passed to Select(). The expression must have a Boolean result.Exception

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.

Tip: True is returned 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

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.

AND, OR: The "AND" and "OR" operators can be used as in SQL. These are part of expressions.

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

DataTable.Select: Microsoft Docs

A summary. The Select method receives a predicate expression in the SQL special form. It returns an array of DataRows. If you specify an invalid column name, it will throw an exception.

Some expressions will result in EvaluateExceptions. Select provides another way of filtering DataTables, separate from imperative testing with if-statements and loops.

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

C# DataTable Select Example

Related Links:

.Net

Java

Related Links