Custom Paging in ASP.NET 2.0 with SQL Server 2005

In web application it is a routine to paging data retrieved from the database. And in ASP.NET 2.0 it is very simple to paging data by enable the "AllowPaging" property of GridView. This solution works well when the database data amount is not very large. However, if you want to page through thousands, tens of thousands, or hundreds of thousands
of records the default paging model is not viable.

1. Paging Data with SQL Server 200

create procedure [dbo].[P_GetPagedOrders2000]
(@startIndex int,
@endIndex int
)
as
set nocount on
declare @indextable table(id int identity(1,1),nid int)
set rowcount @endIndex
insert into @indextable(nid) select orderid from orders order by orderid desc
select O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID
inner join @indextable t on
O.orderid=t.nid
where t.id between @startIndex and @endIndex order by t.id
set nocount off
RETURN

2. Paging Data with SQL Server 2005

In SQL Server 2005 it is more easier to page data using the keyword "ROW_NUMBER()". The code looks like below.

create PROCEDURE [dbo].[P_GetPagedOrders2005]
(@startIndex INT,
@endindex INT
)
AS
begin
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY O.orderid DESC)AS Row, O.orderid,O.orderdate,O.customerid,C.CompanyName,E.FirstName+' '+E.LastName as EmployeeName
from orders O
left outer join Customers C
on O.CustomerID=C.CustomerID
left outer join Employees E
on O.EmployeeID=E.EmployeeID)

SELECT orderid,orderdate,customerid,companyName,employeeName
FROM orderlist
WHERE Row between @startIndex and @endIndex
end

3. Make it More Generalization

In real application we often need to query data by joining multiple tables,and sorting by any field if possible. The one optimized shown as below.

CREATE Procedure [ISIS].[GetPagingData]
(
@PageIndex int, -- count from 0
@PageSize int, -- record amount shown per page
@SortExpression nvarchar(100), -- Sorting Fields, include ASC or Desc
@TableOrViewName nvarchar(500), -- table or view name
@FieldList nvarchar(2000), -- Fields to Return:* for all
@Filter nvarchar(1000) -- string for where clause
)
AS
Begin
SET NOCOUNT ON
Declare @SQL nvarchar(2000)
Declare @TotalQuery nvarchar(2000)

Set @SQL = '
Select * From (
Select ' + @FieldList + ',
ROW_NUMBER() OVER (ORDER BY ' + @SortExpression + ') as RowNum
FROM ' + @TableOrViewName + ' Where 1=1 And ' + @Filter + '
) as PagedList
WHERE RowNum >= ' + cast(@PageIndex * @PageSize + 1 as nvarchar(6)) + ' AND RowNum <= ' + cast((@PageIndex + 1) * @PageSize as nvarchar(6))

Set @TotalQuery = '
Select count(*)
FROM ' + @TableOrViewName + ' Where 1=1 And ' + @Filter

EXEC sp_executesql @SQL
EXEC sp_executesql @TotalQuery


print @SQL
print @TotalQuery

End

References:

http://blog.guohai.org/?p=460