ADO.Net supports a fairly sophisticated set of Expressions. Expressions can be used to transform the common DataTable into a powerful tool for manipulating and presenting data.

This might be a good time to review Beginning ADO.Net or Saving Data in ADO.Net

Expressions

Expressions allow you to use the names of columns in a DataTable as variables in an equation. Examples of expressions are:

The underlying data type of the column is used in the expression (in other words, if the column in the table is an integer, the expression uses that column name as an integer variable)

To add expression support to a DataTable, you just add a new DataColumn and fill in the Expression property. The DataTable will now spring to life and evaluate the expression whenever the DataTable is used.

Expressions can also be used with the DataTable's Compute method to evaluate a "scalar" expression (one that returns just a single value).

Expression Operators

The list of supported operators is somewhat small:

Math Operators:

String Operator:

Comparison Operators:

Scalar Functions:

Aggregation Functions:

Aggregate functions return the same value for each row, so if you used the Sum() function in a DataColumn, the values for each row would always be the same for that column. They are most often used by the Compute method of the DataTable class.

Note: Aggregates use only a single column name as an argument, so an expression like Avg(UnitPrice *2) will not work.

Constants

The rules for constants are as follows:

Note: The variable names (the column names) inside expressions are not quoted, and the square brackets '[' and ']' are already used to escape "reserved words". So, a column name of "Order Details" must be converted to a very ugly "[[]Order Details[]]"

Relationships

One of the more powerful features of Expressions is the ability to make reference to not just the column names of the current DataTable, but also the column names of related DataTables in the same DataSet. This means that by using DataRelations and Expressions you can essentially do an SQL "INNER JOIN" on two tables!

The use of Relationships in Expressions is not covered by this article. Read a related article on Using ADO.Net DataRelations

Documentation Links

The Example Code

Let's look at a simple example. The first thing we do is just fill a few DataTables to play with (nothing fancy here...)

Dim ds As New DataSet
Dim con As New OleDbConnection
Dim da As New OleDbDataAdapter
Dim cmd As New OleDbCommand

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\NorthWind.mdb"
cmd.Connection = con
da.SelectCommand = cmd
da.MissingSchemaAction = MissingSchemaAction.AddWithKey

' fill a few tables from the Northwind database
con.Open()
cmd.CommandText = "Select * from Categories"
da.Fill(ds, "Categories")
cmd.CommandText = "Select ProductName, CategoryID, UnitPrice, UnitsInStock," _ 
 & UnitsOnOrder from Products"
da.Fill(ds, "Products")

Next we create a few new DataColumns in our Products DataTable to hold the expressions:

Dim dc As DataColumn

' add on a few simple expression columns
dc = New DataColumn("UnitPriceX2")
dc.DataType = GetType(Double)
dc.Expression = "UnitPrice * 2" ' column and numeric constant
ds.Tables("Products").Columns.Add(dc)

dc = New DataColumn("TotalStock")
dc.DataType = GetType(Integer)
dc.Expression = "UnitsInStock + UnitsOnOrder" ' two column types
ds.Tables("Products").Columns.Add(dc)

dc = New DataColumn("Hello")
dc.DataType = GetType(String)
dc.Expression = "ProductName + ' Hello'" ' column and string constant
ds.Tables("Products").Columns.Add(dc)

' add on a few aggregation expression columns
dc = New DataColumn("AvgPrice")
dc.DataType = GetType(Double)
dc.Expression = "Avg(UnitPrice)" ' average (shows in all rows)
ds.Tables("Products").Columns.Add(dc)

dc = New DataColumn("SumStock")
dc.DataType = GetType(Integer) ' sum (shows in all rows)
dc.Expression = "Sum(UnitsInStock)"
ds.Tables("Products").Columns.Add(dc)

' display the results in a DataGrid
DataGrid1.DataSource = ds.Tables("Products")

That's it... the DataTable will now evaluate the expressions and show the results in the DataGrid. The evaluation is "live"... meaning that if you change a value in the DataGrid, the expressions are immediately reevaluated and the updated results are displayed in the DataGrid.

Using the Compute method is very similar to the ExecuteScalar method, except ExecuteScalar makes a round trip to the underlying database and Compute operates on the locally-cached DataTable

Dim InventoryValue As Double

' let's use the Compute() method of the DataTable to do some math
InventoryValue = ds.Tables("Products").Compute("Avg(UnitPrice) * Sum(UnitsInStock)", "")
MsgBox(InventoryValue.ToString("C"))

The Compute method also supports a second argument to allow you to apply a filter to the rows of data used during the execution of the expression.

Downloads/Links

Read a related article on Using ADO.Net DataRelations
Download the complete VB.Net Source code example used in this article: Expressions.zip