Efficient Paging with Repeater and SQL Server 2005 / 2008
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.
- 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:
- 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.
- The following code is the paging.aspx file:
- 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.
-- 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.
<%@ 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.
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.