修正过的通用分页存储过程
最近在做毕业设计,用的是李天平老大开发的".Net代码自动生成器"搭建的框架,用起来感觉很不错,但是在用到分页存储时遇到了一个问题,原先的要么只能获取分页所需的数据,要么只能返回一个总共的数据值.这样略显麻烦了点.我就修改了一下源码和存储过程,可同时返回分页所需数据的同时,返回所有数据的个数.
存储过程:
数据层也相应的做了些修改:
存储过程:
1
------------------------------------
2
--用途:分页存储过程(对有主键的表效率极高)
3
--说明:
4
------------------------------------
5![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
6
ALTER PROCEDURE UP_GetRecordByPage
7
@tblName varchar(255), -- 表名
8
@fldName varchar(255), -- 主键字段名
9
@PageSize int = 10, -- 页尺寸
10
@PageIndex int = 1, -- 页码
11
@RowCount int output, -- 返回记录总数
12
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
13
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
14
AS
15![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
16
declare @strSQL varchar(6000) -- 主语句
17
declare @strTmp varchar(100) -- 临时变量
18
declare @strOrder varchar(400) -- 排序类型
19![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
20
if @OrderType != 0
21
begin
22
set @strTmp = '<(select min'
23
set @strOrder = ' order by [' + @fldName +'] desc'
24
end
25
else
26
begin
27
set @strTmp = '>(select max'
28
set @strOrder = ' order by [' + @fldName +'] asc'
29
end
30![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
31
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
32
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
33
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
34
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
35
+ @strOrder
36![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
37
if @strWhere != ''
38
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
39
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
40
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
41
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
42
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
43![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
44
if @PageIndex = 1
45
begin
46
set @strTmp =''
47
if @strWhere != ''
48
set @strTmp = ' where ' + @strWhere
49![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
50
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
51
+ @tblName + ']' + @strTmp + ' ' + @strOrder
52
end
53![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
54![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
55
declare @sql NVARCHAR(400)
56
set @sql = N'select @RowCount=count(*) from '+@tblName+' where '+ @strWhere
57
EXEC sp_executesql @sql,N'@RowCount int OUT',@RowCount OUT
58![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
59
exec (@strSQL)
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
48
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
49
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
50
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
51
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
52
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
53
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
54
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
55
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
56
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
57
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
58
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
59
![](https://maiqi.cnblogs.com/Images/OutliningIndicators/None.gif)
数据层也相应的做了些修改:
1 /// <summary>
2 /// 分页获取数据列表
3 /// </summary>
4 public DataSet GetList(int PageSize,int PageIndex,string strWhere,ref int Output)
5 {
6 SqlParameter[] parameters = {
7 new SqlParameter("@tblName", SqlDbType.VarChar, 255),
8 new SqlParameter("@fldName", SqlDbType.VarChar, 255),
9 new SqlParameter("@PageSize", SqlDbType.Int),
10 new SqlParameter("@PageIndex", SqlDbType.Int),
11 new SqlParameter("@RowCount", SqlDbType.Int),
12 new SqlParameter("@OrderType", SqlDbType.Bit),
13 new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
14 };
15 parameters[0].Value = "TM_TeachInfo";
16 parameters[1].Value = "InfoID";
17 parameters[2].Value = PageSize;
18 parameters[3].Value = PageIndex;
19 parameters[4].Direction = System.Data.ParameterDirection.Output;
20 parameters[5].Value = 1;
21 parameters[6].Value = strWhere;
22
23 DataSet ds = DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
24
25 if (parameters[4].Value != DBNull.Value && parameters[4].Value.ToString() != string.Empty)
26 {
27 Output=Convert.ToInt32(parameters[4].Value);
28 }
29
30 return ds;
31 }
2 /// 分页获取数据列表
3 /// </summary>
4 public DataSet GetList(int PageSize,int PageIndex,string strWhere,ref int Output)
5 {
6 SqlParameter[] parameters = {
7 new SqlParameter("@tblName", SqlDbType.VarChar, 255),
8 new SqlParameter("@fldName", SqlDbType.VarChar, 255),
9 new SqlParameter("@PageSize", SqlDbType.Int),
10 new SqlParameter("@PageIndex", SqlDbType.Int),
11 new SqlParameter("@RowCount", SqlDbType.Int),
12 new SqlParameter("@OrderType", SqlDbType.Bit),
13 new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
14 };
15 parameters[0].Value = "TM_TeachInfo";
16 parameters[1].Value = "InfoID";
17 parameters[2].Value = PageSize;
18 parameters[3].Value = PageIndex;
19 parameters[4].Direction = System.Data.ParameterDirection.Output;
20 parameters[5].Value = 1;
21 parameters[6].Value = strWhere;
22
23 DataSet ds = DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
24
25 if (parameters[4].Value != DBNull.Value && parameters[4].Value.ToString() != string.Empty)
26 {
27 Output=Convert.ToInt32(parameters[4].Value);
28 }
29
30 return ds;
31 }