ADO.NET Cookbook

2.1. Retrieving Hierarchical Data into a DataSet

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

There are several techniques you can use to load parent and child data into a DataSet.

The sample code contains three event handlers:

Form.Load

Sets up the sample by creating a DataSet with table schemas for both the Orders table and the Order Details table from Northwind and a DataRelation object relating these two tables. The default view of the parent table, Orders, is bound to a data grid on the form.

Load DataSet Button.Click

Starts by clearing the data from the DataSet and refreshing the data grid. DataAdapter objects are created for both the parent and the child table. The Orders and Order Details are then filled using data adapters in the sequence specified and enforcing constraints during the load as specified by the user.

The C# code is shown in Example 2-1.

Example 2-1. File: HierarchicalDataSetForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; private DataSet ds; // . . . private void HierarchicalDataSetForm_Load(object sender, System.EventArgs e) { ds = new DataSet( ); // Get the schema for the Orders table. DataTable parentTable = new DataTable("Orders"); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders", ConfigurationSettings.AppSettings["Sql_ConnectString"]); ...