分页存储过程
这个是我在项目中用到的存储过程分页，现在摘录下来
在SQL数据库编写存储过程语句
1 USE [ExTzglDev]
2 GO
3
4 /****** Object: StoredProcedure [dbo].[UP_GetRecordByPage] Script Date: 07/11/2011 11:16:57 ******/
5 SET ANSI_NULLS ON
6 GO
7
8 SET QUOTED_IDENTIFIER ON
9 GO
10
11
12
13 Create PROCEDURE [dbo].[UP_GetRecordByPage]
14 @TableName varchar(255), -- 表名
15 @PageSize int = 10, -- 页尺寸
16 @PageIndex int = 1, -- 页码
17 @OrderColumn varchar(255), -- 主排序字段名()
18 @OrderType varchar(4)='Asc', -- 设置排序类型, 0 值表示升序
19 @WhereStr varchar(1500)='', -- 查询条件 (注意: 不要加 where)
20 @TotalCount int output, -- 返回记录总数
21 @OrderBy varchar(255)='' -- 副排序字段名
22 AS
23 Declare @strSQL varchar(4000) -- 主语句
24 Declare @Sort nvarchar(500)
25
26 if @OrderColumn <> ''
27 Begin
28 if UPPER( @OrderType) = 'ASC'
29 set @Sort = @OrderColumn+' Asc'
30 else
31 set @Sort = @OrderColumn+' Desc'
32 End
33 if @OrderBy <> ''
34 set @Sort = @Sort+','+@OrderBy
35
36 Set @strSQL = 'SELECT * FROM (SELECT *,ROW_NUMBER() OVER(Order By '+@Sort+') AS rownum
37 FROM '
38 if @WhereStr =''
39 set @strSQL = @strSQL+' '+@TableName+' ) AS D'
40 else
41 set @strSQL = @strSQL+'(Select * From '+@TableName+' WHERE ('+ @WhereStr+')) AS B) AS D'
42
43 set @strSQL = @strSQL + ' WHERE (rownum BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)+')'-- Order By ' + @Sort
44
45 Declare @strCount nvarchar(2000)
46 if @WhereStr <> ''
47 set @strCount =N'select @TotalCount=Count(*) from ['+@TableName+'] Where ('+@WhereStr+')'
48 else
49 set @strCount =N'select @TotalCount=Count(*) from ['+ @TableName +']'
50
51 execute sp_executesql @strCount,N'@TotalCount int output',@TotalCount output
52
53 exec (@strSQL)
54 return @TotalCount
55
56
57
58
59 GO
在数据操作层DAL的代码：
1 /// <summary>
2 /// 分页获取数据列表
3 /// </summary>
4 /// <param name="fieldlist">查找的字段</param>
5 /// <param name="tablename">表名</param>
6 /// <param name="where">查询条件</param>
7 /// <param name="orderfield">排序字段</param>
8 /// <param name="key">主键</param>
9 /// <param name="pageindex">页索引</param>
10 /// <param name="pagesize">每页记录数</param>
11 /// <param name="ordertype">排序方式 0=ASC 1=DESC</param>
12 /// <param name="recordcount">总记录数</param>
13 /// <returns></returns>
14 public DataTable GetPagerData(string tablename,string where, string orderfield,string orderDerect, int pageindex, int pagesize, out int totalCount )
15 {
16 SqlParameter[] para = new SqlParameter[7];
17 para[0] = new SqlParameter("@TableName", tablename);
18 para[1] = new SqlParameter("@PageSize", pagesize);
19 para[2] = new SqlParameter("@PageIndex", pageindex);
20 para[3] = new SqlParameter("@OrderColumn", orderfield);
21 para[4] = new SqlParameter("@OrderType", orderDerect.Trim() == "" ? "Asc" : orderDerect);
22 para[5] = new SqlParameter("@WhereStr", where);
23 para[6] = new SqlParameter("@TotalCount", SqlDbType.Int);
24 para[6].Direction = ParameterDirection.Output;
25 DataTable dt = DbHelperSQL.RunProcedure("UP_GetRecordByPage", para, "resultSet").Tables[0];
26 totalCount = int.Parse(para[6].Value.ToString());
27 return dt;
28
29 }
同在数据操作层的 DbHelperSQL代码为：
View Code
1 /// <summary>
2 /// 执行存储过程
3 /// </summary>
4 /// <param name="storedProcName">存储过程名</param>
5 /// <param name="parameters">存储过程参数</param>
6 /// <param name="tableName">DataSet结果中的表名</param>
7 /// <returns>DataSet</returns>
8 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
9 {
10 using (SqlConnection connection = new SqlConnection(connectionString))
11 {
12 DataSet dataSet = new DataSet();
13 connection.Open();
14 SqlDataAdapter sqlDA = new SqlDataAdapter();
15 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
16 sqlDA.Fill(dataSet, tableName);
17 connection.Close();
18 return dataSet;
19 }
20 }
