Creating Computed Columns in a DataSet

DataSets are very powerful - and one of the features that makes it so powerful is computed columns. You can create a DataColumn that does not contain storage of data but rather computes a value dynamically.

Computations can be done by SQL on retrieval, however once the data is in a DataSet, those computations are outdated. As the data changes in your DataSet, it is desirable to have the latest results of a calculation.

One of the properties of a DataColumn is "Expression". This is a string that stores a computational expression. The data type of the column should match the data type of the computed value. These expressions can calculate simple values, or you can use aggregate functions across an entire set of data. You can even use computed columns to lookup a value in a parent table.

The source code below contains three snippets of code that add computed columns to some tables. Listing 1 is a simple expression that calculates the extended price on an Order Item by multiplying the quantity by a unit price. The column is first created and then added to the table. 

The first listing assumed there was not only a Quantity column on the OrderItem Table, but also that there was a Price column. In all likelihood, you would need to look this up on a related product table assuming the OrderItem table had a foreign key to a Product Table. If you have set your DataSet up with a DataRelation for this foreign key, you can perform this lookup within the DataSet. In Listing 2, we create a computed Price column on the OrderItem table that looks up the Price on the Product Table. The syntax for referencing the rows in a parent table is:

 Parent(DataRelationName).parentcolumn 

...where DataRelationName is the name of the DataRelation between the parent and child tables. To perform parent table referencing, it is mandatory that a DataRelation is established between the parent and child tables.

The final example in Listing 3, shows a computed column that is a total of all the order item amounts for a given OrderHeader. Assuming we have created a DataRelation for the foreign key between OrderHeader and OrderItem, we can perform this child table reference. The syntax is similar to the parent relation but you use the keyword Child.

 Child(DataRelationName).parentcolumn

You should note that when referencing a child table, you are normally referring to many rows (possibly many anyway). Therefore any expressions on a child table should be done within an aggregate expression (e.g. Sum, Min, Max, Avg, etc.).

The benefit of performing your calculations within the DataSet instead of the Database is that changing values in the base columns of the DataSet will have an instant impact on your calculations. For example, users may want to know the total of an Order before they actually save it in the database. In the second example, if you change the product_id being order, the unit price and extended price will automatically change to reflect the price of the new product.

Finally, if you place this code to modify your DataSet  within you Business Logic Layer, you are able to place the critical business logic of calculations in your business objects along with the rest of your business logic, not hidden away in your Data Access Layer or database stored procedures.

You should note that in the current version of the Visual Studio.NET IDE, there is a bug with computed columns and typed Datasets. You can add the computed column to your DataSet in the schema editor, but if you specify the expression, you will get compile time errors. You should specify the expression at runtime.

[Listing 1]
stDecimal = System.Type.GetType("System.Decimal");
DataColumn dcExtPrice = new DataColumn("ExtPrice", stDecimal, "Price * Quantity", MappingType.Attribute);
dcExtPrice.AutoIncrement = false;
dcExtPrice.ReadOnly = true;
DataSet1.Tables["OrderItem"].Columns.Add(dcExtPrice);
[Listing 2]
stDecimal = System.Type.GetType("System.Decimal");
DataColumn dcPrice = new DataColumn("Price", stDecimal, "Parent(drProduct_OrderItem).Price", MappingType.Attribute);
dcPrice.AutoIncrement = false;
dcPrice.ReadOnly = true;
DataSet1.Tables["OrderItem"].Columns.Add(dcPrice);

[Listing 3]
stDecimal = System.Type.GetType("System.Decimal");
DataColumn dcTotal = new DataColumn("Total", stDecimal, "Sum(Child(drOrderHeader_OrderItem).Price)", MappingType.Attribute);
dcTotal.AutoIncrement = false;
dcTotal.ReadOnly = true;
DataSet1.Tables["OrderHeader"].Columns.Add(dcTotal);