WindowsDevCenter.com

O'Reilly Book Excerpts: ADO.NET Cookbook

Cooking with ADO.NET, Part 2

Recipe 7.6: Synchronizing Master-Detail Web Forms DataGrids

Problem

You need to create a master-detail pair of DataGrid controls and synchronize them so that when you select a record in the master, the child grid is updated with the corresponding records.

Solution

Fill a DataSet with results from both tables, and create the necessary relations before binding the DataGrid to the DataSet.

The code for the Web Forms page is shown in Example 7-11.

Example 7-11. File: ADOCookbookCS0706.aspx

<form id="ADOCookbookCS0706" method="post" runat="server">
    <asp:HyperLink id="HyperLink1"
        style="Z-INDEX: 101; LEFT: 16px; POSITION: absolute; TOP: 24px"
        runat="server" NavigateUrl="default.aspx">
        Main Menu
    </asp:HyperLink>
    <br>
    <br>
    <br>
    <asp:DataGrid id="ordersDataGrid" runat="server" PageSize="5"
        AllowPaging="True">
        <SelectedItemStyle BackColor="#80FF80"></SelectedItemStyle>
        <AlternatingItemStyle BackColor="#FFFF99"></AlternatingItemStyle>
        <Columns>
            <asp:ButtonColumn Text="Detail" CommandName="Select">
            </asp:ButtonColumn>
        </Columns>
    </asp:DataGrid>
    <br>
    <br>
    <asp:DataGrid id="orderDetailsDataGrid" runat="server" PageSize="2"
        AllowPaging="True" Width="200px">
        <AlternatingItemStyle BackColor="#FFFF99"></AlternatingItemStyle>
    </asp:DataGrid>
</form>

The code-behind file contains four event handlers and a single method:

Page.Load: Calls the CreateDataSource( ) method and binds the parent data to the parent Web Forms DataGrid, if the page is being loaded for the first time.
CreateDataSource( ): This method fills a DataSet with the Orders table and the Order Details table from the Northwind sample database, creates a relation between the tables, and stores the DataSet to a Session variable to cache the data source for both parent and child DataGrid objects.
Orders DataGrid.SelectedIndexChanged: Gets the cached data from the Session variable. If a row is selected in the Orders data grid, a DataView is created containing Order Details for the row selected in the Orders data grid and bound to the Order Details data grid; otherwise the Order Details data grid is cleared.
Orders DataGrid.PageIndexChanged: Sets the SelectedIndex to -1 so that no Order is selected after the page is changed. The cached data is retrieved from the Session variable, the new page index for the Orders data grid is set, and the data is bound.
Order Details DataGrid.PageIndexChanged: Gets the cached data from the Session variable, creates a DataView containing Order Details for the row selected in the Orders data grid, sets the new page index for the Order Details data grid, and binds the data.

The C# code for the code-behind is shown in Example 7-12.

Example 7-12. File: ADOCookbookCS0706.aspx.cs

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

//  . . . 

if(!Page.IsPostBack)
{
    DataSet ds = CreateDataSource( );

    // Bind the Orders data grid.
    ordersDataGrid.DataSource = ds.Tables["Orders"].DefaultView;
    ordersDataGrid.DataKeyField = "OrderID";

    Page.DataBind( );
}

private DataSet CreateDataSource( )
{
    DataSet ds = new DataSet( );

    // Create a DataAdapter and fill the Orders table with it.
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    da.FillSchema(ds, SchemaType.Source, "Orders");
    da.Fill(ds, "Orders");

    // Create a DataAdapter and fill the Order Details table with it.
    da = new SqlDataAdapter("SELECT * FROM [Order Details]",
        ConfigurationSettings.AppSettings["DataConnectString"]);
    da.FillSchema(ds, SchemaType.Source, "OrderDetails");
    da.Fill(ds, "OrderDetails");

    // Add a relation between parent and child table.
    ds.Relations.Add("Order_OrderDetail_Relation",
        ds.Tables["Orders"].Columns["OrderID"],
        ds.Tables["OrderDetails"].Columns["OrderID"]);

    // Store data in session variable to store data between
    // posts to server.
    Session["DataSource"] = ds;

    return ds;
}

private void ordersDataGrid_SelectedIndexChanged(object sender,
    System.EventArgs e)
{
    // Get the Orders data view from the session variable.
    DataView dv =
        ((DataSet)Session["DataSource"]).Tables["Orders"].DefaultView;

    // Bind the data view to the Orders data grid.
    ordersDataGrid.DataSource = dv;

    // Bind the default view of the child table to the DataGrid.
    if(ordersDataGrid.SelectedIndex != -1)
    {
        // Get the OrderID for the selected Order row.
        int orderId =
            (int)ordersDataGrid.DataKeys[ordersDataGrid.SelectedIndex];

        // Get the selected DataRowView from the Order table.
        dv.Sort = "OrderID";
        DataRowView drv = dv[dv.Find(orderId)];

        // Bind the child view to the Order Details data grid.
        orderDetailsDataGrid.DataSource =
            drv.CreateChildView("Order_OrderDetail_Relation");

        // Position on the first page of the Order Details grid.
        orderDetailsDataGrid.CurrentPageIndex = 0;
    }
    else
        orderDetailsDataGrid.DataSource = null;

    Page.DataBind( );
}

private void ordersDataGrid_PageIndexChanged(object source,
    System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
    // Deselect Orders row after page change.
    ordersDataGrid.SelectedIndex = -1;

    // Get the Orders data from the session variable.
    DataView dv =
        ((DataSet)Session["DataSource"]).Tables["Orders"].DefaultView;

    // Bind the data view to the data grid.
    ordersDataGrid.DataSource = dv;

    // Update the current page in data grid.
    ordersDataGrid.CurrentPageIndex = e.NewPageIndex;

    Page.DataBind( );
}

private void orderDetailsDataGrid_PageIndexChanged(object source,
    System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
    // Get the Orders data view from the session variable.
    DataView dv =
        ((DataSet)Session["DataSource"]).Tables["Orders"].DefaultView;

    // Get the OrderID for the selected Order row.
    int orderId =
        (int)ordersDataGrid.DataKeys[ordersDataGrid.SelectedIndex];

    // Get the selected DataRowView from the Order table.
    dv.Sort = "OrderID";
    DataRowView drv = dv[dv.Find(orderId)];

    // Bind the child view to the Order Details data grid.
    orderDetailsDataGrid.DataSource =
        drv.CreateChildView("Order_OrderDetail_Relation");

    // Update the current page index.
    orderDetailsDataGrid.CurrentPageIndex = e.NewPageIndex;

    orderDetailsDataGrid.DataBind( );
}

Discussion

Unlike the Windows Forms DataGrid control, the Web Forms DataGrid control does not inherently support master-detail views of data. Instead, you must use two Web Forms DataGrid controls and programmatically synchronize them.

The master and child data DataGrid controls in this solution each display one DataTable from a DataSet. Displaying and paging through the data in each of the grids is fundamentally the same as shown in Recipe 7.4.

O'Reilly Learning Lab's .NET Certificate Series -- New! Learn .NET programming skills and earn a .NET Programming Certificate from the University of Illinois Office of Continuing Education. The .NET Certificate Series is comprised of three courses that give you the foundation you need to do .NET programming well. The courses are: Learn XML; Learn Object-Oriented Programming Using Java; and Learn C#. Enroll now in all three courses and save over $500.

The SelectedIndexChanged event handler keeps the two data grids synchronized. When a new item is selected in the Orders DataGrid, the Order data is retrieved from the cached data in the Session variable and bound to the Order DataGrid. The OrderID is obtained from the DataKeys collection for the selected row and used to create a child DataView of the Order Details records that is bound to the Order Details DataGrid.

For information about master-detail data using the Windows Forms DataGrid, see Recipe 2.22.

Pages: 1, 2