DataTable Select

A DataTable stores rows and columns of certain types of data. It contains other data—it is a collection. But the DataTable also provides search functionality.

With the Select Function, we query a DataTable for rows that match a condition. We can then loop over the resulting array of DataRow objects.

First example

We create a DataTable with the name "Players." We add 2 columns—each row will store a "Size" and "Team" value. We call Rows.Add 5 times to populate the collection with data.

Module Module1

    Sub Main()
        ' Create a table.
        Dim table As DataTable = New DataTable("Players")

        ' Add 2 columns.
        table.Columns.Add(New DataColumn("Size", GetType(Integer)))
        table.Columns.Add(New DataColumn("Team", GetType(Char)))

        ' Add 5 rows.
        table.Rows.Add(100, "a"c)
        table.Rows.Add(235, "a"c)
        table.Rows.Add(250, "b"c)
        table.Rows.Add(310, "b"c)
        table.Rows.Add(150, "b"c)

        ' Get players above 230 size with "b" team.
        Dim result() As DataRow = table.Select("Size >= 230 AND Team = 'b'")

        ' Loop and display.
        For Each row As DataRow In result
            Console.WriteLine("{0}, {1}", row(0), row(1))
        Next
    End Sub

End Module
250, b
310, b

DateTime example

Next we use the Select Function with a DateTime. We create a new DataTable storing Widget model information. We populate it with three rows.

Module Module1

    Sub Main()
        ' Create a table.
        Dim table As DataTable = New DataTable("Widgets")

        ' Add 2 columns.
        table.Columns.Add(New DataColumn("ID", GetType(Integer)))
        table.Columns.Add(New DataColumn("Date", GetType(DateTime)))

        ' Add rows.
        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))

        ' Get rows more recent than 6/1/2001.
        Dim result() As DataRow = table.Select("Date > #6/1/2001#")

        ' Loop.
        For Each row As DataRow In result
            Console.WriteLine(row(0))
        Next
    End Sub

End Module
200
300

EvaluateException

We must specify a known column name in the Select expression. If we use a nonexistent column name like "X" here, an EvaluateException is thrown.

Module Module1

    Sub Main()
        Dim table As DataTable = New DataTable("Players")

        ' We must use known columns, or an EvaluateException will occur.
        Dim result() As DataRow = table.Select("X")
    End Sub

End Module
Unhandled Exception: System.Data.EvaluateException: Cannot find column [X].
   at System.Data.NameNode.Bind(DataTable table, List`1 list)
   at System.Data.DataExpression.Bind(DataTable table)
   at System.Data.DataExpression..ctor(DataTable table, String expression, Type type)
   at System.Data.DataTable.Select(String filterExpression)

Notes, syntax

In the examples, we have seen the "AND" operator. We have also done numeric comparisons and date comparisons. Another supported operator for Select is the "OR" operator.

A DataTable is more than a container for data objects. It provides functionality that helps you search for matching rows. The syntax is like that of an SQL query.