Summary of commonly used paging stored procedures in C #

Source: Internet
Author: User

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:
Copy codeThe 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: (using SQL cursor Stored Procedure paging)
Copy codeThe Code is as follows:
Create procedure SqlPager
@ Sqlstr nvarchar (4000), -- query string
@ Currentpage int, -- page N
@ Pagesize int -- number of lines 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...

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.