Binaryworld - Calculated columns that refer to relationships ... [ VB.net -> ADO.net ]

The DataSet is a container of multiple DataTables, and it allows to create parent-child relationships between two tables, as shown below:

Click here to copy the following block

' create a relationship between the Categories and the Products tables,
' against the CatID column
ds.Relations.Add(New DataRelation("CatProducts", _
ds.Tables("Categories").Columns("CatID"), ds.Tables("Products").Columns _
("CatID")))

This is nice, but even nicer is the possibility to create calculated columns in a table, that refer to the parent-child relationship. For example, here's how you can create a new column ProductsCount in the DataSet's Categories table, which will return the number of child products:

Click here to copy the following block

' add a new calculated column to the Categories table, with the number of child
' products
ds.Tables("Categories").Columns.Add("ProductsCount", GetType(Integer), _
"Count(Child(CatProducts).ProductID)")

The expression refers to the CatProducts relationship created before, and uses the Count function to count the number of child records returned by the relationship for each single category. Many aggregate functions are supported, such as Min, Max, Sum, Avg.
From a child record, you can also refer to the parent item of course. The following code shows how to add a calculated column to the Products table, that will contain the name of the parent category:

Click here to copy the following block

' add a new calculated column to the Products table,
' with the name of the parent category
ds.Tables("Products").Columns.Add("CategoryName", GetType(String), _
"Parent(CatProducts).CategoryName")