Click here to Skip to main content
13,145,774 members (18,772 online)
Click here to Skip to main content
Add your own
alternative version


15 bookmarked
Posted 7 Jul 2010

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.


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)


About the Author

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

You may also be interested in...


Comments and Discussions

GeneralNot really .NET Pin
secorbett13-Jul-10 3:32
membersecorbett13-Jul-10 3:32 
GeneralRe: Not really .NET Pin
Anuj Khadloya13-Jul-10 21:38
memberAnuj Khadloya13-Jul-10 21:38 
GeneralMy vote of 3 Pin
secorbett13-Jul-10 3:28
membersecorbett13-Jul-10 3:28 
QuestionWhy would you run a query which returns a million records? Pin
Keith Worden13-Jul-10 1:22
memberKeith Worden13-Jul-10 1:22 
AnswerRe: Why would you run a query which returns a million records? Pin
Anuj Khadloya13-Jul-10 22:50
memberAnuj Khadloya13-Jul-10 22:50 
GeneralRe: Why would you run a query which returns a million records? Pin
Keith Worden14-Jul-10 0:08
memberKeith Worden14-Jul-10 0:08 
GeneralRe: Why would you run a query which returns a million records? Pin
Anuj Khadloya14-Jul-10 0:26
memberAnuj Khadloya14-Jul-10 0:26 
GeneralGood Pin
Yves12-Jul-10 12:59
memberYves12-Jul-10 12:59 
GeneralRe: Good Pin
sambasivam elayaraja12-Jul-10 21:05
membersambasivam elayaraja12-Jul-10 21:05 
GeneralRe: Good Pin
Anuj Khadloya14-Jul-10 0:55
memberAnuj Khadloya14-Jul-10 0:55 
GeneralMy vote of 4 Pin
debuggervn9-Jul-10 23:12
memberdebuggervn9-Jul-10 23:12 
GeneralNot enough for an article Pin
digital man7-Jul-10 21:28
memberdigital man7-Jul-10 21:28 
GeneralJust enough for an article Pin
aboveunrefined8-Jul-10 3:14
memberaboveunrefined8-Jul-10 3: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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 14 Jul 2010
Article Copyright 2010 by Anuj Khadloya
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid