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 columnds.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:

' add a new calculated column to the Categories table, with the number of child ' productsds.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:

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