Implement paging in the stored procedure.

I am not very experienced Program Personnel, there will be a lot of problems in the project process. Using paging in the database is a problem I encountered in the project. I checked a lot of information from the Internet, there are many ways. however, I think creating a temporary data table is the easiest way. When I made a membership extension, I found that Microsoft also used it like this. You can simply open a membership storage process.
It's useless to say more. Just check it out. Code It's clear, huh, huh.
1 Create   Procedure DBO. createsimple
2 (
3   @ Pageindex   Int ,
4   @ Pagesize   Int
5 )
6 As
7 Begin
8   -- Define three variables:
9   -- @ Pagelowerbound: the lower limit of the retrieved record.
10   -- @ Pageupperbound: Maximum number of records to be retrieved.
11   -- @ Totalrecords: Total number of returned records, mainly used for page calculation.
12   Declare   @ Pagelowerbound   Int
13   Declare   @ Pageupperbound   Int
14   Declare   @ Totalrecords   Int
15
16   -- Calculates the upper and lower limit values.
17   Set   @ Pagelowerbound = @ Pageindex   *   @ Pagesize
18   Set   @ Pageupperbound = @ Pagelowerbound + @ Pagesize - 1
19
20 -- Create a temporary table:
21 -- Indexid is the identifier, which is automatically increased by 1;
22 -- Simpleid is filled by the data table [simple;
23   Create   Table # Pageindexforsimple
24 (
25 Indexid Int   Identity ( 1 , 1 ) Not   Null ,
26 Simpleid Int
27 )
28 -- Fill temporary table
29 Insert   Into # Pageindexforsimple (simpleid)
30 Select S. [ Simpleid ]
31 From   [ Simple ] S
32 -- Here we can add the where condition and Oder by statements
33
34 -- The total number of retrieved records. In fact, the number of affected rows is the total number of records.
35 Select   @ Totalrecords = @ Rowcount
36
37 -- Obtain the record we want.
38 Select S. *
39 From   [ Simple ] S, # pageindexforsimple P
40 Where S. [ Simpleid ] = P. [ Simpleid ]
41 And P. [ Indexid ] > = @ Pagelowerbound
42 And P. [ Indexid ] <= @ Pageupperbound
43 Order   By S. [ Simple ]
44  
45 -- Total number of returned records.
46 Reture @ Totalrecords
47 End  

The above comments can be understood. Haha, since it is written here, the code of the program is also written: 1 Public list < Simple > Getsimple ( Int Pageindex, Int Pageindex,Out intTotalrecords) {
2 List < Simple > Entity = New List < Simple > ();
3 Sqlparameter [] Param = New Sqlparameter [] {
4 New Sqlparameter ( " @ Pageindex " , Sqldbtype. INT ),
5 New Sqlparameter ( " @ Pagesize " , Sqldbtype. INT ),
6 New Sqlparameter ( " @ Returnvalue " , Sqldbtype. INT ),
7 } ;
8 Param [ 0 ]. Value = Pageindex;
9 Param [ 1 ]. Value = Pagesize;
10 Param [ 2 ]. Direction = Parameterdirection. returnvalue;
11 Sqldatareader Reader = Sqlhelper. executereader (commandtype. storedprocedure, " Getsimple " , Param );
12 While (reader. Read ()) {
13Entity. Add (getsimpleentity (Reader ))
14}
15 Reader. Close ();
16 Try {
17Totalrecords=(Int) Param [2]. Value;
18} Catch {}
19 Return Entity;
20 }

Some of the above functions are self-written:
Sqlhelper class: simplifies the Database Query Class.
Getsimpleentity (sqldatareader reader): Because basic entity classes are often used in projects, a private function is written separately for reuse;
It is worth noting that an error may be caused by dbnull when the total number of records is obtained.

for the first time, I want to criticize and correct it.