The DataRelation class represents a parent/child relationship between two tables. The DataRelation's overloaded constructors allow you to create a DataRelation object from a parent and a child column or a collection of columns. The first argument of the constructors is the name of the relation. The second and third arguments are the parent and child column(s), respectively. The last optional argument is a Boolean type, which indicates whether you want to create a constraint.
Before you create a DataRelation, let's fill a DataSet from two tables. Listing 8-7 fills a DataSet from the Customers and Orders tables.
Listing 8-7: Filling a DataSet from Two Tables
' Create a Connection Object Dim conn As SqlConnection = New SqlConnection(ConnectionString) SQL = "SELECT * FROM Customers" conn.Open() Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn) Dim ds1 As DataSet = New DataSet("Customers") adapter.Fill(ds1, "Customers") SQL = "SELECT * FROM Orders" adapter = New SqlDataAdapter(SQL, conn) Dim ds2 As DataSet = New DataSet("Orders") adapter.Fill(ds2, "Orders") ds1.Merge(ds2)
Now let's create a DataRelation between the CustomerId column of the Customers table and the CustomerID column of Orders table (see Listing 8-8).
Listing 8-8: Creating a DataRelation
' Get the DataColumn objects from Customers ' and Orders tables of a DataSet Dim parentCol As DataColumn = New DataColumn() Dim childCol As DataColumn = New DataColumn() 'Retrieve columns from a DataSet parentCol = ds1.Tables("Customers").Columns("CustomerID") childCol = ds1.Tables("Orders").Columns("CustomerID") Dim bConstraints As Boolean bConstraints = True ' Create a DataRelation. Dim CustOrderRelation As DataRelation = _ New DataRelation("CustOrdersRelation", parentCol, childCol, bConstraints)
Now you can add a DataRelation to a DataSet using the Add method of DataRelationCollection, which you access through the DataSet.Relations property. (We dicuss DataRelationCollection in a moment.) Use this code to add the DataRelation:
' Add the relation to the DataSet. ds1.Relations.Add(CustOrderRelation)
Even multiple sets of columns can participate in a relationship. The following code uses two columns to participate in a data relation:
Dim parentCols() As DataColumn Dim childCols() As DataColumn parentCols(0) = DataSet1.Tables("Customers").Columns("CustID") parentCols(1) = DataSet1.Tables("Customers").Columns("OrdID") childCols(0) = DataSet1.Tables("Orders").Columns("CustID") childCols(1) = DataSet1.Tables("Orders").Columns("OrdID") Dim CustOrderRel As DataRelation CustOrderRel = New DataRelation("CustomersOrders", parentCols, childCols)
After creating a DataRelation object, the next step is to see what properties are available in the DataRelation class. Table 8-4 describes the DataRelation class properties.
PROPERTY | DESCRIPTION |
---|---|
ChildColumns | Returns all child columns of a relation |
ChildKeyConstraint | Returns the FOREIGN KEY constraint of a relation if any available |
ChildTable | Returns the child table (DataTable) |
DataSet | Returns the DataSet to which a relation belongs |
ExtendedProperties | Returns the collection of custom properties |
Nested | Represents whether a DataRelation is nested (both get and set) |
ParentColumns | Returns all parent columns of a relation |
ParentKeyConstraint | Returns the UNIQUE constraint of the parent column |
ParentTable | Returns the parent table (DataTable) of a relation |
RelationName | Represents the name of the relation (both get and set) |
You can also create nested data relations. For example, if in addition to the Customers and Orders tables you have an Items table that stores data related to items such as Item ID, location, and so on, then you can create one more relation between the Orders and Items tables using the same steps you took for the Customers and Orders tables.
Now you'll learn how to access a DataRelation's properties. Listing 8-9 reads the DataSet, ParentTable, ChildTable, ParentColumns, ChildColumns, and RelationName properties of DataRelation.
Listing 8-9: Getting DataRelation Properties
' Getting a DataRelation properties Dim dtSet As DataSet = dtRelation.DataSet ' Getting Tables Dim pTable As DataTable = dtRelation.ParentTable Dim chTable As DataTable = dtRelation.ChildTable ListBox1.Items.Add("Parent Table: " + pTable.TableName) ListBox1.Items.Add("Child Table: " + chTable.TableName) 'Getting columns Dim parentCols() As DataColumn Dim childCols() As DataColumn parentCols = dtRelation.ParentColumns childCols = dtRelation.ChildColumns ' Print the ColumnName of each column. Dim i As Integer ListBox1.Items.Add("Parent Columns:") For i = 0 To parentCols.GetUpperBound(0) ListBox1.Items.Add(parentCols(i).ColumnName) Next i ListBox1.Items.Add("Child Columns:") For i = 0 To childCols.GetUpperBound(0) ListBox1.Items.Add(childCols(i).ColumnName) Next i ' Data relation name ListBox1.Items.Add("DataRelation Name:" + dtRelation.RelationName)
You just saw how to create a DataRelation object, add it to a DataSet, and access its properties. Now let's look at the DataRelationCollection class, which is a gateway to access data relations.
The DataRelationCollection class represents a collection of DataRelation objects of a DataSet, which is accessed through the Relations property of the DataSet. The Count property of DataRelationCollection returns the number of data relations in a collection, and the Items property allows you to access a DataRelation based on the index in the collection. Like the other collection classes, this class also provides Add, Remove, and other methods. Table 8-5 describes these methods.
METHOD | DESCRIPTION |
---|---|
Add | Adds a DataRelation to the collection. |
AddRange | Copies an array of DataRelation objects to the end of this collection. |
CanRemove | Returns True if the specified DataRelation can be removed from the collection; otherwise False. |
Clear | Removes all DataRelation objects from the collection. |
Contains | Returns True if a collection contains the specified DataRelation. You should make sure that a DataRelation exists in the collection before calling the Remove method to avoid exceptions. |
IndexOf | Returns the index of the specified data relation. |
Remove | Deletes the specified data relation from the collection. |
RemoveAt | Removes the data relation at the specified index. |
Listing 8-10 calls the Contains, CanRemove, and Remove methods to delete a data relation from the collection.
Listing 8-10: Deleting a Data Relation from the Collection
Dim dtRelCollection As DataRelationCollection = dtSet.Relations If (dtRelCollection.Contains("CustOrdersRelation")) Then If (dtRelCollection.CanRemove(dtRelation)) Then dtRelCollection.Remove(dtRelation) End If End If