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
DataGrid instead, but the
Repeater control is lightest and also we don't need any special features available in
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
CREATE TABLE Products (
ProductID int IDENTITY (1, 1) NOT NULL ,
ProductName varchar (50) NOT NULL
) ON [PRIMARY]
CREATE PROCEDURE GetProductsByPage
CREATE TABLE #TempProducts
ID int IDENTITY PRIMARY KEY,
INSERT INTO #TempProducts
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
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
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
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:
document.all.PageNumber.value = id;
<form id="Topics" method="post" runat="server">
<input type="hidden" runat="server"
<input type="hidden" runat="server"
<asp:button ID="PageClick" OnClick="Page_Click"
<asp:label ID="Info" runat="server"></asp:label>
<asp:linkbutton ID="PrevPage" runat="server"
<asp:linkbutton ID="LastPage" runat="server"
<table width="300" style="border: 1 solid gray" align="center">
style="color: white">Product ID</td>
style="color: white">Product Name</td>
<asp:repeater ID="ProductsRepeater" runat="server">
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.
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"
<%@ import Namespace="System" %>
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.