DataRelations

A DataRelation object represents a relationship between two DataTable objects within a DataSet. These relationships are fundamental to modeling hierarchical data and enabling navigation between related tables. They are analogous to foreign key constraints in relational databases.

DataRelation objects allow you to:

• Navigate from a row in a parent table to its child rows.
• Navigate from a row in a child table to its corresponding parent row.
• Enforce referential integrity between related tables.
• Filter child rows based on the selected parent row.

Creating a DataRelation

You can create a DataRelation by using the DataRelation constructor or by adding a relation to a DataSet's Relations collection. The most common approach is to add it to the DataSet.

Using the DataSet.Relations.Add Method

The DataSet.Relations.Add method offers several overloads. A common overload requires the name of the relation, an array of parent key columns, and an array of child key columns.

// Assume ds is a DataSet containing two DataTables: 'Customers' and 'Orders'
// Each table has a primary key column named 'CustomerID'.

// Define the columns that link the tables
DataColumn[] parentColumns = new DataColumn[] { ds.Tables["Customers"].Columns["CustomerID"] };
DataColumn[] childColumns = new DataColumn[] { ds.Tables["Orders"].Columns["CustomerID"] };

// Create the DataRelation
DataRelation relation = new DataRelation("CustomerOrders", parentColumns, childColumns);

// Add the relation to the DataSet
ds.Relations.Add(relation);
            

You can also specify a name for the relation, a parent DataColumn, and a child DataColumn if the keys are single columns:

// Assume ds is a DataSet containing 'Customers' and 'Orders' DataTables
// with a single 'CustomerID' column in each.

ds.Relations.Add("CustomerOrders", ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]);
            

Navigating DataRelations

Once a DataRelation is established, you can traverse the relationships between rows.

Navigating from Parent to Child Rows

Use the GetChildRows method of a DataRow object to retrieve the related child rows.

// Assume 'customerRow' is a DataRow from the 'Customers' table
// and the 'CustomerOrders' relation has been added.

DataRow[] childOrders = customerRow.GetChildRows("CustomerOrders");

foreach (DataRow orderRow in childOrders)
{
    Console.WriteLine($"Order ID: {orderRow["OrderID"]}, Date: {orderRow["OrderDate"]}");
}
            

Navigating from Child to Parent Row

Use the GetParentRow method of a DataRow object to retrieve the related parent row.

// Assume 'orderRow' is a DataRow from the 'Orders' table
// and the 'CustomerOrders' relation has been added.

DataRow parentCustomer = orderRow.GetParentRow("CustomerOrders");

if (parentCustomer != null)
{
    Console.WriteLine($"Customer Name: {parentCustomer["CompanyName"]}");
}
            

Enforcing Referential Integrity

DataRelation objects can optionally enforce referential integrity, similar to foreign key constraints in databases. This prevents the creation of "orphan" records (child rows without a corresponding parent row).

Creating Relations with Constraints

When creating a DataRelation, you can specify a ForeignKeyConstraint to enforce referential integrity. This is often done implicitly when adding a relation with keys to a DataSet, as the DataSet automatically creates constraints.

You can set properties on the ForeignKeyConstraint (which is associated with the DataRelation) to control the behavior on update or delete operations:

• AcceptRejectRule: Defines how changes are accepted or rejected when AcceptChanges is called.
• DeleteRule: Specifies what happens to child rows when a parent row is deleted. Options include Cascade (delete children), SetNull (set child key columns to null), and None (allow deletion, potentially creating orphans).
• UpdateRule: Specifies what happens to child rows when a parent row's key is updated. Similar options to DeleteRule.

Filtering Child Rows with DataView

DataRelation objects are often used in conjunction with DataView objects to filter child rows based on the current row in the parent table.

// Assume a GridView displays customers. When a customer is selected:

// Get the selected customer row
DataRowView selectedCustomerView = (DataRowView)customerGridView.SelectedRow.DataItem;
DataRow selectedCustomerRow = selectedCustomerView.Row;

// Get the relation name
string relationName = "CustomerOrders";

// Filter the DataView for orders related to the selected customer
DataView ordersView = new DataView(ds.Tables["Orders"]);
ordersView.RowFilter = $"ParentRelations.{relationName} = '{selectedCustomerRow["CustomerID"]}'";

// Bind the filtered orders to another control (e.g., a second GridView)
ordersGridView.DataSource = ordersView;
ordersGridView.DataBind();
            

The ParentRelations. syntax within RowFilter is a powerful feature for dynamic filtering based on established relations.