Web Forms DataGrid and DataSet Programming

Default Paging

Default paging is easy to add using the DataGrid's built in support for paging. With default paging, all of the data is loaded into the DataSet, but only a page of data is displayed in the DataGrid.

A more efficient method is to use "custom paging". In a nutshell, custom paging is implemented using carefully constructed SQL Select statements with a sort index, a corresponding sort order and SQL7's TOP syntax. In custom paging, only a page of data is loaded into memory. The current page index must be manually persisted to the ViewState.

Register the Paging Event Handlers

If you look at the file WebForm1.aspx in the HTML view you will see where the OnPageIndexChanged property is set to the appropriate event handler. The DataGrid will use this property to register the button event handlers:

<asp:datagrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 23px; POSITION: absolute; TOP: 221px" runat="server"
DataKeyField="au_id" DataSource="<%# view %>" Height="270px" Width="679px"
OnUpdateCommand="DataGrid1_Update" OnCancelCommand="DataGrid1_Cancel" OnEditCommand="DataGrid1_Edit"
OnDeleteCommand="DataGrid1_Delete" BorderColor="Blue" OnItemCommand="Item_Click" AllowSorting="True"
OnSortCommand="DataGrid1_Sort"
AllowPaging="True" OnPageIndexChanged="DataGrid1_Page" BackColor="#C0FFFF">

Add a Navigation Bar to the DataGrid

Using the Design view, you can add a navigation bar to the DataGrid by setting the DataGrid properties:

AllowCustomPaging --> False
AllowPaging --> True
PageSize --> 10

Add the Event Handler to WebForm1.aspx.cs

Finally, you need to add the paging event handler with the proper signature to the WebForm1.asp.cs file.

protected void DataGrid1_Page(Object sender, DataGridPageChangedEventArgs e)
{
    DataGrid1.CurrentPageIndex= e.NewPageIndex;
    DataGrid1.EditItemIndex = -1;
    ResetPageIndex(DataGrid1, view);
    DataGrid1.DataBind();
}

Note the call to disable editing by setting the EditItemIndex to -1. You should disable editing when the user pages to a different page or wrong "record" will be left in edit mode. Here again is the ResetPageIndex function. You should call this function on most any page that calls Fill and DataBind. In a multi use environment, you cannot rely on the DataGrid's persisted page index since the actual size of the DataSet may change on Fill(). ResetPageIndex checks for an invalid page index. If the page index is invalid, the function leaves the user on the last page.

// ResetPageIndex resets invalid page index to last page
// ASSERT grid and view NOT NULL
protected void ResetPageIndex(DataGrid grid, DataView view)
{
    // check for invalid page index
    if ((grid.CurrentPageIndex != 0) && (((grid.CurrentPageIndex)*grid.PageSize)>= view.Count))
    {
        // invalid so leave at last page
        if ((view.Count % grid.PageSize)== 0)
        { // ends on page border
            grid.CurrentPageIndex= (view.Count/grid.PageSize)-1;
        }
        else // partial page
        {
            grid.CurrentPageIndex= (view.Count/grid.PageSize);
        }
    }
}

Custom Paging

Default paging is fairly easy to implement! Custom paging is a bit more difficult. Here the SQL from another project that implements custom paging with a page size of one:

private void buttonFirst_Click(object sender, System.EventArgs e)
{
    string sqlCustomers= "Select TOP 1 CompanyName+' '+CustomerID AS sortKey,
CustomerID, CompanyName, City
From Customers WHERE (CompanyName LIKE @CompanyName+'%' OR CompanyName IS NULL)
AND (CustomerID LIKE @CustomerID+'%')
AND (City LIKE @City+'%' OR City IS NULL) ORDER BY sortKey ASC";
    string sqlOrders= "SELECT TOP 1 OrderID, CustomerID, OrderDate FROM Orders
WHERE (CustomerID = @CustomerID) ORDER BY OrderDate";
    NavigateCustomersTable(sqlCustomers,sqlOrders);        
}
private void buttonPrevious_Click(object sender, System.EventArgs e)
{
    string sqlCustomers= "Select TOP 1 CompanyName+' '+CustomerID AS sortKey,
CustomerID, CompanyName, City
From Customers WHERE (CompanyName+' '+CustomerID < @sortKey) AND (CompanyName
LIKE @CompanyName+'%' OR CompanyName IS NULL)
AND (CustomerID LIKE @CustomerID+'%') AND (City LIKE @City+'%' OR City IS NULL)
ORDER BY sortKey DESC";
    string sqlOrders= "SELECT TOP 1 OrderID, CustomerID, OrderDate FROM Orders
WHERE (CustomerID = @CustomerID) ORDER BY OrderDate";
    NavigateCustomersTable(sqlCustomers,sqlOrders);        
}
private void buttonNext_Click(object sender, System.EventArgs e)
{
    string sqlCustomers= "Select TOP 1 CompanyName+' '+CustomerID AS sortKey,
CustomerID, CompanyName, City
From Customers WHERE (CompanyName+' '+CustomerID > @sortKey) AND (CompanyName
LIKE @CompanyName+'%' OR CompanyName IS NULL)
AND (CustomerID LIKE @CustomerID+'%') AND (City LIKE @City+'%' OR City IS NULL)
ORDER BY sortKey ASC";            
    string sqlOrders= "SELECT TOP 1 OrderID, CustomerID, OrderDate FROM Orders
WHERE (CustomerID = @CustomerID) ORDER BY OrderDate";
    NavigateCustomersTable(sqlCustomers,sqlOrders);        
}
private void buttonLast_Click(object sender, System.EventArgs e)
{
    string sqlCustomers= "Select TOP 1 CompanyName+' '+CustomerID AS sortKey,
CustomerID, CompanyName, City
From Customers WHERE (CompanyName LIKE @CompanyName+'%' OR CompanyName IS NULL)
AND (CustomerID LIKE @CustomerID+'%')
AND (City LIKE @City+'%' OR City IS NULL) ORDER BY sortKey DESC";
    string sqlOrders= "SELECT TOP 1 OrderID, CustomerID, OrderDate FROM Orders
WHERE (CustomerID = @CustomerID)
ORDER BY OrderDate";
    NavigateCustomersTable(sqlCustomers,sqlOrders);        
}

Beware, the sort key cannot contain null able columns.

You might also like...

Comments

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Beware of bugs in the above code; I have only proved it correct, not tried it.” - Donald Knuth