Click here to Skip to main content
Click here to Skip to main content

Efficient Paging with Repeater and SQL Server 2005 / 2008

, 16 Dec 2011
Rate this:
Please Sign up or sign in to vote.
An efficient implementation of paging using a Repeater and the OVER() keyword in SQL Server 2005.

Introduction

The code provided here provides an optimized paging solution using the ASP.NET Repeater and SQL Server 2005/2008 (not 2000). The implementation does not use temp tables, ROW_COUNT, or similar. It only returns the specified amount of rows, and not the full dataset, to the client for performance reasons. The code is meant to be very simple for easy maintenance.

Background

Paging is used in many places, but it is hard to find an optimized solution that is fast, efficient, and light-weight to implement. With this article, I have tried to implement some of the techniques I think are efficient. All comments and code optimizations are welcome.

Using the code

To use this code, you must have SQL Server 2005, 2008, or later. Express editions will work.

  1. Start by running the paging.sql file - it will create a table [Employees], a view [Paging_v], and a Stored Procedure [Paging]. The code is as follows:
  2. -- Create the table
    CREATE TABLE dbo.Employees 
    (
     EmployeeID int identity PRIMARY KEY,
     FirstName nvarchar(64),
     LastName nvarchar(64)
    )
    GO
    
    -- Create the view
    CREATE VIEW [dbo].[Paging_v]
    AS
    SELECT     EmployeeID, FirstName, LastName
    FROM         dbo.Employees
    GO
    
    
    -- Create the stored procedure
    CREATE PROCEDURE [dbo].[Paging]
    (
      @Page int,
      @PageSize int
    )
    AS
    SET NOCOUNT ON;
    
    SELECT TOP(@PageSize) * FROM 
    (
     SELECT RowID = ROW_NUMBER() OVER (ORDER BY EmployeeID), 
      EmployeeID, FirstName, LastName, TotalRows=Count(*) OVER()
     FROM Paging_v  
    ) 
    A WHERE A.RowID > ((@Page-1)*@PageSize)
    SET NOCOUNT OFF

    As you can see in the Stored Procedure, I'm using the ROW_NUMBER() OVER statement in conjunction with the SQL TOP(@PageSize) to return only the requested amount of rows. This is very efficient compared to #temp tables.

  3. Now load some custom data into the [Employee] table - e.g., add 10-15 rows for testing. I have tested the code with more than 1,000,000 rows and it is still efficient.
  4. The following code is the paging.aspx file:
  5. <%@ Page Language="C#" AutoEventWireup="true" 
             CodeFile="paging.aspx.cs" 
             Inherits="paging" EnableViewState="true" %>
    <html>
    <head>
    <style type="text/css">
    th {text-align:left;}
    div.paging a {padding:0 3 0 3;}
    </style>
    </head>
    
    <body>
    <form runat="server">
    <asp:ScriptManager runat="server" />
    
    <asp:UpdatePanel ID="up1" runat="server">
    <ContentTemplate>
    
    <asp:Repeater ID="rep1" runat="server">
    <HeaderTemplate>
    <table style="width:100%">
    <thead>
        <tr><th>ID</th><th>First</th><th>Last</th></tr>
    </thead>
    <tbody>
    </HeaderTemplate>
    <ItemTemplate>
        <tr>
        <td><%# Eval("EmployeeID")%></td>
        <td><%# Eval("FirstName") %></td>
        <td><%# Eval("LastName") %></td>
        </tr>
    </ItemTemplate>
    <FooterTemplate>
    </tbody>
    </table>
    </FooterTemplate>
    </asp:Repeater>
    <br />
    
    <div class="paging"><asp:PlaceHolder ID="plcPaging" 
       runat="server" /> <asp:HiddenField ID="hdCount" 
       runat="server" Value="0" /></div>
    
    </ContentTemplate>
    </asp:UpdatePanel>
    
    </form>
    </body>
    </html>

    I have encapsulated the Repeater in an UpdatePanel, but you can remove this if needed. There is a placeholder for the actual pager called plcPaging, e.g., "1 2 3 4 5 6 ...", and a HiddenField which contains the total number of rows.

  6. The following is the paging.aspx.cs file - I am using Microsoft Enterprise Library to get the data from SQL Server, but you can use any provider you like.
  7. using System;
    using System.Data;
    using System.Configuration;
    using System.Collections;
    using System.Web;
    using System.Web.Security;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Web.UI.WebControls.WebParts;
    using System.Web.UI.HtmlControls;
    using System.Text;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System.Data.Common;
    
    public partial class paging : System.Web.UI.Page
    {
        int pageSize = 15;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                getData(1); // initially get data from the first page
                createPaging();
            }
            else
            {
                plcPaging.Controls.Clear();
                createPaging();
            }
        }
    
        private void getData(int Page)
        {
            DataTable dt = PagingData(Page, pageSize);
            if (dt.Rows.Count > 0)
            {
                hdCount.Value = dt.Rows[0]["TotalRows"].ToString();
                rep1.DataSource = dt;
                rep1.DataBind();
            }
        }
    
        private DataTable PagingData(int Page, int PageSize)
        {
            // talk to database and return datatable
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand cmd = db.GetStoredProcCommand("Paging"))
            {
                db.AddParameter(cmd, "@Page", DbType.Int32, 4, 
                  ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Default, Page);
                db.AddParameter(cmd, "@PageSize", DbType.Int32, 4, 
                  ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Default, PageSize);
                using (DataTable dt = (db.ExecuteDataSet(cmd)).Tables[0])
                {
                    return dt;
                }
            }
        }
    
        protected void pager_Click(object sender, EventArgs e)
        {
            LinkButton lnk = sender as LinkButton;
            int currentPage = int.Parse(lnk.Text);
            getData(currentPage);
        }
    
        private void createPaging()
        {
            int rowCount = Convert.ToInt32(hdCount.Value.ToString());
            if (rowCount <= pageSize)
            // don't create the pager if there are less rows than specified pageSize.
                return;
    
            // e.g. 9 % 5 = 4 - means we have an extra page,
            // so add 1 to rowCount otherwise add 0
            rowCount = rowCount / pageSize + (rowCount % pageSize != 0 ? 1 : 0); 
    
            for (int i = 0; i < rowCount; i++) 
            {
                LinkButton lnk = new LinkButton();
                lnk.Click += new EventHandler(pager_Click);
                lnk.ID = "p" + (i).ToString();
                lnk.Text = (i+1).ToString();
                plcPaging.Controls.Add(lnk);
            }
        }    
    }

Explanation

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        getData(1); // initially get data from the first page
        createPaging();
    }
    else
    {
        plcPaging.Controls.Clear();
        createPaging();
    }
}

In Page_Load, we just check to see if there is a postback or not. If no postback occurred, we just load the first page of data using getData(1). The createPaging() method is called to create links for each page in the dataset.

Next, in getData(int Page), we get the data from the database via the PagingData(Page,pageSize) method. You can use any provider here as stated previously, but I have used Enterprise Library for clarity.

private void getData(int Page)
{
    DataTable dt = PagingData(Page, pageSize);
    if (dt.Rows.Count > 0)
    {
        hdCount.Value = dt.Rows[0]["TotalRows"].ToString();
        rep1.DataSource = dt;
        rep1.DataBind();
    }
}

private DataTable PagingData(int Page, int PageSize)
{
    // talk to database and return datatable
    Database db = DatabaseFactory.CreateDatabase();
    using (DbCommand cmd = db.GetStoredProcCommand("Paging"))
    {
        db.AddParameter(cmd, "@Page", DbType.Int32, 4, 
          ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Default, Page);
        db.AddParameter(cmd, "@PageSize", DbType.Int32, 4, 
          ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Default, PageSize);
        using (DataTable dt = (db.ExecuteDataSet(cmd)).Tables[0])
        {
            return dt;
        }
    }
}

Finally, I'm using an EventHandler to handle click events for links in the plcPaging control.

protected void pager_Click(object sender, EventArgs e)
{
    LinkButton lnk = sender as LinkButton;
    int currentPage = int.Parse(lnk.Text);
    getData(currentPage);
}

private void createPaging()
{
    int rowCount = Convert.ToInt32(hdCount.Value.ToString());
    if (rowCount <= pageSize)
    // don't create the pager if there are less rows than specified pageSize.
        return;

    // e.g. 9 % 5 = 4 - means we have an extra page, so add 1 to rowCount otherwise add 0
    rowCount = rowCount / pageSize + (rowCount % pageSize != 0 ? 1 : 0); 

    for (int i = 0; i < rowCount; i++) 
    {
        LinkButton lnk = new LinkButton();
        lnk.Click += new EventHandler(pager_Click);
        lnk.ID = "p" + (i).ToString();
        lnk.Text = (i+1).ToString();
        plcPaging.Controls.Add(lnk);
    }
}

The paging only produces numbers like "1 2 3 4 5 6" - it will not produce "previous" and "next" links - any feedback here is welcome.

History

  • 16/12/2011 - Implemented version 1.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

perels

Denmark Denmark
No Biography provided

Comments and Discussions

 
QuestionIt only works on natural sort... PinmemberHaBiX18-Dec-11 21:07 
AnswerRe: It only works on natural sort... Pinmemberperels20-Dec-11 11:19 
GeneralRe: It only works on natural sort... [modified] PinmemberHaBiX20-Dec-11 23:07 
Generalmy vote 4 Pinmembershaheen_mix17-Dec-11 0:16 
GeneralRe: my vote 4 Pinmemberperels17-Dec-11 1:36 

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 | Mobile
Web04 | 2.8.140814.1 | Last Updated 16 Dec 2011
Article Copyright 2011 by perels
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid