Ado
Working with relational Data in Ado.net
submitted by
User Level
Date of submissionC.vinodh kumarintermediate04/03/2001
Working with hierarchal data is not so easy with the previous version of ADO. With the advent of ado.net things are made is much more easier for the programmers. They need not write complex data shape query to get the hierarchal output (hierarchal recordset World抯 in ADO).
ado.net DataSet
The DataSet object is a to ado.net. The DataSet is a simple memory-resident database which provides a consistent programming model regardless of the data sour Ce. The DataSet represents a complete set of data including related tables, constraints, and relationships among the tables. In Ado.net, the DataSet is a collection the one or more tables represented by the DataTable objects. The Table Collection object contains all of 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 was like this, except it's based on a hierarchical paradigm. Because data is often stored in relational databases, the DataSet can handle both hierarchical relationships and foreign k EY 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 tab Les.
The example below presumes two DataTable objects 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 "Argument" ("CustOrders") specifies the name of the relationship. The second and third arguments are the DataColumn 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"]);
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 is code adds a relation between the CustomerId key on the Customers table and the CustomerId key on the Orders T Able in the DataSet. Now this 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 related DataRow objects in one table when given a single DataRow from a R Elated table. The above example gives a DataRow from the Customers table; We can retrieve all of 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 would 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 a introduction to the types of relationships and have of NAL data using ado+ DataSet and datarelation.in particular Ado.net offers several advantages over previous of ADO And over the other data access components. These benefits fall into the following categories:interoperability, maintainability, programmability, and performance.
Source Code:
Datarelation.zip
