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

Paging in any ASP.NET grid using SQL Server 2005

, 14 Jul 2010
Rate this:
Please Sign up or sign in to vote.
Implementing paging in any ASP.NET grid using SQL Server 2005.

Introduction

This article helps to add paging to any ASP.NET/WinForms grid. The benefit of using this method is it doesn't fetch all the data at once, but only the required data from the database, thus improving the performance of the page by many folds when the amount of data is large.

Background

After working on different grid controls provided by ASP.NET for over 5 years and using the Microsoft technique of filling all the data in a dataset and then binding it to a grid control, I always used to think there has to be a better way of doing this because of the huge amount of data bound to the control.

Using the Code

My primary aim while using the Microsoft default technique was to show only a few records on a grid and allow paging on the grid. For doing this, I used to fetch all the data from the database, thinking although I am fetching all the data, it happens only once and thus paging becomes much easier and efficient.

But then, one day reality (the cons of this way) came crashing on me, when one fine day my application had almost a million records and the loading of the grid page became super slow. Now I had to search for a better way (the one I always used to think about Wink | ;-) ) which would allow me to only fetch the required rows from the database for displaying. And then the "ROW_NUMBER()" function of SQL came to my aid, and by using this function in my Stored Procedure, the performance of my page improved drastically (I can't think of a better word here). The updated Stored Procedure looks like below.

CREATE PROCEDURE [dbo].[GetEmployeesPage] 
( 
@RowFrom int, 
@RowTo int 
) 
AS 
BEGIN 
SET NOCOUNT ON; 
WITH IndexedEmployees AS 
( 
SELECT 
ROW_NUMBER() OVER (ORDER BY Id) AS rowIndex, 
Employees.* 
FROM 
Employees 
) 

SELECT * FROM IndexedEmployees WHERE rowIndex BETWEEN @RowFrom AND @RowTo 

SELECT COUNT(*) 
FROM 
Employees 

END 
GO

The Stored Procedure returns two results. The first one contains the data that has been paged on the basis of the RowFrom and RowTo parameters, and the second query returns the count of employees, and these can be used on the web page to decide on the paging parameters.

For example: if the 'Employees' table contains 54 rows, then based on the different paging parameters, this will be 1-10, 11-20, 21-30, 31-40, 41-50, 51-54. The second resultset will allow the developer to send the 51-54 parameter.

Points of Interest

The "ROW_NUMBER()" function of SQL Server 2005 is new, and is used to assign ranks to the result of a query. This rank can be used for the paging of records.

An analysis I did by using the traditional method and this method will be of a lot of interest to many of you. I used three ways to fetch data from a table having half a million records.

  • SqlDataAdapter.Fill() method takes 42 seconds to fetch the entire data.
  • SqlDataAdapter.Fill() method with a starting record and page size needs around 10 seconds to fetch page-wise data.

And using the method described above, the same data can be fetched in a fraction of 1 second.

This technique can be extended to add sorting to the grid view as well.

License

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

About the Author

Anuj Khadloya
Technical Lead Kale Consultants Ltd.
India India
No Biography provided

Comments and Discussions

 
GeneralNot really .NET Pinmembersecorbett13-Jul-10 3:32 
GeneralRe: Not really .NET PinmemberAnuj Khadloya13-Jul-10 21:38 
GeneralMy vote of 3 Pinmembersecorbett13-Jul-10 3:28 
QuestionWhy would you run a query which returns a million records? PinmemberKeith Worden13-Jul-10 1:22 
AnswerRe: Why would you run a query which returns a million records? PinmemberAnuj Khadloya13-Jul-10 22:50 
GeneralRe: Why would you run a query which returns a million records? PinmemberKeith Worden14-Jul-10 0:08 
GeneralRe: Why would you run a query which returns a million records? PinmemberAnuj Khadloya14-Jul-10 0:26 
GeneralGood PinmemberYves12-Jul-10 12:59 
GeneralRe: Good Pinmembersambasivam elayaraja12-Jul-10 21:05 
GeneralRe: Good PinmemberAnuj Khadloya14-Jul-10 0:55 
GeneralMy vote of 4 Pinmemberdebuggervn9-Jul-10 23:12 
GeneralNot enough for an article Pinmemberdigital man7-Jul-10 21:28 
GeneralJust enough for an article Pinmemberaboveunrefined8-Jul-10 3:14 

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
Web01 | 2.8.140718.1 | Last Updated 14 Jul 2010
Article Copyright 2010 by Anuj Khadloya
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid