Click here to Skip to main content
15,499,289 members
Articles / Web Development / ASP.NET
Posted 7 Jul 2010


15 bookmarked

Paging in any ASP.NET grid using SQL Server 2005

Rate me:
Please Sign up or sign in to vote.
4.43/5 (7 votes)
14 Jul 2010CPOL3 min read
Implementing paging in any ASP.NET grid using SQL Server 2005.


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.


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 ;-) ) 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 
WITH IndexedEmployees AS 

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



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.


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

Written By
Technical Lead Kale Consultants Ltd.
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

GeneralNot really .NET Pin
secorbett13-Jul-10 4:32
Membersecorbett13-Jul-10 4:32 
GeneralRe: Not really .NET Pin
Anuj Khadloya13-Jul-10 22:38
MemberAnuj Khadloya13-Jul-10 22:38 
GeneralMy vote of 3 Pin
secorbett13-Jul-10 4:28
Membersecorbett13-Jul-10 4:28 
QuestionWhy would you run a query which returns a million records? Pin
Keith Worden13-Jul-10 2:22
MemberKeith Worden13-Jul-10 2:22 
AnswerRe: Why would you run a query which returns a million records? Pin
Anuj Khadloya13-Jul-10 23:50
MemberAnuj Khadloya13-Jul-10 23:50 
Thats what this article suggests. It provides a way to fetch required number of records (100 in your case scenario) and then fetch the next 100 when the client asks for the new set of records.

I hope this makes it clear.

Cheers Smile | :)
GeneralRe: Why would you run a query which returns a million records? Pin
Keith Worden14-Jul-10 1:08
MemberKeith Worden14-Jul-10 1:08 
GeneralRe: Why would you run a query which returns a million records? Pin
Anuj Khadloya14-Jul-10 1:26
MemberAnuj Khadloya14-Jul-10 1:26 
GeneralGood Pin
Yves12-Jul-10 13:59
MemberYves12-Jul-10 13:59 
GeneralRe: Good Pin
Elayaraja Sambasivam12-Jul-10 22:05
MemberElayaraja Sambasivam12-Jul-10 22:05 
GeneralRe: Good Pin
Anuj Khadloya14-Jul-10 1:55
MemberAnuj Khadloya14-Jul-10 1:55 
GeneralMy vote of 4 Pin
debuggervn10-Jul-10 0:12
Memberdebuggervn10-Jul-10 0:12 
GeneralNot enough for an article Pin
R. Giskard Reventlov7-Jul-10 22:28
MemberR. Giskard Reventlov7-Jul-10 22:28 
GeneralJust enough for an article Pin
aboveunrefined8-Jul-10 4:14
Memberaboveunrefined8-Jul-10 4:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.