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.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Rant 
Admin