14.16 Navigating Tables Using DataRelations

14.16 Navigating Tables Using DataRelations

You want to access tables that are joined with a DataRelation .

Technique

To create a relationship between two tables, pass to the DataRelation constructor the two columns between which you want to create a relationship, as shown in the following code:

 DataColumn parentCustID; DataColumn childCustID; // get the DataColumn objects dcCustomerCustID = ds.Tables["Customers"].Columns["CustID"]; dcOrdersCustID = ds.Tables["Orders"].Columns["CustID"]; // Define the relationship DataRelation drCustID; drCustID = new DataRelation("CustomerOrders", parentCustID, childCustID); 

The example creates a relationship between the two columns but does not create a foreign-key constraint. Any time the column in the parent table changes, an exception will be thrown. Therefore, you want to use the technique discussed in Recipe 14.13, "Creating ForeignKey Constraints," to set the foreign-key constraint rule.

Now that a relationship exists between two different tables, you can navigate from one to the other using the various properties and methods defined in the DataTable and DataRow classes. To access all the child relationships that a table contains, enumerate the ChildRelations collections, which will return a DataRelation object for each defined relationship of that table. You can then use each of these DataRelation objects as a parameter to the GetChildRows method of the DataRow class. In other words, enumerate each DataRelation class; for each DataRelation encountered , enumerate each row within the DataTable and call the GetChildRows method, passing the DataRelation object, as shown in the following code:

 private static void PrintChildRowValues() {     DataTable parentTable = ds.Tables[ "Customers" ];     DataRow[] childRows;     foreach(DataRelation curRelation in parentTable.ChildRelations)     {         foreach(DataRow curRow in parentTable.Rows)         {             PrintRow (curRow, "Parent Row Values" );             childRows = curRow.GetChildRows(curRelation);             // Print values of rows.             PrintRow ( childRows, "Child Row Value" );         }     } } 

Comments

Recipe 14.13 demonstrated how to create foreign-key constraints, which you use for columns that are common between two different tables. Without a mechanism to maintain these relationships, you would have to write a lot of custom code to ensure tables are updated correctly. The DataRelation class allows you to define a relationship between two different tables. Creating a ForeignKeyConstraint between similar columns of two different tables creates a relationship but does not allow you to easily navigate between those relationships. Likewise, creating a DataRelation between two tables doesn't allow you to define a rule for the foreign-key constraint, which is why you want to use both techniques to ensure a robust solution.