Simple practical asp.net MSSQL Universal paging Program

Simple and practical ASP tutorial. NET MSSQL Universal Paging program
One, the more omnipotent pagination:

SQL code:

View Sourceprint?1 Select top the number of records displayed per page * from topic where ID isn't in

2 (select top (Current page-1) x number of records displayed per page ID from topic ORDER BY ID DESC)

3 ORDER BY id DESC

Note that you can't be top 0 in access, so if the data is only one page, you have to make a decision.

Second, sql2005 in the paging code:

SQL code:

View Sourceprint?1--a query-telling result set as a temporary table

2 with TempTable as (

3 Select Row_number () over (order by id DESC) as rownum, * from TableName

4)

5--Gets the row of the specified line number range from the temporary table

6 SELECT * from temptable where rownum between @startindex and @endindex

Note: Row_number () over (the ORDER by id DESC) is the sql2005 new function, which indicates that the row number of each column is fetched

Third, paging stored procedure SQL code:

SQL code:

View sourceprint?01 SET ANSI_NULLS on

Go

SET QUOTED_IDENTIFIER ON

Go

05--=============================================

Author: Beef Brisket

Modified--Create date:2009-07-22 12:41

Description: Paging, using Row_number ()

09--=============================================

ALTER procedure [dbo]. [Proc_showpage]

One @tblname varchar (255),--table name

@strgetfields varchar (1000) = ' * ',-the column to be returned, default *

@strorder varchar (255) = ',--sorted field name, required

@strordertype varchar = ' ASC ',--Sort by default ASC

@pagesize int = 10,--page size, default 10

@pageindex int = 1,--page number, default 1

@strwhere varchar (1500) = '--Query criteria (note: Do not add where)

As

19

DECLARE @strsql varchar (5000)

21st

If @strwhere!= '

The Set @strwhere = ' where ' + @strwhere

24

Set @strsql =

' SELECT * FROM (' +

' Select Row_number () over (order by ' + @strorder + ' + @strordertype + ') as POS, ' + @strgetfields + ' +

' From [' + @tblname + '] ' + @strwhere +

As SP where pos between ' +str ((@pageindex-1) * @pagesize + 1) + ' and ' +str (@pageindex * @pagesize)

30

EXEC (@strsql)

Print @strsql--test, see generated SQL statements at query time