When you have to display a large number of records, the common practice is to
use data paging so the information will be presented in a more user friendly
way. There are many solutions that one can use to implement such a system, each
of them have advantages and disadvantages. In this article I will present a
data paging solution and I will explain how it works and what you gain and what
you loose using this technique.
This solution differ from others because the paging is on the database (data
layer) not on the middle layer therefore when you navigate between pages
only the current page records will be retrieved from database. When you do a
data paging on the middle layer you need to retrieve all the records from the database and
display only a part of them which doesn't sound that good when you have a huge
numbers of records.
How it works
You need to have in the database a "container" table which
will hold the "keys" for all records that will be displayed in the
pages. Also that table must contain a key that will identify the user in this
case I used the ASP.NET session id.
You need two stored procedures one used to init the pages and which will be
only called at the beginning when the user first enters the .aspx page in our
example, and the second one is the stored procedure used to retrieve the
displayed records. When the user navigates between pages only the second stored procedure will be used.
In the database I have two tables
Members table has the information that will be displayed and
TempPages is the container table that I will use for holding
the pages for each user that access the site.
The stored procedures in my example are
InitPages (the initialisation
GetPage (the SP for retrieving the records).
The table in my example is presented in the script below. This table will hold
the information which will be paged.
CREATE TABLE [Members] (
[UserID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (25) ,
[Password] [varchar] (25) ,
[FullName] [varchar] (25) ,
[Email] [varchar] (25)
) ON [PRIMARY]
TempPages has a compose key between
to identify the user session and
RecordNo which is used to number
the records starting from 1 .. RecordCount.
User_ID collumn will
CREATE TABLE [TempPages] (
[SessionID] [varchar] (24) NOT NULL ,
[RecordNo] [int] NOT NULL ,
[User_ID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [TempPages] WITH NOCHECK ADD
CONSTRAINT [PK_TempPages] PRIMARY KEY CLUSTERED
) ON [PRIMARY]
InitPages stored precedure
To initialize which records to be displayed
InitPages will be run
only once at the beginning. The stored procedure will select the
UserIDs from the
Members table and will insert them into
In my example this stored precedure has only one parameter and that is
, the session identifier. You can add more pramaters for filtering the
records that will be page, in this case I select all the records from
InitPages works like this:
First will delete from the
TempPages all the records that have
equal with the current
. In case of a new
initialization we delete the records from previous initializations.
After the table is clean I select all the
create a temporary table
#TempPages that will hold all the
plus an IDENTITY field which will later be the
Then I select all the rows from the
#TempPages and insert them
TempPages with the current session id (
I select the
@@ROWCOUNT and then drop the
The code for
InitPages stored procedure is presented below:
CREATE PROCEDURE InitPages
SET NOCOUNT ON
DECLARE @RecordCount int
SET @RecordCount = 0
DELETE TempPages WHERE SessionID = @SessionID
IDENTITY(int, 1, 1) AS RecordNo,
CONVERT(int, UserID) AS UserID
ORDER BY UserID
INSERT INTO TempPages( SessionID, RecordNo, User_ID )
SELECT @SessionID, RecordNo, UserID
ORDER BY RecordNo
SELECT @RecordCount = @@ROWCOUNT
DROP TABLE #TempPages
SELECT @RecordCount AS RecordCount
GetPage stored procedure
This one is really simple. It needs only 3 parameters:
used to identify the session.
is the page number and
is the maximum number of records which will be displayed on each page.
The code for this stored procedure is presented below:
CREATE PROCEDURE GetPage
SET NOCOUNT ON
SET @Start = (@PageNo - 1) * @RowsPerPage + 1
SET @End = @PageNo * @RowsPerPage
TempPages TP INNER JOIN Members ON TP.User_ID = Members.UserID
TP.SessionID = @SessionID AND
TP.RecordNo >= @Start AND
TP.RecordNo <= @End
ORDER BY RecordNo
In ASP.NET I used a data grid to display the returned records from database.
The datagrid is not involved in paging.
For database access and to work with stored procedures I used the Microsoft
Data Access Application Block for .NET which is a single .NET assembly. It has
all the necessary functionality for data access for Microsoft SQL Server. The
advantages using Microsoft Data Access Application Block (mdaab) is that you
write less code for data access :)) and your data access is optimized.
You can read more about this on
Data Access Application Block for .NET. You can download from there the
installer. I attached the DLL to the source code too.
The demo program is simple. The first time you request the
without querystring, the
InitPages is executed, then after the records were initialized
GetPage stored procedure is executed. When you navigate between pages the PageNo and the PageCount is transmited by
QueryString and if the values are not
null then I execute only
SP. See source code for more details.
Also in the
Session_End I execute a clean up stored procedure
which deletes from the TempPages the records that match the current
Advantages using this method
- Works great for large number of recordsets. By retrieving the only the current
page records from database the load on IIS is reduced alot. This solution is
preferred than the paging in ADO/ADO.NET, if you have 100,000 records and you
want see 20 on each page then only 20 records will be selected from the database.
- If the result need a complicated SQL code, then this code is executed only once
at the initialization and when you navigate between pages only the simple and a
fast SELECT is executed (
GetPage SP ).
- If more concurrent users perform operation like ADD, EDIT, DELETE on the paged
records the ADD operation is not felt by a user who navigates between pages.
The user will see the new added records in the database only after the records
are reinitialized. The EDIT and DELETE operations are ok because I do a JOIN on
TempPages.User_ID = Members.UserID
- Requires another table in the database (
TempPages) and a clean up
If in your application you have to display different types of records then you
could add a new column in the
TempPages that will identify what
kind of records and from where to be selected. For example on your site you want to page the
members and the articles.
Feel free to post your comments and questions :)