Server Side Pagination

The paging of a large database resultset in Web applications is a well known problem. In short, you don't want all the results from your query to be displayed on a single Web page, so some sort of paged display is more appropriate. While it was not an easy task in the old ASP, the DataGrid control in the ASP.NET simplifies this to a few lines of code. So, the paging is easy in ASP.NET, but the default behavior of the DataGrid is that all resulting records from your query will be fetched from SQL server to the ASP.NET application. If your query returns a million records this will cause some serious performance issues (if you need convincing, try executing such a query in your web application and see the memory consumption of the aspnet_wp.exe in the task manager). That's why a custom paging solution is required where desired behavior is to fetch only the rows from the current page.

Here I provide some query which provide solution for custom pagination. It helps to improve your search result for your filter data.

Code

Use [Northwind]

Go

create procedure [dbo].[sp_OrderDetail_pagin]

--parameter declaration

@pageno int = 1,--page no

@pagesize int = 1 -- page size

AS

SET NOCOUNT ON

BEGIN

--check if page no 1 then send only top no of record as per page size

--Generally people visit first page of any table. By this condition 1st page load very fast.

if @pageno = 1

begin

SELECT top (@pagesize )

Orders.OrderID,
Orders.OrderDate,
[Order Details].ProductID,Products.ProductName,

[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,

---- for total record use count(1) over() function

COUNT(1) over() as totalCount

FROM
Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID

ORDER BY Orders.OrderDate desc

end

else

--for other page use with (CTE) query , for better performance fetch only (pageno * pagesize) records

begin

WITH SearchResults (OrderID,
OrderDate, ProductID,ProductName,UnitPrice,Quantity, Discount,
totalCount,RowNum)
AS (

SELECT top (@pageno * @pagesize )

Orders.OrderID,

Orders.OrderDate,
[Order Details].ProductID,Products.ProductName,

[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, COUNT(1) over(),

ROW_NUMBER()
OVER (ORDER BY Orders.OrderDate desc) AS RowNum

FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order

Details].OrderID INNER JOIN Products ON [Order Details].ProductID = Products.ProductID

)

--for get records as per pagesize

SELECT * FROM SearchResults WHERE RowNum BETWEEN ((@pagesize * (@pageno-1)) +1) AND @pagesize * @pageno

end

END