ADO.NET 3.5 Cookbook, 2nd Edition

3.16. Navigating Between Parent and Child Tables in an Untyped DataSet

Problem

You want to navigate between the parent and child records in a hierarchical DataSet.

Solution

Use a DataRelation to find the child rows for a parent row in a related table, or to find the parent row for a child row in a related table.

The solution begins by creating a DataSet containing the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables from AdventureWorks and creating a relation between them. The code then iterates over the SalesOrderHeader DataTable and uses the relation to output all child rows in the SalesOrderDetail DataTable to the console. The solution retrieves the CustomerID field for each child row from the parent row, and outputs it to the console.

The C# code in Program.cs in the project NavigatingParentChildTables is shown in Example 3-21.

Example 3-21. File: Program.cs for NavigatingParentChildTables solution

using System; using System.Data; using System.Data.SqlClient; namespace NavigatingParentChildTables { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = @"SELECT * FROM Sales.SalesOrderHeader; SELECT * FROM Sales.SalesOrderDetail;"; DataSet ds = new DataSet( ); SqlDataAdapter da; // Fill the Header and Detail table in the DataSet da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.MissingSchemaAction = MissingSchemaAction.AddWithKey; da.TableMappings.Add("Table", ...