[ Team LiB ]Recipe 3.4 Navigating Between Parent and Child Records Using a DataRelation
Problem
You want to navigate between the parent and child records in a hierarchical DataSet.
Solution
Use a DataRelation to find the child rows for a parent row in a related table, or to find the
parent row for a child row in a related table.
The sample code starts by creating a DataSet containing the Orders and Order Details
tables from the Northwind sample database and a relation between them. The code then
iterates over the Orders and uses the relation to return all Order Detail rows for each
order. Finally, the sample code retrieves the CustomerID field from the parent row and
displays it for each child.
The C# code is shown in Example 3-4
.
Example 3-4. File: NavigateDataRelationForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Text;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String ORDERS_TABLE = "Orders";
private const String ORDERDETAILS_TABLE = "OrderDetails";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
"Orders_OrderDetails_Relation";

ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
true);

StringBuilder result = new StringBuilder( );

// Iterate over the first 10 order records.
for (int i = 0; i < 10; i++)
{
// Display data from the Order record.
result.Append("Order ID:" + orderTable.Rows[i][ORDERID_FIELD] +
Environment.NewLine);

// Iterate over the OrderDetails records for the Order.
foreach(DataRow row in
orderTable.Rows[i].GetChildRows(ORDERS_ORDERDETAILS_RELATION))
{
// Display data for the OrderDetails record.
result.Append("\tProduct: " + row[PRODUCTID_FIELD] +
"; Quantity: " + row[QUANTITY_FIELD] +
// Retrieve CustomerID from the parent Orders record.
"; Customer ID: " + row.GetParentRow
(ORDERS_ORDERDETAILS_RELATION)[CUSTOMERID_FIELD] +
Environment.NewLine);
}

result.Append(Environment.NewLine);
}

resultTextBox.Text = result.ToString( );
Discussion