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:
CREATE TABLE dbo.Employees
(
EmployeeID int identity PRIMARY KEY,
FirstName nvarchar(64),
LastName nvarchar(64)
)
GO
CREATE VIEW [dbo].[Paging_v]
AS
SELECT EmployeeID, FirstName, LastName
FROM dbo.Employees
GO
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.
- 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:
<%@ 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.
- 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.
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);
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)
{
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)
return;
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);
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)
{
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)
return;
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.
