System.Data Namespace   DataColumn Class

Sets or retrieves the expresssion used to filter rows, calculate the values in a column, or create an aggregate column.

The property is read/write with no default value.

One use of the Expression property is to create calculated columns. For example, to calculate a tax value, the unit price is multiplied by a tax rate of a given region. Since tax rates vary from region to region, it would be impossible to put a single tax rate in a column; instead, the value is calculated using the Expression property, as shown in the Visual Basic code below:

myDataSet.Tables ( "Products ).Columns ( "tax" ).Expression = 
   "UnitPrice * 0.086"

A second use is to create an aggregate column. Similar to a calculated value, an aggregate performs an operation based on the entire set of rows in the DataTable. A simple example is to count the number of rows returned in the set, which is the method you would use to count the number of transactions completed by a particular salesperson, as shown in this Visual Basic code:

myDataSet.Tables ( "Orders" ).Columns ( "OrderCount" ).Expression = 
   "Count ( OrderID ) "

When creating an expression, use the ColumnName property to refer to columns. For example, if the ColumnName for one column is "UnitPrice", and another "Quantity", the expression would be:

"UnitPrice * Quantity"

When creating an expression for a filter, enclose strings with single quotes:

"LastName = 'Jones'"

The following characters are special characters and must be escaped, as explained below, if they are to be used in a column name:

• \n ( newline )
• \t ( tab )
• \r ( carriage return )
• ~
• (
• )
• #
• \
• /
• =
• >
• <
• +
• -
• *
• %
• &
• |
• ^
• '
• "
• [
• ]

If a column name contains one of the above characters, the name must be wrapped in brackets. For example to use a column named "Column#" in an expression, you would write " [ column# ] ":

Total * [ column# ]

Because brackets are special characters, you must use a slash ( "\" ) to escape the bracket, if it is part of a column name. For example, a column named "Column [ ] " would be written:

Total * [ column [ \ ] ]

( Only the second bracket must be escaped. )

User-defined values may be used within expressions to be compared against column values. String values should be enclosed within single quotes. Date values should be enclosed within pound signs ( # ). Decimals and scientific notation are permissible for numeric values. For example:

"FirstName = 'John'"
"Price <= 50.00"
"Birthdate < #1/31/82#"

For columns that contain enumeration values, cast the value to an integer data type. For example:

"EnumColumn = 5"

Concatenation is allowed using Boolean AND, OR, and NOT operators. You can use parentheses to group clauses and force precedence. The AND operator has precedence over other operators. For example:

 ( LastName = 'Smith' OR LastName = 'Jones' ) AND FirstName = 'John'

When creating comparison expressions, the following operators are allowed:

• <
• >
• <=
• >=
• <>
• =
• IN
• LIKE

The following arithmetic operators are also supported in expressions:

• + ( addition )
• - ( subtraction )
• * ( multiplication )
• / ( division )
• % ( modulus )

To concatenate a string, use the + character. Whether string comparisons are case-sensitive or not is determined by the value of the DataSet class's CaseSensitive property. However, you can override that value with the DataTable class's CaseSensitive property.

Both the * and % can be used interchangeably for wildcards in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be escaped in brackets ( [ ] ). If a bracket is in the clause, the bracket characters should be escaped in brackets ( e.g. [ [ ] or [ ] ] ). A wildcard is allowed at the beginning and end of a pattern, or at the end of a pattern, or at the beginning of a pattern. For example:

"ItemName LIKE '*product*'"
"ItemName LIKE '*product'"
"ItemName LIKE 'product*'"

Wildcards are not allowed in the middle of a string. For example, 'te*xt' is not allowed.

A column in a child table may be referenced in an expresion by prepending the column name with "Child". For example, "Child.Price" would reference the column named Price in the child table.

If a table has more than one child, the syntax is: Child ( RelationName ). For example, if a table has two child tables named Employee and Titles, and the DataRelation objects are named "Publishers2Employee" and "Publishers2Titles", the reference would be:

Child ( Publishers2Employee ).fname
Child ( Publishers2Titles ).title

A parent table may be referenced in an expression by prepending the column name with "Parent". For example, the "Parent.Price" references the parent table's column named "Price".

The following aggregate types are supported:

• Sum ( Sum )
• Avg ( Average )
• Min ( Minimum )
• Max ( Maximum )
• Count ( Count )
• StDev ( Statistical standard deviation )
• Var ( Statistical variance ).

Aggregates are usually performed along relationships. Create an aggregate expression by using one of the functions listed above and a child table column as detailed in PARENT/CHILD RELATION REFERENCING above. For example:

Avg ( Child.Price )
Avg ( Child ( Orders2Details ).Price )

An aggregate can also be performed on a single table. For example, to create a summary of figures in a column named "Price":

Sum ( Price )

NOTE: If you use a single table to create an aggregate, there would be no group-by functionality. Instead, all rows would display the same value in the column.

If a table has no rows, the aggregate functions will return a null reference.

Data types can always be determined by examining the DataType property of a column. You can also convert data types using the Convert function, shown below.

The following functions are also supported:

CONVERT

Example:

myColumn.Expression="Convert ( total, 'System.Int32' ) "

All conversions are valid with the following exceptions: Boolean can be coerced to and from Byte, SByte, Int16, Int32, Int64, UInt16, UInt32, UInt64, String and itself only. Char can be coerced to and from Int32, UInt32, String, and itself only. DateTime can be coerced to and from String and itself only. TimeSpan can be coerced to and from String and itself only.

LEN

Example: 

myColumn.Expression="Len ( ItemName ) "

ISNULL

Example: 

myColumn.Expression="IsNull ( price, -1 ) "

IIF

Example: 

myColumn.Expression = "Iif ( total>1000,'expensive','dear' ) TRIM

SUBSTRING

Example: 

myColumn.Expression = "SUBSTRING ( phone, 7, 8 ) "

NOTE: You can reset the Expression property by assigning it a null value or empty string. If a default value is set on the expression column, all previously filled rows are assigned the default value after the Expression property is reset.

The following example initializes three coumns in a DataTable. The second and third columns contain expressions; the second calculates tax using a variable tax rate, and the third adds the result of the calculation to the value of the first column. The resulting table is displayed in a DataGrid control.

private void CalcColumns ( ) {
   DataColumn cPrice;
   DataColumn cTax;
   DataColumn cTotal;
   DataTable myTable = new DataTable ( );

   // create the first column.
   cPrice = new DataColumn ( );
   cPrice.DataType = System.Type.GetType ( "System.Decimal" );
   cPrice.ColumnName = "price";
   cPrice.DefaultValue = 50;

   // create the second, calculated, column.
   cTax = new DataColumn ( );
   cTax.DataType = System.Type.GetType ( "System.Decimal" );
   cTax.ColumnName = "tax";
   cTax.Expression = "price * 0.0862";

   // create third column.
   cTotal = new DataColumn ( );
   cTotal.DataType = System.Type.GetType ( "System.Decimal" );
   cTotal.ColumnName = "total";
   cTotal.Expression = "price + tax";

   // add columns to DataTable.
   myTable.Columns.Add ( cPrice );
   myTable.Columns.Add ( cTax );
   myTable.Columns.Add ( cTotal );
   DataRow myRow;
   myRow = myTable.NewRow ( );
   myTable.Rows.Add ( myRow );
   DataView myView = new DataView ( myTable );
   myGrid.DataSource = myView;
}

Private Sub CalcColumns ( )
   Dim cPrice As DataColumn
   Dim cTax As DataColumn
   Dim cTotal As DataColumn
   Dim rate As Single
   rate = .0862
   dim t as DataTable = New DataTable

   ' create the first column.
   cPrice = New DataColumn
   With cPrice
      .DataType = System.Type.GetType ( "System.Decimal" )
      .ColumnName = "price"
      .DefaultValue = 50
   End With

   ' create the second, calculated, column.
   cTax = New DataColumn
   With cTax
      .DataType = System.Type.GetType ( "System.Decimal" )
      .ColumnName = "tax"
      .Expression = "price * 0.0862"
   End With

   ' create third column
   cTotal = New DataColumn
   With cTotal
      .DataType = System.Type.GetType ( "System.Decimal" )
      .ColumnName = "total"
      .Expression = "price + tax"
   End With

   ' add columns to DataTable
   With t.Columns
      .Add ( cPrice )
      .Add ( cTax )
      .Add ( cTotal )
   End With

   Dim r As DataRow
   r = t.NewRow
   t.Rows.Add ( r )
   Dim dView As New DataView
   dView.Table = t
   myGrid.DataSource = dView
End Sub

DataColumn Members

Books and more ...

Custom Search