Custom Data Paging in ASP.NET

• Download source files - 2.53 Kb

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),
    )

    -- fill the temp table with all the products for the 
    -- specified products retrieved from the Products table
    INSERT INTO #TempProducts 
    (
        ProductID,
        ProductName
    )
    SELECT 
        ProductID,
        ProductName
    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
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)
    {
        // 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
        __doPostBack('PageClick','');
    }
</script>
<body bgcolor="black">
    <form id="Topics" method="post" runat="server">
        <!-- Hidden fields store the current page number 
                               and total pages -->
        <input type="hidden" runat="server" 
                           id="PageNumber" value="1">
        <input type="hidden" runat="server" 
                                id="Pages" value="0">
        <!-- Hidden button to handle the click event when 
                              user clicks on a page link-->
        <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()
    {
        // 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 = _
          Math.Ceiling((double)totalRecords/RECORDS_PER_PAGE).ToString();
    
        if (!Page.IsPostBack)
        { 
            // Bind records to repeater
            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();
    
        // Bind Data to the repeater
        ProductsRepeater.DataSource = products;
        ProductsRepeater.DataBind();
    
        // 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>  ";
            else
                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 
         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;
    }
    
    // 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";
                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;
        }
    
        // rebind data
        BindData();
    }
    
    // execute when user clicks on the page link
    void Page_Click(object sender, System.EventArgs e)
    {
        // rebind data
        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.