Recipe 7.12 Binding a Windows DataGrid to Master-Detail Data

Problem

You need to bind both a parent table and child table within a DataSet to a DataGrid so that the child data is displayed when the parent is expanded, and update the database with edits made to data in both tables.

Solution

Use the approach demonstrated in the sample code.

The sample uses eight stored procedures, which are shown in Example 7-20 through Example 7-27:

GetOrders

Used to retrieve a single record from the Orders table if the optional @OrderId parameter is specified or all Orders records if it is not

DeleteOrders

Used to delete the record specified by the @OrderId parameter from the Orders table

InsertOrders

Used to insert a record into the Orders table and return the OrderID identity value for the new record

UpdateOrders

Used to update all field values for the record in the Orders table specified by the @OrderId input parameter

GetOrderDetails

Used to retrieve a single record from the Order Details table if the optional @OrderId and @ProductID parameters are specified, or all Order Details records if it is not

DeleteOrderDetails

Used to delete the record specified by the @OrderId and @ProductID parameters from the Order Details table

InsertOrderDetails

Used to insert a record into the Order Details table

UpdateOrderDetails

Used to update all field values for the record in the Order Details table specified by the @OrderId and @ProductID input parameters

Example 7-20. Stored procedure: GetOrders

CREATE PROCEDURE GetOrders
    @OrderID int=null
AS
    SET NOCOUNT ON
    
    if @OrderID is not null
    begin
        select
            OrderID,
            CustomerID,
            EmployeeID,
            OrderDate,
            RequiredDate,
            ShippedDate,
            ShipVia,
            Freight,
            ShipName,
            ShipAddress,
            ShipCity,
            ShipRegion,
            ShipPostalCode,
            ShipCountry
        from
            Orders
        where
            OrderID=@OrderID
            
        return 0
    end
    
    select
        OrderID,
        CustomerID,
        EmployeeID,
        OrderDate,
        RequiredDate,
        ShippedDate,
        ShipVia,
        Freight,
        ShipName,
        ShipAddress,
        ShipCity,
        ShipRegion,
        ShipPostalCode,
        ShipCountry
    from
        Orders

    return 0

Example 7-21. Stored procedure: DeleteOrders

CREATE PROCEDURE DeleteOrders
    @OrderID int
AS
    SET NOCOUNT ON
    
    delete
    from
        Orders
    where
        OrderID=@OrderID
        
    return 0

Example 7-22. Stored procedure: InsertOrders

CREATE PROCEDURE InsertOrders
    @OrderID int output,
    @CustomerID nchar(5),
    @EmployeeID int,
    @OrderDate datetime,
    @RequiredDate datetime,
    @ShippedDate datetime,
    @ShipVia int,
    @Freight money,
    @ShipName nvarchar(40),
    @ShipAddress nvarchar(60),
    @ShipCity nvarchar(15),
    @ShipRegion nvarchar(15),
    @ShipPostalCode nvarchar(10),
    @ShipCountry nvarchar(15)
AS
    SET NOCOUNT ON
        
    insert Orders(
        CustomerID,
        EmployeeID,
        OrderDate,
        RequiredDate,
        ShippedDate,
        ShipVia,
        Freight,
        ShipName,
        ShipAddress,
        ShipCity,
        ShipRegion,
        ShipPostalCode,
        ShipCountry)
    values (
        @CustomerID,
        @EmployeeID,
        @OrderDate,
        @RequiredDate,
        @ShippedDate,
        @ShipVia,
        @Freight,
        @ShipName,
        @ShipAddress,
        @ShipCity,
        @ShipRegion,
        @ShipPostalCode,
        @ShipCountry)
    
    if @@rowcount=0
        return 1
    
    set @OrderID=Scope_Identity( )
    
    select @OrderId OrderId

    return 0

Example 7-23. Stored procedure: UpdateOrders

CREATE PROCEDURE UpdateOrders
    @OrderID int,
    @CustomerID nchar(5),
    @EmployeeID int,
    @OrderDate datetime,
    @RequiredDate datetime,
    @ShippedDate datetime,
    @ShipVia int,
    @Freight money,
    @ShipName nvarchar(40),
    @ShipAddress nvarchar(60),
    @ShipCity nvarchar(15),
    @ShipRegion nvarchar(15),
    @ShipPostalCode nvarchar(10),
    @ShipCountry nvarchar(15)
AS
    SET NOCOUNT ON
        
    update
        Orders
    set
        CustomerID=@CustomerID,
        EmployeeID=@EmployeeID,
        OrderDate=@OrderDate,
        RequiredDate=@RequiredDate,
        ShippedDate=@ShippedDate,
        ShipVia=@ShipVia,
        Freight=@Freight,
        ShipName=@ShipName,
        ShipAddress=@ShipAddress,
        ShipCity=@ShipCity,
        ShipRegion=@ShipRegion,
        ShipPostalCode=@ShipPostalCode,
        ShipCountry=@ShipCountry
    where
        OrderID=@OrderID
    
    if @@rowcount=0
        return 1
    
    return 0

Example 7-24. Stored procedure: GetOrderDetails

CREATE PROCEDURE GetOrderDetails
    @OrderID int=null,
    @ProductID int=null
AS
    SET NOCOUNT ON
    
    if @OrderID is not null and @ProductID is not null
    begin
        select
            OrderID,
            ProductID,
            UnitPrice,
            Quantity,
            Discount
        from
            [Order Details]
        where
            OrderID=@OrderID and
            ProductID=@ProductID
            
        return 0
    end
    
    select
        OrderID,
        ProductID,
        UnitPrice,
        Quantity,
        Discount
    from
        [Order Details]

    return 0

Example 7-25. Stored procedure: DeleteOrderDetails

CREATE PROCEDURE DeleteOrderDetails
    @OrderID int,
    @ProductID int
AS
    SET NOCOUNT ON
    
    delete
    from
        [Order Details]
    where
        OrderID=@OrderID and
        ProductID=@ProductID
        
    return 0

Example 7-26. Stored procedure: InsertOrderDetails

CREATE PROCEDURE InsertOrderDetails
    @OrderID int,
    @ProductID int,
    @UnitPrice money,
    @Quantity smallint,
    @Discount real
AS
    SET NOCOUNT ON
    
    insert [Order Details](
        OrderID,
        ProductID,
        UnitPrice,
        Quantity,
        Discount)
    values (
        @OrderID,
        @ProductID,
        @UnitPrice,
        @Quantity,
        @Discount)

    if @@rowcount=0
        return 1
        
    return 0

Example 7-27. Stored procedure: UpdateOrderDetails

CREATE PROCEDURE UpdateOrderDetails
    @OrderID int,
    @ProductID int,
    @UnitPrice money,
    @Quantity smallint,
    @Discount real
AS
    SET NOCOUNT ON
    
    update
        [Order Details]
    set
        UnitPrice=@UnitPrice,
        Quantity=@Quantity,
        Discount=@Discount
    where
        OrderID=@OrderID and
        ProductID=@ProductID

    if @@rowcount=0
        return 1
        
    return 0

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataSet containing the Orders and Order Details DataTable objects. A DataRelation object is created relating the tables. DataAdapter objects are created for each DataTable; the select, delete, insert, and update Command objects are specified for each using the custom logic in the eight stored procedures used by this solution. The DataAdapter objects are used to fill both tables in the DataSet. Finally, the default view of the Orders table is bound to the data grid on the form.

Update Button.Click

Uses the DataAdapter for the Orders and Order Details DataTable objects to update offline changes back to the database.

The C# code is shown in Example 7-28.

Example 7-28. File: HierarchicalDataGridForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

private DataSet ds;
// Private SqlDataAdapter daParent, daChild
private SqlDataAdapter daOrder, daOrderDetail;

// Table name constants
private const String ORDERS_TABLE         = "Orders";
private const String ORDERDETAILS_TABLE   = "OrderDetails";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
    "Orders_OrderDetails_Relation";

// Field name constants for Orders table
public const String ORDERID_FIELD         = "OrderID";
public const String CUSTOMERID_FIELD      = "CustomerID";
public const String EMPLOYEEID_FIELD      = "EmployeeID";
public const String ORDERDATE_FIELD       = "OrderDate";
public const String REQUIREDDATE_FIELD    = "RequiredDate";
public const String SHIPPEDDDATE_FIELD    = "ShippedDate";
public const String SHIPVIA_FIELD         = "ShipVia";
public const String FREIGHT_FIELD         = "Freight";
public const String SHIPNAME_FIELD        = "ShipName";
public const String SHIPADDRESS_FIELD     = "ShipAddress";
public const String SHIPCITY_FIELD        = "ShipCity";
public const String SHIPREGION_FIELD      = "ShipRegion";
public const String SHIPPOSTALCODE_FIELD  = "ShipPostalCode";
public const String SHIPCOUNTRY_FIELD     = "ShipCountry";

// Stored procedure name constants
public const String DELETEORDERS_SP       = "DeleteOrders";
public const String GETORDERS_SP          = "GetOrders";
public const String INSERTORDERS_SP       = "InsertOrders";
public const String UPDATEORDERS_SP       = "UpdateOrders";

// Stored procedure parameter name constants for Orders table
public const String ORDERID_PARM          = "@OrderID";
public const String CUSTOMERID_PARM       = "@CustomerID";
public const String EMPLOYEEID_PARM       = "@EmployeeID";
public const String ORDERDATE_PARM        = "@OrderDate";
public const String REQUIREDDATE_PARM     = "@RequiredDate";
public const String SHIPPEDDDATE_PARM     = "@ShippedDate";
public const String SHIPVIA_PARM          = "@ShipVia";
public const String FREIGHT_PARM          = "@Freight";
public const String SHIPNAME_PARM         = "@ShipName";
public const String SHIPADDRESS_PARM      = "@ShipAddress";
public const String SHIPCITY_PARM         = "@ShipCity";
public const String SHIPREGION_PARM       = "@ShipRegion";
public const String SHIPPOSTALCODE_PARM   = "@ShipPostalCode";
public const String SHIPCOUNTRY_PARM      = "@ShipCountry";

// Field name constants for OrderDetails table
public const String ORDERID_FIELD         = "OrderID";
public const String PRODUCTID_FIELD       = "ProductID";
public const String UNITPRICE_FIELD       = "UnitPrice";
public const String QUANTITY_FIELD        = "Quantity";
public const String DISCOUNT_FIELD        = "Discount";

// Stored procedure name constants
public const String DELETEORDERDETAILS_SP = "DeleteOrderDetails";
public const String GETORDERDETAILS_SP    = "GetOrderDetails";
public const String INSERTORDERDETAILS_SP = "InsertOrderDetails";
public const String UPDATEORDERDETAILS_SP = "UpdateOrderDetails";

// Stored procedure parameter name constants for OrderDetails table
public const String ORDERID_PARM          = "@OrderID";
public const String PRODUCTID_PARM        = "@ProductID";
public const String UNITPRICE_PARM        = "@UnitPrice";
public const String QUANTITY_PARM         = "@Quantity";
public const String DISCOUNT_PARM         = "@Discount";

//  . . . 

private void HierarchicalDataGridForm_Load(object sender,
    System.EventArgs e)
{
    ds = new DataSet( );
    
    // Fill the Order table and add it to the DataSet.
    daOrder = new SqlDataAdapter(GETORDERS_SP,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    daOrder.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataTable dtOrder = new DataTable(ORDERS_TABLE);
    daOrder.FillSchema(dtOrder, SchemaType.Source);
    daOrder.Fill(dtOrder);
    ds.Tables.Add(dtOrder);

    // Fill the OrderDetails table with schema and add it to the DataSet.
    daOrderDetail = new SqlDataAdapter(GETORDERDETAILS_SP,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    daOrderDetail.SelectCommand.CommandType = CommandType.StoredProcedure;
    DataTable dtOrderDetail = new DataTable(ORDERDETAILS_TABLE);
    daOrderDetail.FillSchema(dtOrderDetail, SchemaType.Source); 
    daOrderDetail.Fill(dtOrderDetail);
    ds.Tables.Add(dtOrderDetail);

    // Create a relation between the tables.
    ds.Relations.Add(ORDERS_ORDERDETAILS_RELATION,
        ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
        ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
        true);

    // Build the orders delete command.
    SqlCommand deleteCommand = new SqlCommand(DELETEORDERS_SP,
        daOrder.SelectCommand.Connection);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    SqlParameterCollection sqlParams = deleteCommand.Parameters;
    sqlParams.Add(ORDERID_PARM, SqlDbType.Int, 0, ORDERID_FIELD);
    daOrder.DeleteCommand = deleteCommand;

    // Build the orders insert command.
    SqlCommand insertCommand = new SqlCommand(INSERTORDERS_SP,
        daOrder.SelectCommand.Connection);
    insertCommand.CommandType = CommandType.StoredProcedure;
    sqlParams = insertCommand.Parameters;
    sqlParams.Add(ORDERID_PARM, SqlDbType.Int, 0,
        ORDERID_FIELD).Direction = ParameterDirection.Output;
    sqlParams.Add(CUSTOMERID_PARM, SqlDbType.NChar, 5, CUSTOMERID_FIELD);
    sqlParams.Add(EMPLOYEEID_PARM, SqlDbType.Int, 0, EMPLOYEEID_FIELD);
    sqlParams.Add(ORDERDATE_PARM, SqlDbType.DateTime, 0, ORDERDATE_FIELD);
    sqlParams.Add(REQUIREDDATE_PARM, SqlDbType.DateTime, 0,
        REQUIREDDATE_FIELD);
    sqlParams.Add(SHIPPEDDDATE_PARM, SqlDbType.DateTime, 0,
        SHIPPEDDDATE_FIELD);
    sqlParams.Add(SHIPVIA_PARM, SqlDbType.Int, 0, SHIPVIA_FIELD);
    sqlParams.Add(FREIGHT_PARM, SqlDbType.Money, 0, FREIGHT_FIELD);
    sqlParams.Add(SHIPNAME_PARM, SqlDbType.NVarChar, 40, SHIPNAME_FIELD);
    sqlParams.Add(SHIPADDRESS_PARM, SqlDbType.NVarChar, 60,
        SHIPADDRESS_FIELD);
    sqlParams.Add(SHIPCITY_PARM, SqlDbType.NVarChar, 15, SHIPCITY_FIELD);
    sqlParams.Add(SHIPREGION_PARM, SqlDbType.NVarChar, 15,
        SHIPREGION_FIELD);
    sqlParams.Add(SHIPPOSTALCODE_PARM, SqlDbType.NVarChar, 10,
        SHIPPOSTALCODE_FIELD);
    sqlParams.Add(SHIPCOUNTRY_PARM, SqlDbType.NVarChar, 15,
        SHIPCOUNTRY_FIELD);
    daOrder.InsertCommand = insertCommand;
    
    // Build the orders update command.
    SqlCommand updateCommand = new SqlCommand(UPDATEORDERS_SP,
        daOrder.SelectCommand.Connection);
    updateCommand.CommandType = CommandType.StoredProcedure;
    sqlParams = updateCommand.Parameters;
    sqlParams.Add(ORDERID_PARM, SqlDbType.Int, 0, ORDERID_FIELD);
    sqlParams.Add(CUSTOMERID_PARM, SqlDbType.NChar, 5, CUSTOMERID_FIELD);
    sqlParams.Add(EMPLOYEEID_PARM, SqlDbType.Int, 0, EMPLOYEEID_FIELD);
    sqlParams.Add(ORDERDATE_PARM, SqlDbType.DateTime, 0, ORDERDATE_FIELD);
    sqlParams.Add(REQUIREDDATE_PARM, SqlDbType.DateTime, 0,
        REQUIREDDATE_FIELD);
    sqlParams.Add(SHIPPEDDDATE_PARM, SqlDbType.DateTime, 0,
        SHIPPEDDDATE_FIELD);
    sqlParams.Add(SHIPVIA_PARM, SqlDbType.Int, 0, SHIPVIA_FIELD);
    sqlParams.Add(FREIGHT_PARM, SqlDbType.Money, 0, FREIGHT_FIELD);
    sqlParams.Add(SHIPNAME_PARM, SqlDbType.NVarChar, 40, SHIPNAME_FIELD);
    sqlParams.Add(SHIPADDRESS_PARM, SqlDbType.NVarChar, 60,
        SHIPADDRESS_FIELD);
    sqlParams.Add(SHIPCITY_PARM, SqlDbType.NVarChar, 15, SHIPCITY_FIELD);
    sqlParams.Add(SHIPREGION_PARM, SqlDbType.NVarChar, 15,
        SHIPREGION_FIELD);
    sqlParams.Add(SHIPPOSTALCODE_PARM, SqlDbType.NVarChar, 10,
        SHIPPOSTALCODE_FIELD);
    sqlParams.Add(SHIPCOUNTRY_PARM, SqlDbType.NVarChar, 15,
        SHIPCOUNTRY_FIELD);
    daOrder.UpdateCommand = updateCommand;

    // Build the order details delete command.
    deleteCommand = new SqlCommand(DELETEORDERDETAILS_SP,
        daOrderDetail.SelectCommand.Connection);
    deleteCommand.CommandType = CommandType.StoredProcedure;
    sqlParams = deleteCommand.Parameters;
    sqlParams.Add(ORDERID_PARM, SqlDbType.Int, 0, ORDERID_FIELD);
    sqlParams.Add(PRODUCTID_PARM, SqlDbType.Int, 0, PRODUCTID_FIELD);
    daOrderDetail.DeleteCommand = deleteCommand;

    // Build the order details insert command.
    insertCommand = new SqlCommand(INSERTORDERDETAILS_SP,
        daOrderDetail.SelectCommand.Connection);
    insertCommand.CommandType = CommandType.StoredProcedure;        
    sqlParams = insertCommand.Parameters;
    sqlParams.Add(ORDERID_PARM, SqlDbType.Int, 0, ORDERID_FIELD);
    sqlParams.Add(PRODUCTID_PARM, SqlDbType.Int, 0, PRODUCTID_FIELD);
    sqlParams.Add(UNITPRICE_PARM, SqlDbType.Money, 0, UNITPRICE_FIELD);
    sqlParams.Add(QUANTITY_PARM, SqlDbType.SmallInt, 0, QUANTITY_FIELD);
    sqlParams.Add(DISCOUNT_PARM, SqlDbType.Real, 0, DISCOUNT_FIELD);
    daOrderDetail.InsertCommand = insertCommand;

    // Build the order details update command.
    updateCommand = new SqlCommand(UPDATEORDERDETAILS_SP,
        daOrderDetail.SelectCommand.Connection);
    updateCommand.CommandType = CommandType.StoredProcedure;
    sqlParams = updateCommand.Parameters;
    sqlParams.Add(ORDERID_PARM, SqlDbType.Int, 0, ORDERID_FIELD);
    sqlParams.Add(PRODUCTID_PARM, SqlDbType.Int, 0, PRODUCTID_FIELD);
    sqlParams.Add(UNITPRICE_PARM, SqlDbType.Money, 0, UNITPRICE_FIELD);
    sqlParams.Add(QUANTITY_PARM, SqlDbType.SmallInt, 0, QUANTITY_FIELD);
    sqlParams.Add(DISCOUNT_PARM, SqlDbType.Real, 0, DISCOUNT_FIELD);
    daOrderDetail.UpdateCommand = updateCommand;

    // Fill the parent and child table.
    daOrder.Fill(dtOrder);
    daOrderDetail.Fill(dtOrderDetail);

    // Bind the default view of the order table to the grid.
    dataGrid.DataSource = dtOrder.DefaultView;
}

private void updateButton_Click(object sender, System.EventArgs e)
{
    // Update order and order details tables.
    daOrderDetail.Update(ds.Tables[ORDERDETAILS_TABLE].Select(null, null,
        DataViewRowState.Deleted));
    daOrder.Update(ds.Tables[ORDERS_TABLE].Select(null, null,
        DataViewRowState.Deleted));
    daOrder.Update(ds.Tables[ORDERS_TABLE].Select(null, null,
        DataViewRowState.ModifiedCurrent));
    daOrder.Update(ds.Tables[ORDERS_TABLE].Select(null, null,
        DataViewRowState.Added));
    daOrderDetail.Update(ds.Tables[ORDERDETAILS_TABLE].Select(null, null,
        DataViewRowState.ModifiedCurrent));
    daOrderDetail.Update(ds.Tables[ORDERDETAILS_TABLE].Select(null, null,
        DataViewRowState.Added));
}

Discussion

The DataGrid control can display a single DataTable or a DataSet containing a set of DataTable objects with a hierarchical relationship between them. The DataGrid provides a user interface for the data, navigation between related tables as well as formatting and editing capabilities. If a DataGrid is bound to a DataSet containing related tables, and navigation is enabled for the DataGrid, expanders will be displayed for each row that has a child relationship.

The DataGrid must be bound to a data source using its DataSource and DataMember properties at design time, or by using the DataSource property or SetBindingMethod( ) at runtime. Valid data sources for the DataGrid include DataTable, DataSet, DataView, and DataViewManager objects.

The DataGrid control dynamically reflects any changes made to the data source. If the ReadOnly property of the DataGrid is set to false, the data source is updated when changes are made to data in the DataGrid. This automatic update happens when the field being edited changes or when the EndEdit( ) method is called on the data source for the DataGrid. The data object that is bound to the DataGrid is responsible for updating the underlying data source.