.NET Reference Guide

DataColumn Expressions

Last updated Sep 23, 2004.

DataColumn Expressions

Most of the columns in your data tables will be simple columns that map directly to columns in a database table. There are circumstances that, just as with a database, you will want to include a computed column in a table. For example, you might want a column to contain a count, average, or other aggregate expression, or you might want a column to contain data from a column in a related table. This is possible by setting the Expression property of a DataColumn.

The DataColumn.Expression property uses its own syntax, which is "similar to" SQL, but different in many ways. You can find a detailed explanation of the syntax in the DataColumn.Expression online help topic. The remainder of this topic will expand on that discussion somewhat.

Creating Computed Columns

The simplest type of expression is probably the calculated column. For example, in the sample program provided with the discussion of Relationships, the code that outputs order items calculates the extended price (quantity times price) in C# like this:

int qty = (int)orderItem["Quantity"];
System.Decimal price = (Decimal)orderItem["Price"];
Decimal extendedPrice = qty * price;

There's nothing "wrong" with writing such code, but it has one major drawback: every time you want to know the extended price, you have to compute it. It'd be much nicer if there were a column in the table that contained the computed value. That way, the extended price would be recomputed every time the price or quantity changed. Adding such a column is extremely easy, as shown here:

// Create computed columns
DataTable tblOrderItems = ds.Tables["OrderItems"];
tblOrderItems.Columns.Add("ExtendedPrice", typeof(Decimal), "Price * Quantity");

If you add that line of code to the sample program after the Products primary key is created, you can then change the output code in the innermost loop to the single statement below, eliminating the three lines that were used to compute the value in the previous version of the program:

Console.WriteLine("\t\t{0} {1} ({2}) {3} {4}",
    orderItem["Quantity"], productRow["ProductNumber"],
    productRow["Description"], orderItem["Price"], orderItem["ExtendedPrice"]);

You can also concatenate strings in DataColumn expressions. For example, if you want to display a customer's name as LastName comma FirstName, you could do it by constructing the string, or you could create a computed column called "FullName", like this:

tblCustomers.Columns.Add("FullName", typeof(string),
    "LastName + ', ' + FirstName");

The "+" operator is used for string concatenation and literal strings in expressions are enclosed in apostrophes (single quotes).

Aggregates and Related Columns

Computed columns are handy, but that's not all that you can do with DataColumn expressions. As with SQL, you can create columns that contain aggregates: counts of items, sums, averages, and such. The documentation contains a full list of supported aggregates. I've found that aggregates are most useful when used with related columns. For example, a count of orders for a customer. If you were writing code to display each customer and the number of orders that the customer had placed, you would have to loop through the Orders table to count the orders, or use a DataView or DataTable.Select statement that gets the items. It's possible, though, to create an aggregate within the DataTable itself, giving you the benefit of not having to compute the value manually. For example, the code below adds the count of orders to the Customers table:

// Add OrderCount column to Customers table
tblCustomers.Columns.Add("OrderCount", typeof(int),
    "Count(Child(CustomerOrders).OrderId)");

The syntax here can get a bit confusing, and unfortunately the compiler can't check it for you. The only way you'll know if you got the expression wrong is if you try to run the program. The error messages that result from a bad expression are sometimes rather cryptic, so it's a good idea to double check your work before you compile and run. There is one good thing, though: case is not significant in expressions, except in quoted strings. So "Count" and "cOUnt" are the same as "count".

There are two ways to reference a relationship within an expression. If there is only one related table, you could write Child.Column. If there are multiple relationships, the syntax is Child(RelationshipName).Column. Since you never know when you might add a relationship, it's a good idea to always use the fully qualified syntax like I did in the example above.

Functions

DataColumn expressions recognize a handful of functions that make some things easier. Most of the functions are pretty self explanatory, especially if you're familiar with SQL syntax. But two of them, ISNULL and IIF can be somewhat confusing to the uninitiated.

You might expect ISNULL to return a Boolean value: true if the expression is NULL and false if it is non-NULL. (NULL in this case is the SQL meaning rather than the C# value null or Visual Basic Nothing.) But that's not how ISNULL works. ISNULL takes two parameters: an expression and a replacement value. If the expression resolves to a non-NULL value, then the value of the expression is returned. If the expression resolves to NULL, the replacement value is returned. For example, suppose you want to display the Company column from the Customers table, but if the Company column is NULL, you want to display "None". The resulting DataColumn expression would be:

"IsNull(Company, 'None')"

Another use of ISNULL (and expressions in general) is in DataView.RowFilter expressions. If you wanted to create a DataView that references all of the Customer rows that have no company name, you would include this filter expression:

"IsNull(Company, 'None') = 'None'"

The IIF function returns one of two values depending on the result of a logical expression. For example, suppose your order entry system required management review of all order items greater than $10,000. You could write code to check the order items individually, or you could include a column in the table so that items could easily be flagged whenever the extended price changes.

tblOrderItems.Columns.Add("MgmtReview", typeof(Boolean),
    "IIF(ExtendedPrice>10000.00, true, false)");

The returned values don't have to be Boolean values. The above column could just as easily have been a string or a character flag or a numeric value. One possible use would be to compute a discount off the total price based on the amount of the order. In that case, you'd create a Discount column that's computed from the ExtendedPrice, and then create another column called FinalPrice (for example) that applies the Discount to the ExtendedPrice. Chained computed columns.

Conclusion

DataColumn expressions can turn your boring DataSets into dynamic views of data, with columns that update automatically as other data columns change, removing much of the tedium associated with writing code to report such changes. Be aware, though, that this doesn't come for free. There is some CPU overhead associated with computed columns, especially with aggregates, and in rare cases you may find yourself having to remove columns during intensive update procedures and adding them afterwards.