adoWorking with Relational Data in ADO.NetSubmitted ByUser LevelDate of SubmissionC.Vinodh Kumarintermediate04/03/2001
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 Recordset抯 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)

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());

    }

}

厖厖?

厖厖?