Using ADO.Net DataRelations

ADO.Net DataRelations define a relationship between two DataTables in a DataSet. DataRelations are most often used to maintain the integrity of the database and to allow you to use access data from different tables as if they were all in one table.

Database Relationship Fundamentals

To explain ADO.Net DataRelations better, we need to take a small detour to discuss the fundamentals of relationships from the underlying database's point of view.

There are 3 basic types of database relationships

One-to-One (where for every row in the parent table there is exactly one row in the child table)
One-to-Many (where every row in the parent table could have many matching rows in the child table)
Many-to-Many (many rows in the parent could match many rows in the child). This type is not usually used and is not supported by major database vendors.

The reason for using a set of related tables instead of one giant table is for speed and storage efficiency. Consider this example: You have a table with 10,000 rows of Product information. One of the columns in your table has a varchar(30) string value for the dozen or so Categories of products. That means that one of those 12 string values appears in each of the 10,000 rows.

A more efficient database design would be to have a separate table (perhaps called Categories) that associated the string values to a number. Now you change the category column in the parent table to use a number instead of a string value, and create a relationship between the two tables. The size of your database will instantly be reduced (by a lot!).

ADO Relations

In addition, you can use a Foreign Key Constraint to prevent users from misspelling or accidentally using values for the Categories table that do not exist.

More information is available at DataBase Design Principles

ADO.Net DataRelations

The ADO.Net DataSet object maintains a collection of Relations used by its DataTables. Likewise, the ADO.net DataTable object maintains a collection of Constraints.

Use can create a DataRelation with the Visual Studio XSD designer or via code. Either way, you specify the column in the parent table that matches the column in the child table. The data types of both columns should be the same or easily promoted to the other type.

In the case above, the VB.Net code to create the data relations would be:

dr = New DataRelation("CategoryProducts", ds.Tables("Categories").Columns("CategoryID"), _
 ds.Tables("Products").Columns("CategoryID"), False)
ds.Relations.Add(dr)

By default, the DataRelations also creates a foreign key constraint on the DataTable of the parent table. This might not what we want since it's not certain if the constraint would be successful, therefore we set the last argument to the DataRelation constructor to False.

Using Relationships

The relationship can be used in two different "directions"... the Parent relationship (looking "up" at the higher level row) and the Child relationship (looking "down" at the lower level rows). In a "one to many" database relationship, the "one" is the Parent and the "many" is the Child. So, it should be obvious, that a related row has only one parent row, but might have many child rows.

You decide the initial "direction" of the relationship by the order in which the arguments appear in the DataRelation constructor. Using the example above, the Customer table is the Parent, and the Region table is the Child.

OK, now that you've created a relationship, it's time to use it. You get to the data in the related rows by using the GetParentRow, GetParentRows, and GetChildRows methods. Using the example above the following demonstrates the concepts:

Dim dr, dr_related As DataRow
Dim sb As New System.Text.StringBuilder

' create a relationship between two of the tables
' arguments are Name, ParentCol, ChildCol, create_foreign_key
ds.Relations.Add(New DataRelation("CategoryProducts", _
 ds.Tables("Categories").Columns("CategoryID"), _
 ds.Tables("Products").Columns("CategoryID"), False))

' list rows from the Child table
For Each dr In ds.Tables("Products").Rows
    sb.Append("ProductName='" & dr("ProductName") & "'")
    ' now get the CategoryName column from the related row
    ' GetParentRow will only return one table (or nothing if there is no match)
    dr_related = dr.GetParentRow("CategoryProducts")
    sb.Append(", Category='" & dr_related("CategoryName") & "'" & vbCrLf)
Next

' display our data in a textbox
TextBox1.Text = sb.ToString
 ' list rows from the Parent table
For Each dr In ds.Tables("Categories").Rows
    sb.Append("CategoryName='" & dr("CategoryName") & "'" & vbCrLf)
    ' now get the CategoryName column from the related row
    ' GetChildRows will return many rows (or nothing if there is no match)
    For Each dr_related In dr.GetChildRows("CategoryProducts")
        sb.Append(vbTab & "ProductName='" & dr_related("ProductName") & "'" & vbCrLf)
    Next
Next

' display our data in a textbox
TextBox2.Text = sb.ToString

Documentation Links

The Example Code

This examples uses a different technique... it uses DataColumn Expressions to add a column onto the "left" DataTable that contains the values from a related column from the "right" DataTable. This is useful for getting data from related rows prior to binding to a DataGrid

' create a relationship between two of the tables
' arguments are Name, ParentCol, ChildCol, create_foreign_key
ds.Relations.Add(New DataRelation("CategoryProducts", _
 ds.Tables("Categories").Columns("CategoryID"), _
 ds.Tables("Products").Columns("CategoryID"), False))

' add a column from a related table. 
dc = New DataColumn("CategoryName")
dc.DataType = GetType(String)
dc.Expression = "Parent(CategoryProducts).CategoryName"
ds.Tables("Products").Columns.Add(dc)

' Since a Parent relationship can only return one row, this is a safe expression. However,
' if you had a child relationship, you'd have to include an aggregate in order to guarantee
' that the function would return just one row "Max(Child(CategoryProducts).UnitPrice)"
DataGrid1.DataSource = ds.Tables("Products")

Downloads/Links

Read a related article on Using ADO.Net Expressions
Read a related article on Joining ADO.Net DataTables
Download the VB.Net Source code example used in this article: Relations.zip