Data Relations in ADO.NET

Data relations are a fundamental concept in ADO.NET that allow you to establish and manage relationships between DataTable objects within a DataSet. These relations mimic the relationships found in relational databases, enabling you to navigate from parent rows to child rows and vice-versa.

Creating Data Relations

You can create a data relation using the DataRelation class. This class requires the name of the relation, an array of parent key columns, and an array of corresponding child key columns.

The primary key constraint on the parent table is typically used to enforce referential integrity. The foreign key constraint on the child table references the primary key of the parent table.

Example: Creating a Parent-Child Relation

Consider two DataTable objects: Customers and Orders. We want to establish a relation where each customer can have multiple orders.

// Assuming 'ds' is a DataSet object
                DataTable customersTable = ds.Tables["Customers"];
                DataTable ordersTable = ds.Tables["Orders"];

                // Define the columns involved in the relation
                DataColumn parentColumn = customersTable.Columns["CustomerID"];
                DataColumn childColumn = ordersTable.Columns["CustomerID"];

                // Create the DataRelation
                DataRelation relation = new DataRelation("CustomerOrders", parentColumn, childColumn);

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

Navigating Relations

Once a relation is established, you can easily navigate between related rows.

Getting Child Rows from a Parent Row

Use the GetChildRows method on a DataRow object of the parent table.

// Assuming 'parentRow' is a DataRow from the Customers table
                DataRow[] childRows = parentRow.GetChildRows("CustomerOrders");

                foreach (DataRow orderRow in childRows)
                {
                    // Access order details from orderRow
                    Console.WriteLine($"Order ID: {orderRow["OrderID"]}, Date: {orderRow["OrderDate"]}");
                }

Getting Parent Row from a Child Row

Use the GetParentRow method on a DataRow object of the child table.

// Assuming 'childRow' is a DataRow from the Orders table
                DataRow parentRow = childRow.GetParentRow("CustomerOrders");

                // Access customer details from parentRow
                Console.WriteLine($"Customer Name: {parentRow["CustomerName"]}");

Key Concepts

  • DataSet: A collection of DataTable objects, DataRelation objects, and constraints.
  • DataTable: Represents a table of data in memory.
  • DataRelation: Defines a relationship between two DataTable objects.
  • Referential Integrity: Ensures that relationships between tables are valid, preventing orphaned records.

Use Cases

  • Hierarchical data display (e.g., Master-Detail views).
  • Filtering data based on related tables.
  • Enforcing data consistency within a DataSet.

Related Topics