C # row_number implement database Paging

Let's take a look at the code for implementing database paging in C # row_number. ROW_NUMBER is generated to sort by a column in the table [the sorting effect here is the same as that of select * from table order by id] (getdate () can also be used for reading ()), and the ID is automatically generated.

For example, select ROW_NUMBER () over (order by id) as RowId, * the result from adonet is sorted by ID, and a column of RowId is added in front.

The Code is as follows:

Copy code

RowId id name addr datet 1 000000 onepc0 cnblogs-0 2011-10-25 18:25:04. 093 2 000001 onepc1 cnblogs-1 2011-10-25 18:55:04. 093 3 000002 onepc2 cnblogs-2 2011-10-25 19:25:04. 093 4 000003 onepc3 cnblogs-3 2011-10-25 19:55:04. 093 5 000004 onepc4 cnblogs-4 2011-10-25 20:25:04. 093 6 000005 onepc5 cnblogs-5 2011-10-25 20:55:04. 093 7 000006 onepc6 cnblogs-6 2011-10-25 21:25:04. 093 8 000007 onepc7 cnblogs-7 2011-10-25 21:55:04. 093 9 000008 onepc8 cnblogs-8 2011-10-25 22:25:04. 093 10 000009 onepc9 cnblogs-9 2011-10-25 22:55:04. 093 Select ROW_NUMBER () over (order by id desc) as RowId, * from adonet --- ID is in desc order RowId id name addr datet 1 046606 00000 cnblogs-46606 17:25:04. 093 2 046605 11 cnblogs-46605 16:55:04. 093 3 046604 onepc46604 cnblogs-46604 16:25:04. 093 4 046603 onepc46603 cnblogs-46603 15:55:04. 093 5 046602 onepc46602 cnblogs-46602 15:25:04. 093 6 046601 onepc46601 cnblogs-46601 14:55:04. 093 7 046600 onepc46600 cnblogs-46600 14:25:04. 093 8 046599 onepc000099 cnblogs-46599 13:55:04. 093 9 046598 onepc000098 cnblogs-46598 13:25:04. 093 10 046597 onepc000097 cnblogs-46597 12:55:04. 093

Select ROW_NUMBER () over (order by id desc) as RowId, * from adonet order by id or rowid, the following table is clear.

MSSQL Stored Procedure

The Code is as follows:

Copy code

-- Select id, name, addr, datet from adonet -- Select id, name, addr, datet, ROW_NUMBER () over (order by datet) aaa from adonet order by aaa desc -- Select GETDATE () -- Select * from (select ROW_NUMBER () over (order by id) as RowId, * from adonet) temptable -- Where RowId> = 1 and RowId <= 100 Use wentest If exists (select * from sys. sysobjects where type = 'p' and name = 'row _ fy ') Drop procedure row_fy Go Create procedure row_fy @ Pagesize int, -- page size @ Page int -- current page -- @ Outfynum int output With encryption -- encryption As -- Declare @ temp int Select * from ( Select ROW_NUMBER () over (order by id) as RowId, * from Adonet ) As temptable Where RowId> = (@ page-1) * @ pagesize + 1 and RowId <= @ page * @ pagesize

Run

Row_fy 100,1