Recipe 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"]);
    da.FillSchema(parentTable, SchemaType.Source);
    ds.Tables.Add(parentTable);

    // Get the schema for the Order Details table.
    DataTable childTable = new DataTable("Order Details");
    da = new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    da.FillSchema(childTable, SchemaType.Source);
    ds.Tables.Add(childTable);

    // Add the relation between the tables.
    DataRelation dr = new DataRelation("Order_OrderDetails_Relation",
        parentTable.Columns["OrderID"], childTable.Columns["OrderID"]);
    ds.Relations.Add(dr);

    // Bind the default view of the Orders table with the grid.
    dataGrid.DataSource = parentTable.DefaultView;        
}

private void loadDataSetButton_Click(object sender, System.EventArgs e)
{
    // Remove all data from the DataSet and refresh the grid.
    ds.Clear( );
    dataGrid.Refresh( );

    // Create parent and child data adapters.
    SqlDataAdapter daParent = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    SqlDataAdapter daChild = new SqlDataAdapter(
        "SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);

    // Enforce constraints as specified by user.
    ds.EnforceConstraints = (enforceConstraintsCheckBox.Checked);

    try
    {
        if (loadParentFirstRadioButton.Checked)
        {
            // Load parent data first.
            daParent.Fill(ds, "Orders");
            daChild.Fill(ds, "Order Details");
        }
        else
        {
            // Load child data first.
            daChild.Fill(ds, "Order Details");
            daParent.Fill(ds, "Orders");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
        return;
    }

    ds.EnforceConstraints = true;
}

Discussion

By default, a DataRelation is created with constraints as in the example; however, an overloaded constructor can override this behavior if necessary. If constraints are created, it is important that each record in the child table refers to a valid parent record, otherwise a ConstraintException is raised. Two techniques can be used to load parent and related child data without error into a DataSet with a schema that includes data relations defined:

• Load data from the parent tables before loading data from the child table. This ensures that each record in the child table refers to a valid parent record.

• The EnforceConstraints property of the DataSet indicates whether constraint rules are followed when data is added to or modified in the DataSet. Turn constraints off by setting the EnforceConstraints property to false prior to loading the data and back to true once the data is loaded. With this approach the order in which the data is loaded is not important. If one or more constraints cannot be enforced when EnforceConstraints is set back to true, a ConstraintException will be raised and EnforceConstraints stay set to false.