Working with Relational Data in ADO.NET

Working with hierarchal data was not that easy with the previous version of ADO. With the advent of ADO.Net things are made much easier for the programmers. They need not write complex data shape query to get the hierarchal output (Hierarchal Recordsets in ADO).

ADO.Net DataSet

The DataSet object is central to ADO.NET. The DataSet is a simple memory-resident database which provides a consistent programming model regardless of the data source. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables. In ADO.NET, DataSet is a collection of one or more tables represented by DataTable objects. The Table Collection object contains all the DataTable objects in a DataSet.

A typical dataset contains relationships contained by the RelationsCollection object. A relationship, represented by the DataRelation object, associates rows in one table to the rows in another table. It is similar to the foreign-key relationship in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.

A DataSet can contain either related or unrelated data. A DataSet can either have two unrelated or related tables. A DataSet can be thought of as a document of Data. In fact, an XML data document is like this, except it is based on a hierarchical paradigm. Because data is often stored in relational databases, the DataSet can handle both hierarchical relationships and foreign key relationships. Relationships can also have different types of enforcement. By default, deletions and updates are cascaded. A DataSet contains a Relations collection. It is easy to add a relationship to this collection using the column or columns (in a multi-column key) of the related tables.

The example below presumes two DataTable objects exist in the DataSet. Both tables have a column named "CustID" which serves as the link between the two tables. The example adds a single DataRelation to the Relations collection of the DataSet object. The first argument ("CustOrders") specifies the name of the relationship. The second and third arguments are the DataColumn objects that link the two tables.

//constructs an instance of a DataSet<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" />
DataSet ds = new DataSet("CustomerOrders");
// Add a DataRelation to the Relations collection specifying its name,
// and the appropriate DataColumn objects as arguments.
ds.Relations.Add("CustOrders",ds.Tables["Customers"].Columns["CustID"],
ds.Tables["Orders"].Columns["CustID"]);
// you can also set the relation like the one given below
DataRelation dr;
dr = new DataRelation("CustOrders",ds.Tables["Customers"].Columns["CustID"],
ds.Tables["Orders"].Columns["CustID"]);
ds.Relations.Add(dr)

This code adds a relation between the CustomerId Key on the Customers table and the CustomerId foreign key on the Orders table in the DataSet. Now that a relationship exists, you can iterate through the data, using the relationship, in a meaningful way.

Retrieving Data from the Relationship

foreach (DataRow Customer in dsCustomers.Tables["Customers"].Rows) {
Response.Write("Customer: " + Customer["ContactName"].ToString());
foreach (DataRow Order in Customer.GetChildRows(dsCustomers.Relations["CustOrders"])) {
Response.Write("Order #" + Order["OrderId"].ToString());
}
}

A primary function of the DataRelation is to allow navigation from one table to another within the DataSet. In practice, this allows us to retrieve all the related DataRow objects in one table when given a single DataRow from a related table. The above example gives a DataRow from the Customers table; we can retrieve all the orders for a particular customer from the Orders table.

GetChildRow method gets the specified child rows of this DataRow using the named DataRelation.

The relational output from the customer and orders table will be the same as below (One-to-Many Relation).

Customer: Maria Anders
Order #10643
Order #10692
Order #10702
Order #10835
Order #10952
Order #11011

Customer: Ana Trujillo
Order #10308
Order #10625
Order #10759
Order #10926
.
.
.

Conclusion

In this article, we have started with an introduction to the types of relationships and have learnt how to get the relational data using ADO+ DataSet and DataRelation.In particular ADO.NET offers several advantages over previous versions of ADO and over other data access components. These benefits fall into the following categories: Interoperability, Maintainability, Programmability, and Performance.