Click here to Skip to main content
11,796,275 members (77,463 online)
Click here to Skip to main content

Custom Data Paging in ASP.NET

, 29 May 2003 139.5K 2.3K 60
Rate this:
Please Sign up or sign in to vote.
How to do data paging using Repeater control and some T-SQL programming.

Sample Image - CustomPaging.gif


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.


Use CustomPaging

    ProductID int IDENTITY (1, 1) NOT NULL ,
    ProductName varchar (50) NOT NULL 

@PageNumber int,
@PageSize int
    CREATE TABLE #TempProducts
        ProductID int,
        ProductName varchar(50),

    -- fill the temp table with all the products for the 
    -- specified products retrieved from the Products table
    INSERT INTO #TempProducts 
    FROM Products

    -- declare two variables to calculate the 
    -- range of records to extract for the specified page
    DECLARE @FromID int
    DECLARE @ToID int

    -- calculate the first and last ID of the range of topics we need
    SET @FromID = ((@PageNumber - 1) * @PageSize) + 1
    SET @ToID = @PageNumber * @PageSize

    -- select the page of records
    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 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)
        // save the page number clicked to the hidden field
        document.all.PageNumber.value = id;
        // call the __doPostBack function to post back 
        // the form and execute the PageClick event
<body bgcolor="black">
    <form id="Topics" method="post" runat="server">
        <!--<span class="code-comment"> Hidden fields store the current page number 
                               and total pages --></span>
        <input type="hidden" runat="server" 
                           id="PageNumber" value="1">
        <input type="hidden" runat="server" 
                                id="Pages" value="0">
        <!--<span class="code-comment"> Hidden button to handle the click event when 
                              user clicks on a page link--></span>
        <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" 
        <asp:linkbutton ID="PrevPage" runat="server" 
        <asp:label ID="PagesDisplay" 
        <asp:linkbutton ID="NextPage" 
            runat="server" CommandName="NextPage" 
        <asp:linkbutton ID="LastPage" runat="server" 
        <table width="300" style="border: 1 solid gray" align="center">
            <td bgcolor="gray" 
                style="color: white">Product ID</td>
            <td bgcolor="gray" 
                style="color: white">Product Name</td>
        <asp:repeater ID="ProductsRepeater" runat="server">            
                    <td><%# DataBinder.Eval(Container.DataItem, 
                                            "ProductID") %></td>
                    <td><%# DataBinder.Eval(Container.DataItem, 
                                          "ProductName") %></td>

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()
        // get the number of records found in the database
        totalRecords = GetProductsCount();
        // calculate and save the number of 
        // pages to the Pages hidden field
        Pages.Value = _
        if (!Page.IsPostBack)
            // Bind records to repeater

    void BindData()
        int pageNumber = int.Parse(PageNumber.Value);
        int totalPages = int.Parse(Pages.Value);
        SqlConnection connection = new 
        SqlCommand command = new SqlCommand("GetProductsByPage", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add("@PageNumber", pageNumber);
        command.Parameters.Add("@PageSize", RECORDS_PER_PAGE);
        DataSet products = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = command;
        adapter.Fill(products, "Products");
        // Bind Data to the repeater
        ProductsRepeater.DataSource = products;
        // Display the page links
        PagesDisplay.Text = "";
        for (int i=1; i<=totalPages; i++)
            if (pageNumber != i)
                PagesDisplay.Text += _
                "<a href=\"javascript:ChangePage("+i+")\">"+i+"</a>  ";
                PagesDisplay.Text += "[" + i + "]  ";
        // enable/disable the links to navigate through the pages
        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>";
    // return the number of total records in database
    int GetProductsCount()
        SqlConnection connection = new 
        SqlCommand command = new 
         SqlCommand("SELECT Count(*) FROM Products", connection);
        int count = (int)command.ExecuteScalar();
        return count;
    // execute when user clicks on the next/prev/first/last button
    void Page_Changed(object sender, CommandEventArgs e)
        switch (e.CommandName)
            case "FirstPage":
                PageNumber.Value = "1";
            case "PrevPage":
                PageNumber.Value = _
                  (int.Parse(PageNumber.Value) -1).ToString();
            case "NextPage":
                PageNumber.Value = _
                  (int.Parse(PageNumber.Value) +1).ToString();
            case "LastPage":
                PageNumber.Value = Pages.Value;
        // rebind data
    // execute when user clicks on the page link
    void Page_Click(object sender, System.EventArgs e)
        // rebind data

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 article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Singapore Singapore
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralVery bad code Pin
costoayaco11-Sep-07 22:24
membercostoayaco11-Sep-07 22:24 
QuestionWorks great in IE but the JavaScript fails in FireFox.. Pin
bdykes25-Jun-07 7:33
memberbdykes25-Jun-07 7:33 
Generalfb Pin
priyaram18-Jun-06 18:12
memberpriyaram18-Jun-06 18:12 
QuestionHow about this structure ? Where is failure? Pin
mrphuc27-Aug-05 19:20
membermrphuc27-Aug-05 19:20 
QuestionJavascript??!?!? Pin
Anonymous24-Jun-05 10:13
sussAnonymous24-Jun-05 10:13 
Generalbug:It will not work for more zhan 2 uers Pin
daleboy15-Nov-03 0:20
memberdaleboy15-Nov-03 0:20 
GeneralRe: bug:It will not work for more zhan 2 uers Pin
Anonymous23-Mar-04 8:23
sussAnonymous23-Mar-04 8:23 
GeneralRe: bug:It will not work for more zhan 2 uers Pin
Anonymous9-May-05 2:17
sussAnonymous9-May-05 2:17 
AnswerRe: may be better than the solution given Pin
SoftwareCat22-Dec-06 23:26
memberSoftwareCat22-Dec-06 23:26 
GeneralRe: bug:It will not work for more zhan 2 uers Pin
stojce9-May-05 2:18
sussstojce9-May-05 2:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.151002.1 | Last Updated 30 May 2003
Article Copyright 2003 by Zek3vil
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid