Implement paging in stored procedures
Author：Eve Cole
Update Time：2009-07-01 15:55:30
I am not a very experienced programmer, so I will encounter many problems during the project. Using paging in the database is one of the problems I encountered during the project. I checked a lot of information from the Internet, and there are many Method. But I think creating a temporary data table is the simplest method. When I was doing the Membership extension, I found that Microsoft also used it this way. You can just open a Membership stored procedure and take a look.
It's useless to say more, just look at the code and it will be clear, haha.
1CREATE PROCEDURE dbo.CreateSimple
2(
3 @PageIndex int,
4 @PageSize int
5)
6AS
7BEGIN
8 --Define three variables:
9 -- @PageLowerBound: The lower limit of the retrieved records.
10 -- @PageUpperBound: The upper limit of the records to be retrieved.
11 -- @TotalRecords: Returns the total number of records, mainly used for page calculations.
12 DECLARE @PageLowerBound int
13 DECLARE @PageUpperBound int
14 DECLARE @TotalRecords int
15
16 --Calculate the values of upper and lower limits.
17 SET @PageLowerBound=@PageIndex * @PageSize
18 SET @PageUpperBound=@PageLowerBound+@PageSize-1
19
20--Create temporary table:
21--IndexId is the identifier and automatically increases by 1;
22--SimpleId is filled in by data table [Simple];
23 CREATE TABLE #PageIndexForSimple
twenty four (
25 IndexId int identity(0,1) NOT NULL,
26 SimpleId int
27)
28--Filling the temporary table
29 INSERT INTO #PageIndexForSimple(SimpleId)
30 SELECT s.[SimpleId]
31 FROM [Simple] s
32 --WHERE condition and ODER BY statements can be added here
33
34 --Get the total number of records. In fact, the number of affected rows is the total number of records.
35 SELECT @TotalRecords=@@ROWCOUNT
36
37 --Get 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 --Returns the total number of records.
46 RETURE @TotalRecords
47END You can understand it from the above comments. Haha, now that I have written it here, also write the code of the program:
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;
10 param[2].Direction = ParameterDirection.ReturnValue;
11 SqlDataReader reader=SqlHelper.ExecuteReader(CommandType.StoredProcedure, "GetSimple", param);
12 While(reader.Read()){
13 entity.Add(GetSimpleEntity(reader))
14}
15 reader.Close();
16 try{
17 totalRecords=(int)param[2].Value;
18 }catch{}
19 return entity;
20} Some of the above functions were written by myself:
SqlHelper class: Simplified database query class.
GetSimpleEntity (SqlDataReader reader): Since the acquisition of basic entity classes is often used in projects, a separate private function is written for reuse;
It is worth noting that when obtaining the total number of records, the type may be DbNull, causing an error.
http://www.cnblogs.com/xdotnet/archive/2006/09/19/procedure_for_paging_select.html
