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.