ADO.NET Cookbook

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 ...