Tuesday, July 15, 2008

Implementing Custom Paging in ASP.NET with SQL Server 2005

Why Custom Paging?
Custom paging allows you to get limited number records from a large database table that saves processing time of your database server as well as your application server and makes your application scalable, efficient and fast.


In this article, I am going to explain how to create a stored procedure in SQL Server 2005 that allows you to pass startRowIndex and pageSize as a parameter and return you the number of records starting from that row index to the page size specified. It was possible in the SQL Server 2000 too but it was not as easy as in SQL Server 2005 is.


-- EXEC LoadPagedArticles 10, 5

CREATE PROCEDURE LoadPagedArticles

-- Add the parameters for the stored procedure here

@startRowIndex int,

@pageSize int

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

-- increase the startRowIndex by 1 to avoid returning the last record again

SET @startRowIndex = @startRowIndex + 1

BEGIN

SELECT * FROM (

Select *,

ROW_NUMBER() OVER (ORDER BY AutoID ASC) as RowNum

FROM Articles

) as ArticleList

WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @pageSize) - 1

ORDER BY AutoID ASC

END

END

GO

3 comments:

harrinbw526 said...

I've used this same code and it works great, until you get into dealing with large datasets.

wanderer said...

I'm curious how you figured out the initial page size?

Vivek Gupta said...

hey,

nice article but take a look at this article full code -

Custom paging in asp.net using stored procedure in gridview with nice pagination.