Implementing Custom Paging in ASP.NET with SQL Server

SheoNarayan
Posted by in Sql Server category on for Advance level | Views : 12898 red flag

Many a times we need to present bulk data to the user in our day to day application development. Loading all data at one shot is okay when we have few hundreads data but when it comes to thousands of thousands records, it hardly work.

In scenario like this we need to go for custom paging in our application.
Introduction

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.

Stored Procedure for Custom Paging in SQL Server 2005

In this example, I am assuming that I have a Articles table that contains thousands of records and I have to list articles from this table page wise. To do that I am going to pass two parameter to this SP and getting the custom paging done.

-- 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

In the above stored procedure, I have two parameter @startRowIndex and @pageSize. @startRowIndex receives the row Index from where records to be returned and @pageSize receives the number of records to be returned.

First I am increasing the @startRowIndex by 1 so that I don't get the last record again in my result then writing my select statement specific to paging. Here you need to make sure that you are writing order by statement same in both subquery and main query like I have specified as "ORDER BY AutoID ASC" in my above code snippet. Now you can write your ASP.NET code that access the method that accepts two parameter and give you the desired result.

In my above example, I will execute the SP in SQL Server with followig parameter value for different pages (I am assuming that I have to get 5 records per page), You should pass same value from your code to the SP.

UPDATE: The pagination code in SQL Server 2012 onward has become much easier and efficient.


Notice the last statement in the following code snippet, just we need to keep the OFFSET .... and we are done.
SELECT * FROM Articles WHERE Active = 1 AND IsPublished = 1 AND
ORDER BY UpdatedDateTime DESC 
OFFSET @startRowIndex ROWS FETCH NEXT @pageSize ROWS ONLY


Page 1 - EXEC LoadPagedArticles 0, 5
Page 2 - EXEC LoadPagedArticles 5, 5
Page 3 - EXEC LoadPagedArticles 10, 5

Conclusion

Its always good to write code that is efficien and fast to serve the user request, Custom paging is most efficient way to get desired number of records from a large database table without hitting the performance of the database as well as application.

Please let me know your feedback if any. Happy Coding!!!

Recommendation
Read Cascading delete in SQL Server after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Dssmanyam on: 7/22/2008
Great Job! You are helping Beginners and also experienced people. Thank You very much

Login to post response

Comment using Facebook(Author doesn't get notification)