Data paging in the database (an ASP.NET approach)

• Download source code + SQL script - 10 Kb

Introduction

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.

Database

In the database I have two tables Members and TempPages. The Members table has the information that will be displayed and the 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 stored procedure) and GetPage (the SP for retrieving the records).

Members Table

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]
GO

TempPages Table

TempPages has a compose key between SessionID, used to identify the user session and RecordNo which is used to number the records starting from 1 .. RecordCount. User_ID collumn will hold the Members.UserID.

CREATE TABLE [TempPages] (
    [SessionID] [varchar] (24)  NOT NULL ,
    [RecordNo] [int] NOT NULL ,
    [User_ID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [TempPages] WITH NOCHECK ADD 
    CONSTRAINT [PK_TempPages] PRIMARY KEY  CLUSTERED 
    (
        [SessionID],
        [RecordNo]
    )  ON [PRIMARY] 
GO

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 TempPages. In my example this stored precedure has only one parameter and that is @SessionID , 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 Members.

InitPages works like this:

First will delete from the TempPages all the records that have SessionID equal with the current @SessionID. In case of a new initialization we delete the records from previous initializations.

After the table is clean I select all the Members.UserID and create a temporary table #TempPages that will hold all the Members.UserIDs plus an IDENTITY field which will later be the RecordNo from TempPages.

Then I select all the rows from the #TempPages and insert them into TempPages with the current session id (@SessionID). I select the @@ROWCOUNT and then drop the #TempPages.

The code for InitPages stored procedure is presented below:

CREATE PROCEDURE InitPages 
    @SessionID varchar(24)
AS

SET NOCOUNT ON

DECLARE @RecordCount int
SET @RecordCount = 0

--Deletes the previous records associated with the current session 

DELETE TempPages WHERE SessionID = @SessionID

-- Create a temporary table with all the UserIDs from Members.

SELECT 
    IDENTITY(int, 1, 1) AS RecordNo,
    CONVERT(int, UserID) AS UserID
INTO #TempPages  
FROM Members
ORDER BY UserID

-- Insert into TempPages the records from #TempPages and with the @SessionID

INSERT INTO TempPages( SessionID, RecordNo, User_ID )
SELECT @SessionID, RecordNo, UserID
FROM #TempPages
ORDER BY RecordNo

SELECT @RecordCount = @@ROWCOUNT

-- Drop the temporary table #TempPages

DROP TABLE #TempPages

-- Return the record count.

SELECT @RecordCount AS RecordCount
GO

GetPage stored procedure

This one is really simple. It needs only 3 parameters: @SessionID, @PageNo and @RowsPerPage. @SessiondID is used to identify the session. @PageNo is the page number and @RowsPerPage 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
    @SessionID    varchar(24),
    @PageNo       int,
    @RowsPerPage  int
AS
SET NOCOUNT ON
DECLARE
  @Start      int,
  @End        int

-- Calculate the boundaries between which I will select 

SET @Start = (@PageNo - 1) * @RowsPerPage + 1
SET @End = @PageNo * @RowsPerPage

-- Select all the records from Members and join TempPages

SELECT
  Members.*
FROM 
  TempPages TP INNER JOIN Members ON TP.User_ID = Members.UserID
WHERE
  TP.SessionID = @SessionID AND
  TP.RecordNo >= @Start AND
  TP.RecordNo <= @End
ORDER BY RecordNo
GO

ASP.NET implementation

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 default.aspx without querystring, the InitPages is executed, then after the records were initialized the 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 GetPage 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 Session.SessionID.ToString().

Overview

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

Disadvantages

• 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 solution.

Remarks

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

Happy Programming!