DataTable and DataColumn Expressions in ADO.NET - Calculated Columns
by David Hayden ( Florida .NET Developer )
A question came up in the MSDN forums as to how to handle Data Column Expressions within a DataTable in ADO.NET. The expression syntax available to create calculated columns is probably much richer than you might think if you don't play around a lot with the Expression Property of the DataColumn.
The question was how to create a Calculated ColumnC that had the following logic:
- If ColumnA - ColumnB > 100, ColumnC = “Yes“, else ColumnC = “No“
We can create a DataTable to do this in only a few lines of code:
// Create the DataTable
DataTable dt = new DataTable("Expressions");
// Create ColumnA and ColumnB
DataColumn columnA =
new DataColumn("columnA", typeof(int));
DataColumn columnB =
new DataColumn("columnB", typeof(int));
// Create ColumnC
DataColumn columnC =
new DataColumn("columnC", typeof(string),
"IIF(columnA - columnB > 100,'Yes','No')");
// Add Columns to DataTable
dt.Columns.AddRange(new DataColumn[]
{ columnA, columnB, columnC });
// Add a Couple of Rows Supplying ColumnA and ColumnB Data...
dt.Rows.Add(new object[] { 200, 50 });
dt.Rows.Add(new object[] { 100, 20 });
As you can see above, there is an IIF Function available to us that populates the column based on whether an expression is true or false.
IIF(expr, truepart, falsepart)
expr -- The expression to evaluate.
truepart -- The value to return if the expression is true.
falsepart -- The value to return if the expression is false.
I wrote the expression as follows:
IIF(columnA - columnB > 100,'Yes','No')
Using the DataSet Visualizer in VS2005 we can see the results of the DataColumn Expression:
Conclusion
Creating Calculated Columns in a DataTable using the DataColumn.Expression Property is really cool and the Expression Syntax available may be a lot richer than you might think. For more on the functions and operators supported by the DataColumn.Express Property, see the following documentation on MSDN.
Related ADO.NET Tutorials
Source: David Hayden ( Florida .NET Developer )
Filed: ADO.NET Tutorials