Asp. NET tip: Implementing pagination in Stored procedures

Asp.net| stored Procedures | pagination | Tips I'm not a very experienced programmer, in the process of doing the project will encounter a lot of problems, the use of pagination in the database is a problem I encountered in the project. I've looked up a lot of information on the Internet, there are many ways. But I think it's the easiest way to create a temporary datasheet, When I do membership expansion found that Microsoft is the same, you can open a membership stored procedures to see.
Said more also useless, just look at the code is clear, hehe.

1CREATE PROCEDURE dbo. Createsimple
2 (
3 @PageIndex int,
4 @PageSize int
5)
6AS
7BEGIN
8--Define three variables:
9--@PageLowerBound: The lower bound of the record being taken out.
@PageUpperBound: The upper limit of the record to be removed.
One--@TotalRecords: Returns the total number of records, mainly for the calculation of the page.
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @TotalRecords int
15
16--Calculates the value of the upper and lower limits.
SET @PageLowerBound = @PageIndex * @PageSize
SET @PageUpperBound = @PageLowerBound + @PageSize-1
19
20--Create a temporary table:
21--indexid is the logo, automatic growth 1;
The 22--simpleid is populated by a datasheet [simple];
CREATE TABLE #PageIndexForSimple
24 (
IndexID int identity (0,1) not NULL,
num Simpleid int
27)
28--Fill Temp Table
INSERT into #PageIndexForSimple (Simpleid)
SELECT S.[simpleid]
From [Simple] s
32--Here you can add the where condition and oder by statement
33
34--Get the total number of records, actually affect the number of rows is the total number of records
SELECT @TotalRecords =@ @ROWCOUNT
36
37-Get the records we want.
SELECT s.*
From [simple] s, #PageIndexForSimple p
WHERE S.[simpleid]=p.[simpleid]
p.[indexid]>= @PageLowerBound
p.[indexid]<= @PageUpperBound
by S.[simple]
44
45--Returns the total number of records.
Reture @TotalRecords
47END by the above annotation can understand, hehe, since wrote here also the code of the program to write out:
1Public list<simple> getsimple (int pageindex,int pageindex,out int totalrecords) {
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;
Ten param[2]. Direction = ParameterDirection.ReturnValue;
One SqlDataReader reader=sqlhelper.executereader (CommandType.StoredProcedure, "getsimple", param);
The while reader. Read ()) {
Entity. ADD (getsimpleentity (reader))
14}
Reader. Close ();
try{
totalrecords= (int) param[2]. Value;
}catch{}
return entity;
20} Some of the above functions are written by themselves:
SqlHelper class: Simplifies database query classes.
Getsimpleentity (SqlDataReader reader): Because often in the project will use the Good foundation entity class obtains, therefore writes a private function separately, in order to reuse;
It is worth noting that the total number of records may be of type DBNull and result in an error.