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:
- UnitPrice * 2
- UnitsInStock + UnitsOnOrder
- ProductName + ' Hello'
- ((UnitPrice * Quantity * (1 - Discount)) / 100) * 100
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:
- + (addition)
- - (subtraction)
- * (multiplication)
- / (division)
- % (modulus, the remainder after division)
String Operator:
- + (concatenation, joining strings)
Comparison Operators:
- < (less than)
- > (greater than)
- <= (less than or equal to)
- >= (greater than or equal to)
- <> (not equal to)
- = (equal)
- IN (compare to a list of items)
- LIKE (pattern matching)
Scalar Functions:
- Convert (change from one data type to another)
- Len (return the length of the string)
- IsNull (test for null)
- IIf (Immediate If)
- Trim (trim white space from string)
- Substring (return a part of a string)
Aggregation Functions:
- Sum (Sum)
- Avg (Average)
- Min (Minimum)
- Max (Maximum)
- Count (Count)
- StDev (Standard deviation)
- Var (Statistical variance)
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:
- Strings are enclosed in single quotes (i.e. 'Hello')
- Reserved words are escaped by the square brackets '[' and ']'.
- Dates are enclosed in pound signs (i.e. #08/09/2005#)
- String patterns can use either the '*' or '%' wildcard character (but must be at the beginning or end of the string)
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