In last week's tutorial I showed you how to implement the built-in paging functionality
using the ASP.NET DataGrid server control (See DataGrid
Paging Part 1). In Part 2 of the DataGrid paging tutorials I will show you
how to implement a custom paging solution. Downloadable source code includes
versions of the code in this tutorial in both Visual Basic .NET and C#. You can download the source code here.
Why Write Paging Code When it is Built-in?
In many cases the built-in paging functionality offered with the DataGrid control
will suit your needs. Its advantages include its simplicity in implementation
and versatile functionality. However, as in life anything that good has to be
hiding something...right. The drawback to the DataGrid's built-in paging functionality
is that with each page change a PostBack event
fires and the data source is recreated and re-bound to the DataGrid. The entire
data source, not just the single page being displayed. If the table you are
paging through has one million records in it, all one million records are queried,
transported to the application, and then a select few (the current page) are
displayed. I'm guessing you can see where you may run into a slight performance
issue with this.
With custom paging, you control how many records are returned from the data
source. The DataGrid displays rows zero (0) through one less than the PageSize
property value of the DataGrid. In other words, if you specify 10 records
per page, rows 0-9 (10 records) are bound to the DataGrid, the rest are ignored.
Regardless of what page you are rendering, 0 is always the first record bound
to the DataGrid.
In this tutorial I will be showing you how to implement a custom paging solution
using the ASP.NET DataGrid server control and SQL Server. The page you will
be creating will render ten records per page from the Customers
table in the Northwind database. The page-navigation links will be "First
Page", "Previous Page", "Next Page" and "Last
Page". Figure 1.1 shows the final result of what you will learn in this
tutorial.
Figure 1.1 - The DataGrid with a Custom Paging Solution
Bring It On - Getting the Data
The heart and soul of a custom paging solution is in how you retrieve the data.
You want to retrieve only the data needed for rendering and no more. If your
PageSize property is set to 10 (records per
page) then you only want to bring back ten records. The trick is getting the
correct ten records. For this custom paging solution to work you need the stored
procedure in Listing 1.1.
Listing 1.1 - The Stored Procedure
CREATE PROCEDURE [Get_Customers_By_Page]
@CurrentPage int,
@PageSize int,
@TotalRecords int output
AS
--Create a temp table to hold the current page of
data
--Add and ID column to count the records
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
CompanyName nvarchar(40),
ContactName nvarchar (30),
ContactTitle nvarchar (30),
Phone nvarchar (24),
Fax nvarchar (24)
)
--Fill the temp table with the Customers data
INSERT INTO #TempTable
(
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
)
SELECT
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
FROM
Customers
--Create variable to identify the first and last record
that should be selected
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)
--Select one page of data based on the record numbers
above
SELECT
CompanyName,
ContactName,
ContactTitle,
Phone,
Fax
FROM
#TempTable
WHERE
ID > @FirstRec
AND
ID < @LastRec
--Return the total number of records available as
an output parameter
SELECT @TotalRecords = COUNT(*) FROM Customers |
Break It Down
In Listing 1.1 you create a stored procedure to return only the records you
need. This is done by passing in two parameters, the page number being requested
(@CurrentPage) and the number of records
per page (@PageSize). Additionally you create
an output parameter to return the total number of records in the Customers
table.
The stored procedure creates a temporary table and adds an Identity
column (ID). The ID
column is used to find the correct records to return. Once you fill the temporary
table with only data from the columns you want to return, then you can select
the specific rows based on the @CurrentPage
value and the @PageSize value. This is done
by mathematically determining the first record (@FirstRec)
and the last record (@LastRec) to return.
Lastly you get the value for @TotalRecs by
using the COUNT() aggregate function.
I've Got the Data, Now What?
Now that you have the stored procedure you can get to work building the ASP.NET
code to retrieve the correct data. While the sample code in this article
is in Visual Basic.NET, the downloadable sample code has both VB and C# code.
Allow me to start at the end by showing you the page code first, then the programmatic
code. Listing 1.2 shows the Web Form page code.
Listing 1.2 - The Web Form Page Code
<%@ Page Inherits="GridCustomPaging" Src="2001010902.aspx.cs"
%>
<html>
<head>
<style ref="stylesheet" type="text/css">
.tableItem {font: x-small Verdana, Arial, sans-serif;}
.tableHeader {font: bold small Arial; color:#663300;
background-color:#CCCC66;}
.alternatingItem {font: x-small Verdana, Arial, sans-serif;
background-color:#FFFFCC;}
A {color:#663300}
A:hover {color:red}
.pageLinks {font: bold x-small Verdana, Arial, sans-serif;}
</style>
</head>
<body>
<form runat="server" method="post">
<asp:DataGrid runat="server" id="MyDataGrid"
Width="740" CellPadding="4" CellSpacing="0"
Gridlines="Horizontal" HorizontalAlign="Center"
HeaderStyle-CssClass="tableHeader"
ItemStyle-CssClass="tableItem"
AlternatingItemStyle-BackColor="#FFFFCC"
AllowPaging="True"
AllowCustomPaging="True"
PageSize="10"
PagerStyle-Visible="False"
/>
<center>
<p class="pageLinks">
<b>Page
<asp:Label id="CurrentPage" CssClass="pageLinks"
runat="server" />
of
<asp:Label id="TotalPages" CssClass="pageLinks"
runat="server" />
</p>
<asp:LinkButton runat="server" CssClass="pageLinks"
id="FirstPage" Text="[First Page]"
OnCommand="NavigationLink_Click" CommandName="First"
/>
<asp:LinkButton runat="server" CssClass="pageLinks"
id="PreviousPage" Text="[Previous Page]"
OnCommand="NavigationLink_Click" CommandName="Prev"
/>
<asp:LinkButton runat="server" CssClass="pageLinks"
id="NextPage" Text="[Next Page]"
OnCommand="NavigationLink_Click" CommandName="Next"
/>
<asp:LinkButton runat="server" CssClass="pageLinks"
id="LastPage" Text="[Last Page]"
OnCommand="NavigationLink_Click" CommandName="Last"
/>
</center>
</form>
</body>
</html> |
In Listing 1.2 you place a DataGrid on a Web Form. To enable paging you must
set the AllowPaging property to True.
For custom paging you want to disable the built-in paging functions, but
still leave PageSize active. Setting AllowCustomPaging
to True indicated to the the DataGrid that
you will be paging data according to the PageSize
property, but that the built-in paging will not be used. Also, you do not
want the DataGrid's page-navigation links to render since you will be providing
your own controls for paging. Setting the PagerStyle-Visible
property to False hides the page-navigation
links.
Just under the DataGrid are two Label controls. These are used to indicate
to the user where they are (Page 7 of 700 for example). Below that are the LinkButton
controls you will use to navigate through the data. Each LinkButton specifies
an event handler named NavigationLink_Click()
as its OnCommand event handler. The same event
handler will be used for all four LinkButtons. Their CommandName
property will be used to determine which LinkButton was clicked, and how to
navigate through the pages.
The Code Behind
To handle the sequence of events that occurs when a page is requested, then
subsequent pages are navigated to you need to create two event handlers and
one method - Page_Load(), NavigationLink_Click()
and BindData(). The Page_Load()
event handler will be used to set values only when the page request is not a
post-back. Because a click on a LinkButton triggers a PostBack
event we will handle post-backs separately. The NavigationLink_Click()
event will handle post-backs when a page-navigation link is clicked, and set
values for retrieving the data. The BindData()
method is the guts of the whole operation. This is where you will call to the
stored procedure and return only the data you need.
Listing 1.3 shows the begining of the code behind class.
Listing 1.3 - The Page_Load() Event Handler in the Code Behind Class
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
public class GridCustomPaging : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid
MyDataGrid;
protected System.Web.UI.WebControls.Label
CurrentPage;
protected System.Web.UI.WebControls.Label
TotalPages;
protected System.Web.UI.WebControls.LinkButton
NextPage;
protected System.Web.UI.WebControls.LinkButton
PreviousPage;
protected System.Web.UI.WebControls.LinkButton
FirstPage;
protected System.Web.UI.WebControls.LinkButton
LastPage;
protected Int32 _currentPageNumber
= 1;
public void Page_Load ( Object
sender, EventArgs e )
{
if ( !Page.IsPostBack
)
{
BindData();
}
} |
Before you create the Page_Load() event handler,
you need to create a class-level variable for the page number being requested.
This is the _currentPageNumber variable. It
will be used in both event handlers and the BindData()
method. By default we set it to 1.
The Page_Load() event handler calls BindData()
only when the current page request is not a PostBack
event. On the first call to the page the _currentPageNumber
is set to 1, to render the first page of data.
Subsequent page requests will be handled by the NavigationLink_Click()
event handler.
Listing 1.4 - The NavigationLink_Click() Event Handler
protected void NavigationLink_Click ( Object
sender, CommandEventArgs e )
{
switch ( e.CommandName
)
{
case "First":
_currentPageNumber = 1;
break;
case "Last":
_currentPageNumber = Int32.Parse
( TotalPages.Text );
break;
case "Next":
_currentPageNumber = Int32.Parse
( CurrentPage.Text ) + 1;
break;
case "Prev":
_currentPageNumber = Int32.Parse
( CurrentPage.Text ) - 1;
break;
}
BindData();
} |
In the NavigationLink_Click() event handler
you simply reset the _currentPageNumber value
depending on the LinkButton that was clicked. A switch
(or Select Case in Visual Basic .NET) evaluator
is perfect for this purpose. Since both the page number and the total number
of records are being displayed in Label controls on the page, you can use their
value (maintained state with ViewState) to base
the new _currentPageNumber value from. After
the _currentPage value is set, call the BindData()
method.
Listing 1.5 - The BindData() Method
public void
BindData()
{
SqlConnection myConnection = new
SqlConnection(
"server=localhost;database=northwind;trusted_connection=true;"
);
SqlCommand myCommand = new
SqlCommand ( "Get_Customers_By_Page",
myConnection );
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add ( new
SqlParameter ( "@CurrentPage",
SqlDbType.Int ) ).Value = _currentPageNumber;
myCommand.Parameters.Add ( new
SqlParameter ( "@PageSize",
SqlDbType.Int ) ) .Value = MyDataGrid.PageSize;
myCommand.Parameters.Add ( new
SqlParameter ("@TotalRecords",
SqlDbType.Int ) ).Direction =
ParameterDirection.Output;
try
{
myConnection.Open();
MyDataGrid.DataSource = myCommand.ExecuteReader();
MyDataGrid.DataBind();
}
finally
{
myConnection.Close();
}
CurrentPage.Text = _currentPageNumber.ToString();
Double _totalPages = 1;
if ( !Page.IsPostBack
)
{
Int32 _totalRecords = (Int32) myCommand.Parameters["@TotalRecords"].Value;
_totalPages = _totalRecords / MyDataGrid.PageSize;
TotalPages.Text = ( System.Math.Ceiling
( _totalPages ) ).ToString();
}
else
{
_totalPages = Double.Parse ( TotalPages.Text
);
}
if ( _currentPageNumber
== 1 )
{
PreviousPage.Enabled = false;
if ( _totalPages
> 1 )
{
NextPage.Enabled = true;
}
else
{
NextPage.Enabled = false;
}
}
else
{
PreviousPage.Enabled = true;
if ( _currentPageNumber
== _totalPages )
{
NextPage.Enabled = false;
}
else
{
NextPage.Enabled = true;
}
}
} |
The BindData() method uses a SqlCommand to
get the data fro the database. You must set the myCommand.CommandType
= CommandType.StoredProcedure to indicate the first parameter passed
into the SqlCommand is the name of a stored procedure, not a SQL statement.
The @CurrentPage parameter is the number of
the page being requested (the CurrentPageNumber
value). The @PageSize parameter is the value
of the DataGrid's PageSize property. Since the @TotalRecs
parameter is an output parameter you specify this with the myCommand.Parameters["@TotalRecords"].Direction
= ParameterDirection.Output declaration. When you execute the command
only the number of records you need are returned. When you bind the DataGrid,
the number of records specified in the PageSize
property are bound.
Note: In Visual Basic .NET you can use the With
keyword to set all of the parameters of the stored procedure. In C# the With
keyword is not available.
You use an if{...} evaluator to check if this
is the first request for the page. For this example I am not worried that the
number of records in the table may change between requests, so I am having you
set the TotalRecords.Text value only on the
first request. Its state will be maintained across multiple requests. To do
this you use the Ceiling method of the System.Math
class. Since you returned the total number of records from the Customers
table, not the total number of pages you must do a little math to get the total
number of pages. I chose to do this so you have the total number of records
available if you need it. We could easily move the math to the stored procedure
and return the total number of pages. A point to note is that if you are using
custom paging and the DataGrid's standard navigation links (PagerStyle.Visible
= true), then you need to set the VirtualItemCount
of the DataGrid to the total number of records. This MUST be done at
run-time; it is not allowed to be set at design-time.
The Ceiling method takes in a Double
datatype and returns the nearest whole number equal to or greater than the values
passed in. For example, if the total number of records is 91 and the PageSize
is 10, simple division returns 9.1 (9 pages of 10 records and one additional
record). What you really want is 10 pages, with the last page only showing one
record. Ceiling takes the result of this division
and returns 10, the nearest whole number equal to or greater than 9.1.
Using another if{...} evaluator you either
enable or disable the "Previous Page" and "Next Page" LinkButtons
based on the _currentPageNumber value. You certainly
do not want a "Previous Page" link if the user is looking at the first
page. The Enable property of the LinkButton
determines whether or not the Text property
renders with an HREF attribute or not. Either
way the text is still displayed.
Yeah, But Does It Work
Now all the code is in place. Browse to the Web Form to see the fruits of your
labor. You have implemented a custom paging solution that keeps resource usage
to a minimum by retrieving only the values need to be rendered.
Next week I will continue on with the DataGrid by showing you how to implement
column sorting.