ADO.NET: Work with Relational Data
A DataSet can contain either unrelated tables or related tables. You can think of a DataSet 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 key/foreign key relationships. Relationships can also have different types of enforcement. By default, deletions and updates are cascaded: if you delete a Customer row, the related Orders rows are also deleted; if you Update the key of a Customer row, the associated foreign key values in the Orders table is also updated.A DataSet contains a Relations collection. You can add a Relation to this collection using the column or columns (in a multicolumn key) of the related tables. The following code example creates a relation between Customers and Orders, and names the relation CustOrders.
myDataSet.Relations.Add("CustOrders",myDataSet.Tables("Customers").Columns("CustomerID"), _
myDataSet.Tables("Orders").Columns("CustomerID"))
VB
After adding a relation between the CustomerID Key on the Customers table and the CustomerID foreign key on the Orders table in the DataSet, you can iterate through the data.
Dim myDataRow1 as DataRow
for each myDataRow1 in myDataSet.Tables("Customers").Rows
Console.WriteLine("Customer: " + myDataRow1("ContactName").ToString())
' Iterate over orders data
Dim myDataRow2 as DataRow
for each myDataRow2 in myDataRow1.GetChildRows(myDataSet.Relations("CustOrders"))
Console.WriteLine("Order #" + myDataRow2("OrderID").ToString())
next
Console.WriteLine()
next
VB
The following sample iterates over the data and outputs it to a Web page hierarchically.
VB RelationalData.aspx
[Run Sample] |
[View Source]