Expression columns are a useful feature of ADO.NET that allows developers to define calculated columns that are maintained on the client side using local data. This is a significant improvement over performing calculations within a SQL query, because expression columns can recalculate based on changes to local data without requiring a round trip to the database server, and less data needs to be transferred from server to client. Besides being able to perform simple arithmetic on values within the same row, expressions can also retrieve values from parent and child rows in a relational dataset, thus providing lookup and aggregation functionality as well. For detailed information on expression column syntax, see the DataColumn.Expression property help topic.
As useful as expression columns can potentially be, however, they come with a substantial limitation: they cannot be used in conjunction with an insert or update query that automatically refreshes row values from the database server. This is because of the way that the .NET framework applies the retrieved values during an update operation, which is to iterate through all columns in the table, expression columns included, and update their values, temporarily setting the ReadOnly property to false. When this operation is attempted on an expression column, the following exception message will result: “Cannot change ReadOnly property of the expression column.” This has been a known bug since 2002, and Microsoft has supposedly promised a fix for the next version of .NET, although it is unclear whether they mean the next version of the framework or of Visual Studio. Since the bug still exists in Visual Studio 2003 and .NET framework 1.1, one can only hope that this bug will be fixed in Visual Studio 2005 and .NET framework 2.0. Until then, there are several workarounds possible, which will be explained in the following paragraphs.
Since this bug occurs when expression columns are mixed with insert or update queries that refresh the dataset, the only solution is to remove one of the two factors. The first and simplest solution is to remove the select query from the end of the insert or update query. However, this solution has a drawback in that the row will not be refreshed, which can be especially vexing if the database is generating the primary key field.
The next solution takes the previous one a step further by writing custom code in the RowUpdated event. The RowUpdated event fires after changes have been applied to the database. Custom code could be added to this event to more selectively and intelligently update only the fields in the dataset that correspond to fields in the database. For example:
private void sqlDataAdapter1_RowUpdated(object sender,
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
DataColumn dc = e.Row.Table.Columns[ "RowID" ];
bool readOnly = dc.ReadOnly;
dc.ReadOnly = false;
try
{
SqlCommand cmd = new SqlCommand( "select @@IDENTITY", conn );
e.Row[ "RowID" ] = cmd.ExecuteScalar();
e.Row.AcceptChanges();
}
finally
{
dc.ReadOnly = readOnly;
}
}
Another possibility is to remove the expression columns from the bug-causing equation, rather than removing the automatically supported dataset refresh. In this approach, each call to DataAdapter.Update() would be bracketed by deletion and recreation of all expression fields in the DataSet. For example:
ArrayList al = new ArrayList();
// Save expression columns
foreach ( DataColumn dc in ds.Table1.Columns )
if ( dc.Expression != "" )
al.Add( dc );
// Delete saved columns from collection
foreach ( object o in al )
ds.Table1.Columns.Remove( (DataColumn) o );
try
{
da.Update( ds.Table1 );
}
finally
{
// Restore saved columns
foreach ( object o in al )
ds.Table1.Columns.Add( (DataColumn) o );
}
Lastly, some expression column functionality can be duplicated through custom data binding expressions. To do this, it is first necessary to explain the syntax data bindings created at design time. When creating a simple data binding through the Data Bindings dialog, Visual Studio creates a DataBinder.Eval() method call. The role of the DataBinder class is to simplify writing data binding expressions by eliminating two tasks required by data binding expressions that do not use DataBinder. These two tasks are: casting the object containing the data, and converting the data to the correct data type, which is typically a string. To see these advantages at work, consider the following line of code, which is a typical example of the kind of expression required to bind non-string data in a repeating control such as a DataList:
lblDisplay.Text =
((DataRowView) container.DataItem)[ "IntField" ].ToString();
If special formatting was needed, then the code would expand to the following:
lblDisplay.Text =
String.Format("{0:d10}",
((DataRowView) container.DataItem)[ "IntField" ] );
This same task can be accomplished by using DataBinder.Eval(), as shown in the following example:
lblDisplay.Text =
DataBinder.Eval( container.DataItem, "IntField", "{0:d10}" );
The benefit is that explicit casting isn’t necessary and the format string may be accessed as an extra parameter of the Eval() method rather than requiring a call to the static String.Format() method. However, the savings in typing have a cost in runtime speed. When writing your own custom data binding expressions, consider using the longer form as a way to improve application performance.
What is important to understand when writing custom data binding expressions is that there isn’t anything magic about using DataBinder.Eval(). Any expression written in the page language that returns a value can be used as a custom data binding expression. Thus, to perform a simple lookup in a data binding expression, enter the following expression as a custom data binding expression. Assume the dataset ds contains two tables named Table and Lookup, with a relationship defined between them with the default name LookupTable. The example code returns the value of the field LookupValue in the table Lookup.
// Typed dataset syntax
ds.Table[0].GetLookupRow.LookupValue
// Untyped dataset syntax
ds.Tables["Table"].Rows[0].GetParentRow("LookupTable")["LookupValue"]
Likewise, aggregate expression columns can be replaced with equivalent DataTable.Compute() method calls. To extend the above example, assume that the table called Table has a child table called Child containing a field called Amount and related by the field TableID. The relationship in the typed dataset is called TableChild. The following expression column (defined in Table) and DataTable.Compute() calls will yield the same results:
// Expression column expression
Sum( Child( TableChild ).Amount )
// Equivalent DataTable.Compute() for the current TableRow
Child.Compute( "Sum( Amount )", "TableID=" + TableRow.TableID );
Armed with this knowledge, it may be possible to entirely avoid the need for creating any persistent expression columns in the dataset.
Expression columns provide useful and powerful tools to create columns that can recalculate themselves in response to data changes without making a round trip to the database server while keeping the size of the data transmitted as compact as possible. However, in the current version of .NET, they come with significant limitations that will require extra effort on the developer’s part. If you need both the functionality of expression columns and to refresh row values from the database on insert or update, the approaches described above will provide you with several options with which to work around this bug. Here’s hoping it really gets fixed in the next version!