ADO.NET 3.5 Cookbook, 2nd Edition

3.15. Retrieving Hierarchical Data

Problem

You want to fill a DataSet with parent and related child data, even if the DataSet already has a schema that includes the relationship.

Solution

The solution shows how to load parent and child data into a DataSet using both multiple SQL SELECT queries and a batch SQL query.

The solution creates a DataSet and loads it with both schema and data from the Sales.SalesOrderHeader and Sales.SalesOrderDetail tables in AdventureWorks. A DataRelation object is created relating the two tables. The first three rows from the SalesOrderHeader table is output together with the related SalesOrderDetail rows for each.

The C# code in Program.cs in the project RetrieveHierarchicalDataSet is shown in Example 3-19.

Example 3-19. File: Program.cs for RetrieveHierarchicalDataSet solution

using System; using System.Data; using System.Data.SqlClient; namespace RetrieveHierarchicalDataSet { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelectHeader = "SELECT * FROM Sales.SalesOrderHeader"; string sqlSelectDetail = "SELECT * FROM Sales.SalesOrderDetail"; DataSet ds = new DataSet( ); SqlDataAdapter da; // Fill the Header table in the DataSet da = new SqlDataAdapter(sqlSelectHeader, sqlConnectString); da.FillSchema(ds, SchemaType.Source, "SalesOrderHeader"); da.Fill(ds, "SalesOrderHeader"); // Fill the Detail table in the DataSet da = new SqlDataAdapter(sqlSelectDetail, ...