DataRelation in ADO.NET

To provide data integrity and consistency, you should use relationships between two tables. You achieve this relationship by defining a primary key in one table and using a foreign key in the other table. Say a customer has multiple orders; the Customers table stores the customer details, and the Orders table stores all the order details. To avoid the redundancy of data, you define the primary key of the Customers table as a foreign key in the Orders table.

 

Note: In general this relationship is called the customer/order relationship parent/child, or sometimes master/ details.

 

In this example, the Customers table is also the parent table, and the Orders table is also the child table. The ParentRelations property of DataTable represents the parent relationship, and ChildRelations represents the child relationship.

 

CAUTION: The data type of both columns, which you're linking through a relationship between the customers and the Order Table, must be identical.

 

You can also access this relationship through a DataSet using its Relations property. To create a relationship between two columns, you create two DataColumn objects and pass them as DataRelation arguments.

 

The listing below shows you how to create a customer/order relationship between the Customers and Orders table through the Customers table's id column, referenced as CustId in the orders table. The DataRelation constructor takes three arguments: the name of the relationship, the first DataColumn, and the second DataColumn. After that, you call DataTable's ParentRelation.Add method with DataRelation as an argument.

 

Listing: Creating a customer/order relationship using DataRelation

private void BindData() {
    DataRelation dtRelation;
    DataColumn CustCol = dtSet.Tables["Customers"].Columns["id"];
    DataColumn orderCol = dtSet.Tables["Orders"].Columns["CustId"];

    dtRelation = new DataRelation("CustOrderRelation", CustCol, orderCol);
    dtSet.Tables["Orders"].ParentRelations.Add(dtRelation);

    dataGrid1.SetDataBinding(dtSet, "Customers");
}

Conclusion

 

Hope this article would have helped you in understanding DataRelation in ADO.Net. See my other articles on the website on ADO.NET.