Cooking with ADO.NET, Part 2

Related Reading ADO.NET Cookbook By Bill Hamilton

Editor's note: In O'Reilly's ADO.NET Cookbook, you'll find more than 150 solutions and best practices for everyday dilemmas. This week, we're offering two more recipes from the book that show how to create, and then synchronize, a master-detail pair of DataGrid controls, as well as how to improve performance when a user pages through a large result set in a data grid. (And if you missed our first batch of recipes from the book, click here.)

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.

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

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

Recipe 9.4: Improving Paging Performance

Problem

Given an application that allows the user to page through a large result set in a data grid, you need to improve the performance of the paging.

Solution

Build a custom paging solution that overcomes the performance limitations of the overloaded Fill( ) method of the DataAdapter.

The sample uses a single stored procedure, which is shown in Example 9-5:

SP0904_PageOrders

Used to return 10 records from the Orders table of the Northwind database that correspond the first, last, next, or previous page, or a specific page. The procedure has the following arguments:

@PageCommand

An input parameter that accepts one of the following values: FIRST, LAST, PREVIOUS, NEXT, or GOTO. This specifies the page of results to return to the client.

@FirstOrderId

An input parameter that contains the OrderID of the first record of the client's current page of Orders data.

@LastOrderId

An input parameter that contains the OrderID of the last record of the client's current page of Orders data.

@PageCount

An output parameter that returns the number of pages, each of which contains 10 records, in the result set.

@CurrentPage

An output parameter that returns the page number of the result set returned.

ALTER PROCEDURE SP0904_PageOrders
    @PageCommand nvarchar(10),
    @FirstOrderId int = null,
    @LastOrderId int = null,
    @PageCount int output,
    @CurrentPage int output
AS
    SET NOCOUNT ON
    
    select @PageCount = CEILING(COUNT(*)/10) from Orders
    
    -- first page is requested or previous page when the current
    -- page is already the first
    if @PageCommand = 'FIRST' or (@PageCommand = 'PREVIOUS' and
        @CurrentPage <= 1)
    begin
        select top 10 *
        from Orders
        order by OrderID
        
        set @CurrentPage = 1
        
        return 0
    end
    
    -- last page is requested or next page when the current
    -- page is already the last
    if @PageCommand = 'LAST' or (@PageCommand = 'NEXT' and
        @CurrentPage >= @PageCount)
    begin
        select a.*
        from
            (select TOP 10 *
            from orders
            order by OrderID desc) a
        order by OrderID
        
        set @CurrentPage = @PageCount
        
        return 0
    end
    
    if @PageCommand = 'NEXT'
    begin
        select TOP 10 * 
        from Orders
        where OrderID > @LastOrderId
        order by OrderID
        
        set @CurrentPage = @CurrentPage+1
        
        return 0
    end
    
    if @PageCommand = 'PREVIOUS'
    begin
        select a.*
        from (
            select TOP 10 *
            from Orders
            where OrderId < @FirstOrderId
            order by OrderID desc) a
        order by OrderID
        
        set @CurrentPage = @CurrentPage-1
        
        return 0
    end
    
    if @PageCommand = 'GOTO'
    begin
        if @CurrentPage < 1
            set @CurrentPage = 1
        else if @CurrentPage > @PageCount
            set @CurrentPage = @PageCount
        
        declare @RowCount int
        set @RowCount = (@CurrentPage * 10)
        
        exec ('select * from
        (select top 10 a.* from
        (select top ' + @RowCount + ' * from Orders order by OrderID) a
        order by OrderID desc) b
        order by OrderID')        
        
        return 0
    end
    
    return 1

The sample code contains six event handlers and a single method:

Form.Load

Sets up the sample by loading the schema for the Orders table from the Northwind database into a DataTable. Next, a DataAdapter is created to select records using the stored procedure to perform the paging through the DataTable. The GetData( ) method is called to load the first page of Orders data.

GetData( )

This method accepts a page navigation argument. The parameters for the stored procedure created in the Form.Load event are set. The Fill( ) method of the DataAdapter is called to execute the stored procedure to retrieve the specified page of records and the output parameters of the stored procedure—@PageCount and @CurrentPage—are retrieved.

Previous Button.Click

Calls the GetData( ) method with the argument PREVIOUS to retrieve the previous page of data from the Orders table.

Next Button.Click

Calls the GetData( ) method with the argument NEXT to retrieve the next page of data from the Orders table.

First Button.Click

Calls the GetData( ) method with the argument FIRST to retrieve the first page of data from the Orders table.

Last Button.Click

Calls the GetData( ) method with the argument LAST to retrieve the last page of data from the Orders table.

Goto Button.Click

Sets the value of the current page to the specified page value and calls the GetData( ) method with the argument GOTO to retrieve that page of data from the Orders table.

The C# code is shown in Example 9-6.

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

private SqlDataAdapter da;
private DataTable table;

// Stored procedure name constants
public const String PAGING_SP      = "SP0904_PageOrders";

// Field name constants
private const String ORDERID_FIELD = "OrderID";

private int currentPage;
private int firstOrderId;
private int lastOrderId;

//  . . . 

private void ImprovePagingPerformanceForm_Load(object sender,
    System.EventArgs e)
{
    // Get the schema for the Orders table.
    da = new SqlDataAdapter("SELECT * FROM Orders",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    table = new DataTable("Orders");
    da.FillSchema(table, SchemaType.Source);

    // Set up the paging stored procedure.
    SqlCommand cmd = new SqlCommand( );
    cmd.CommandText = PAGING_SP;
    cmd.Connection = new SqlConnection(
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@PageCommand", SqlDbType.NVarChar, 10);
    cmd.Parameters.Add("@FirstOrderId", SqlDbType.Int);
    cmd.Parameters.Add("@LastOrderId", SqlDbType.Int);
    cmd.Parameters.Add("@PageCount", SqlDbType.Int).Direction =
        ParameterDirection.Output;
    cmd.Parameters.Add("@CurrentPage", SqlDbType.Int).Direction =
        ParameterDirection.InputOutput;
    da = new SqlDataAdapter(cmd);

    // Get the first page of records.
    GetData("FIRST");
    dataGrid.DataSource = table.DefaultView;
}

public void GetData(string pageCommand)
{
    da.SelectCommand.Parameters["@PageCommand"].Value = pageCommand;
    da.SelectCommand.Parameters["@FirstOrderId"].Value = firstOrderId;
    da.SelectCommand.Parameters["@LastOrderId"].Value = lastOrderId;
    da.SelectCommand.Parameters["@CurrentPage"].Value = currentPage;

    table.Clear( );
    da.Fill(table);

    if(table.Rows.Count > 0)
    {
        firstOrderId = (int)table.Rows[0][ORDERID_FIELD];
        lastOrderId =
            (int)table.Rows[table.Rows.Count - 1][ORDERID_FIELD];
    }
    else
        firstOrderId = lastOrderId = -1;

    int pageCount = (int)da.SelectCommand.Parameters["@PageCount"].Value;
    currentPage = (int)da.SelectCommand.Parameters["@CurrentPage"].Value;

    dataGrid.CaptionText =
        "Orders: Page " + currentPage + " of " + pageCount;
}

private void previousButton_Click(object sender, EventArgs args)
{
    GetData("PREVIOUS");
}

private void nextButton_Click(object sender, EventArgs args)
{
    GetData("NEXT");
}

private void firstButton_Click(object sender, System.EventArgs e)
{
    GetData("FIRST");        
}

private void lastButton_Click(object sender, System.EventArgs e)
{
    GetData("LAST");        
}

private void gotoPageButton_Click(object sender, System.EventArgs e)
{
    try
    {
        currentPage = Convert.ToInt32(gotoPageTextBox.Text);
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message, "Improving Paging Performance",
            MessageBoxButtons.OK, MessageBoxIcon.Warning);
        return;
    }

    GetData("GOTO");
}

Discussion

Overloads of the Fill( ) method of the DataAdapter allow a subset of data to be returned from a query by specifying the starting record and the number of records to return as arguments. This method should be avoided for paging through result sets-especially large ones-because it retrieves the entire result set for the query and subsequently discards the records outside of the specified range. Resources are used to process the entire result set instead of just the subset of required records.

The solution shows how to create a stored procedure that will return a result set corresponding to a page of data from a larger result set. The TOP and WHERE clauses are used together with the primary key (any unique identifier would do) and the sort order. This allows first, last, next, and previous paging. The goto paging is done by nesting SELECT TOP n statements with alternate ascending and descending sorts to get the subset of the records for the page specified. The goto select statement uses a dynamic SQL statement executed using the T-SQL EXEC command. This allows a variable number of TOP n records to be selected within the statement. The EXEC command could also be used to dynamically calculate the top records for all statements so that the number of records per page could be supplied as an input parameter to the stored procedure rather than hardcoded.

Copyright © 2009 O'Reilly Media, Inc.