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