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.
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.
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.
Select
and pass a string
argument. We use the a string
parameter that checks Size
and Team for the selection.Select()
returns an array of DataRow
instances where every Player has those matching characteristics.Console
with Console.WriteLine
and a format string
.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]); } } }250, b 310, b
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.
string
is surrounded by pound "#" symbols. This syntax is important.Select
queries the DataTable
and finds two matching rows. Both of them have a date more recent than 6/1/2001.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"]); } } }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.
Select
. The expression "A" does not evaluate to true or false.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); } }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)
Here we change the argument to Select
to be in the form of a predicate. The statement compares A against the value 1.
EvaluateException
is triggered.DataTable
are vulnerable to many exceptions. All the code must be exactly correct or an error will occur.Select
must evaluate to true or false—otherwise, an EvaluateException
is encountered.// Call Select. DataRow[] rows = table.Select("A > 1"); System.Console.WriteLine(rows.Length);2
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 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.