Summary of commonly used paging stored procedures in C #

The primary key of the table must be the ID column. [ID] int identity () // you can add one at a time.
1. Paging solution 1: (use not in and select top pages)
Statement format: CopyCode The Code is as follows: Select top 10 *
From testtable
Where (id not in
(Select top 20 ID
From testtable
Order by ID ))
Order by ID

Select top page size *
From testtable
Where (id not in
(Select top page size * Page ID
From table
Order by ID ))
Order by ID

2. Paging solution 2: (use the ID greater than the number of pages and select top pages)
Statement format:Copy codeThe Code is as follows: Select top 10 *
From testtable
Where (ID>
(Select max (ID)
From (select top 20 ID
From testtable
Order by ID) as t ))
Order by ID

Select top page size *
From testtable
Where (ID>
(Select max (ID)
From (select top page size * Page ID
From table
Order by ID) as t ))
Order by ID

3. paging solution 3: (paging using the SQL cursor Stored Procedure) copy Code the code is as follows: Create procedure sqlpager
@ sqlstr nvarchar (4000 ), -- query string
@ currentpage int, -- page n
@ pagesize int -- number of rows per page
as
set nocount on
declare @ P1 int, -- P1 is the cursor id
@ rowcount int
exec sp_cursoropen @ P1 output, @ sqlstr, @ scrolopt = 1, @ ccopt = 1, @ rowcount = @ rowcount output
select ceiling (1.0 * @ rowcount/@ pagesize) as total number of pages --, @ rowcount as total number of rows, @ currentpage as current page
set @ currentpage = (@ currentpage-1) * @ pagesize + 1
exec sp_cursorfetch @ P1, 16, @ currentpage, @ pagesize
exec sp_cursorclose @ P1
set nocount off

4.Copy codeThe Code is as follows: Create procedure framworkpage
@ Tablename varchar (50), -- table name
@ Fields varchar (5000) = '*', -- field name (all fields are *)
@ Orderfield varchar (5000), -- Sort field (required! Supports multiple fields)
@ Sqlwhere varchar (5000) = NULL, -- Condition Statement (Where is not required)
@ Pagesize int, -- number of records per page
@ Pageindex Int = 1, -- specifies the current page number
@ Totalpage int output, -- number of returned results
@ Ordertype bit -- set the sorting type. If the value is 1 in ascending order and the value is 0 in descending order
As
Begin
Declare @ strorder varchar (400) -- sort type

Begin tran -- start transaction
Declare @ SQL nvarchar (4000 );
Declare @ totalrecord int;
-- Calculate the total number of records
If (@ sqlwhere = ''' or @ sqlwhere is null)
Set @ SQL = 'select @ totalrecord = count (*) from' + @ tablename
Else
Set @ SQL = 'select @ totalrecord = count (*) from '+ @ tablename + 'where' + @ sqlwhere
Exec sp_executesql @ SQL, n' @ totalrecord int output', @ totalrecord output -- calculate the total number of records

-- Calculate the total number of pages

Select @ totalpage = @ totalrecord -- ceiling (@ totalrecord + 0.0)/@ pagesize)

If @ ordertype = 0
Begin
Set @ strorder = 'order by ['+ @ orderfield +'] DESC'
-- If @ ordertype is 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strorder = 'order by ['+ @ orderfield +'] ASC'
End

If (@ sqlwhere = ''' or @ sqlwhere is null)
Set @ SQL = 'select * from (select row_number () over ('+ @ strorder +') as rowid, '+ @ fields + 'from' + @ tablename
Else
Set @ SQL = 'select * from (select row_number () over ('+ @ strorder +') as rowid, '+ @ fields + 'from' + @ tablename + 'where' + @ sqlwhere
-- Processing page number out of range
If @ pageindex <= 0
Set @ pageindex = 1

If @ pageindex> @ totalpage
Set @ pageindex = @ totalpage

-- Process start and end points
Declare @ startrecord int
Declare @ endrecord int

Set @ startrecord = (@ pageIndex-1) * @ pagesize + 1
Set @ endrecord = @ startrecord + @ pagesize-1

If @ ordertype = 0
Begin
Set @ strorder = 'order by rowid DESC'
-- If @ ordertype is 0, execute the descending order. This sentence is very important!
End
Else
Begin
Set @ strorder = 'order by rowid ASC'
End
-- Continue merging SQL statements
Set @ SQL = @ SQL + ') as' + @ tablename + 'where rowid between' + convert (varchar (50), @ startrecord) + 'and' + convert (varchar (50), @ endrecord) + ''+ @ strorder
-- Print @ SQL
Exec (@ SQL)
---------------------------------------------------
If @ error <> 0
Begin
Rollback tran
Return-1
End
Else
Begin
Commit tran
Return @ totalrecord --- total number of returned records
End
End

Learn from these things. I think I can find a satisfactory answer...