Introduction
Even though DataGrid
control has a built-in support for paging through the records of the DataSource
by enabling the AllowPaging
property, Microsoft found that paging this way has a big disadvantage when you have like thousands of records in database or more, since every time you navigate to a new page, those records must be retrieved from the data source into memory. That will reduce the performance painfully. So Microsoft allowed us to implement a custom paging solution to get around this limitation, by enabling the AllowCustomPaging
. Instead of retrieving all records to display each page, now you only retrieve the records needed for the current page.
That sounds like it's something we need, but I found that customizing the data paging this way also has its drawback, because it only works with a table that has an identity column and when the identity column is not missing any value. If certain values are missing, the DataGrid
will display fewer records for some pages than others. For example, the DataGrid
is about to display 10 records with unique IDs from 10 to 20, for some reasons 5 last records are missing. You'll expect the DataGrid
will display ten records from 10 to 25, but actually it won't. It'll display exactly 5 records from 10 to 20, and that is not what you want.
So to get around this problem, I will demonstrate here how to implement our own custom paging solution with a bit more work, by using the Repeater
and T-SQL programming. We could use DataList
or DataGrid
instead, but the Repeater
control is lightest and also we don't need any special features available in DataList
and DataGrid
controls.
To follow along this article, we only need to create a database and one web page. Let's begin with creating a database:
Creating a database
We'll creating a database named CustomPaging
, one table say Products
and then add one stored procedure named GetProductsByPage
to retrieve products for a specific page.
CREATE DATABASE CustomPaging
GO
Use CustomPaging
GO
CREATE TABLE Products (
ProductID int IDENTITY (1, 1) NOT NULL ,
ProductName varchar (50) NOT NULL
) ON [PRIMARY]
GO
CREATE PROCEDURE GetProductsByPage
@PageNumber int,
@PageSize int
AS
CREATE TABLE #TempProducts
(
ID int IDENTITY PRIMARY KEY,
ProductID int,
ProductName varchar(50),
)
INSERT INTO #TempProducts
(
ProductID,
ProductName
)
SELECT
ProductID,
ProductName
FROM Products
DECLARE @FromID int
DECLARE @ToID int
SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
SET @ToID = @PageNumber * @PageSize
SELECT ProductID, ProductName FROM #TempProducts
WHERE ID >= @FromID AND ID <= @ToID
GO
Creating the database and a table in the code is quite straightforward, only the code that creates the stored procedure GetProductsByPage
is quite long and hard to understand if you're not familiar with T-SQL. What is does is, first it creates a temporary table with all the columns that matches the Products
table and a new identity column. It then inserts all the records from the Products
to the temp
table, so now the new identity column of the temp
table has no missing values. Next we declare two variables to calculate the range of records for a requested page.
So by using this stored procedure we only need to pass two parameters PageNumber
and PageSize
(records per page) to retrieve the records that need to be displayed for a page. And now what we do is bind those records to the Repeater
control.
Binding data to the Repeater control
Next, we're gonna bind data to the Repeater
control. So we need to declare a Repeater
control in our web form. The following HTML code adds a Repeater
control and some buttons to navigate through the pages:
<script language="javascript">
function ChangePage(id)
{
document.all.PageNumber.value = id;
__doPostBack('PageClick','');
}
</script>
<body bgcolor="black">
<form id="Topics" method="post" runat="server">
<input type="hidden" runat="server"
id="PageNumber" value="1">
<input type="hidden" runat="server"
id="Pages" value="0">
<asp:button ID="PageClick" OnClick="Page_Click"
runat="server" Visible="false"></asp:button>
<asp:label ID="Info" runat="server"></asp:label>
<asp:linkbutton ID="FirstPage"
runat="server" CommandName="FirstPage"
OnCommand="Page_Changed">First</asp:linkbutton>
<asp:linkbutton ID="PrevPage" runat="server"
CommandName="PrevPage"
OnCommand="Page_Changed">Prev</asp:linkbutton>
<asp:label ID="PagesDisplay"
runat="server"></asp:label>
<asp:linkbutton ID="NextPage"
runat="server" CommandName="NextPage"
OnCommand="Page_Changed">Next</asp:linkbutton>
<asp:linkbutton ID="LastPage" runat="server"
CommandName="LastPage"
OnCommand="Page_Changed">Last</asp:linkbutton>
<br><br>
<table width="300" style="border: 1 solid gray" align="center">
<tr>
<td bgcolor="gray"
style="color: white">Product ID</td>
<td bgcolor="gray"
style="color: white">Product Name</td>
</tr>
<asp:repeater ID="ProductsRepeater" runat="server">
<itemtemplate>
<tr>
<td><%# DataBinder.Eval(Container.DataItem,
"ProductID") %></td>
<td><%# DataBinder.Eval(Container.DataItem,
"ProductName") %></td>
</tr>
</itemtemplate>
</asp:repeater>
</table>
</form>
</body>
We have 2 hidden fields to store the current page number and number of pages. Then we have 4 navigation buttons: First, Prev, Next, Last, all share the same command but with unique command names. that means when one of those buttons is clicked, the event handler Paged_Changed
will be executed. We also have a Label
to display all the pages as links, that helps users to jump to another page a lot easier. Finally we have a Repeater
to display the records.
Note that there's a JavaScript function ChangePage()
which is called when a user clicks on a page number, to save that page number to the PageNumber
hidden field, and then call the __doPostBack
function (automatically generated by ASP.NET), to post the form back and execute the Page_Click
event handler to refresh the page with new records.
That's all it takes for the HTML code. So let's now take a look at the following C# code and see how to bind data to the Repeater
and implement the paging navigator.
<%@ Page Language="C#" ContentType="text/html"
ResponseEncoding="iso-8859-1" %>
<%@ import Namespace="System" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
const int RECORDS_PER_PAGE = 5;
int totalRecords;
void Page_Load()
{
totalRecords = GetProductsCount();
Pages.Value = _
Math.Ceiling((double)totalRecords/RECORDS_PER_PAGE).ToString();
if (!Page.IsPostBack)
{
BindData();
}
}
void BindData()
{
int pageNumber = int.Parse(PageNumber.Value);
int totalPages = int.Parse(Pages.Value);
SqlConnection connection = new
SqlConnection("server=(local);database=CustomPaging;uid=sa;pwd=;");
SqlCommand command = new SqlCommand("GetProductsByPage", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@PageNumber", pageNumber);
command.Parameters.Add("@PageSize", RECORDS_PER_PAGE);
connection.Open();
DataSet products = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
adapter.Fill(products, "Products");
connection.Close();
ProductsRepeater.DataSource = products;
ProductsRepeater.DataBind();
PagesDisplay.Text = "";
for (int i=1; i<=totalPages; i++)
{
if (pageNumber != i)
PagesDisplay.Text += _
"<a href=\"javascript:ChangePage("+i+")\">"+i+"</a> ";
else
PagesDisplay.Text += "[" + i + "] ";
}
FirstPage.Enabled = (pageNumber != 1);
PrevPage.Enabled = (pageNumber != 1);
NextPage.Enabled = (pageNumber != totalPages);
LastPage.Enabled = (pageNumber != totalPages);
Info.Text = totalRecords + " records are found and divided into "
+ Pages.Value + " pages<br><br>";
}
int GetProductsCount()
{
SqlConnection connection = new
SqlConnection("server=(local);database=CustomPaging;uid=sa;pwd=;");
SqlCommand command = new
SqlCommand("SELECT Count(*) FROM Products", connection);
connection.Open();
int count = (int)command.ExecuteScalar();
connection.Close();
return count;
}
void Page_Changed(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "FirstPage":
PageNumber.Value = "1";
break;
case "PrevPage":
PageNumber.Value = _
(int.Parse(PageNumber.Value) -1).ToString();
break;
case "NextPage":
PageNumber.Value = _
(int.Parse(PageNumber.Value) +1).ToString();
break;
case "LastPage":
PageNumber.Value = Pages.Value;
break;
}
BindData();
}
void Page_Click(object sender, System.EventArgs e)
{
BindData();
}
</script>
When the page is loaded, it gets the number of total records by calling the GetProductsCount()
method, then calculate the number of pages and save to the hidden field of the form. Next, it calls the BindData()
method to bind data to the Repeater
control and display the page links if the page is not posted back, because we don't need to rebind data if it's posted back.
There are two event handlers in the above code, the first one is Page_Click
which only rebind data to the Repeater
, the other is Page_Changed
executed when user clicks one of the 4 navigation buttons. It detects which button is clicked by the command names and save the page number that's going to be displayed to the hidden field and rebind data.
So that's all it takes to implement our own custom data paging. Thanks for reading.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.