Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: VB7
Expertise: Intermediate
Mar 17, 2003



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Calculated columns that refer to relationships

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

' 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 _
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:

' add a new calculated column to the Categories table, with the number of child 
' products
ds.Tables("Categories").Columns.Add("ProductsCount", GetType(Integer), _
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:

' add a new calculated column to the Products table,
'  with the name of the parent category
ds.Tables("Products").Columns.Add("CategoryName", GetType(String), _
Marco Bellinaso
Thanks for your registration, follow us on our social networks to keep up-to-date