修正过的通用分页存储过程
最近在做毕业设计,用的是李天平老大开发的".Net代码自动生成器"搭建的框架,用起来感觉很不错,但是在用到分页存储时遇到了一个问题,原先的要么只能获取分页所需的数据,要么只能返回一个总共的数据值.这样略显麻烦了点.我就修改了一下源码和存储过程,可同时返回分页所需数据的同时,返回所有数据的个数.
存储过程:
数据层也相应的做了些修改:
存储过程:
1
------------------------------------
2
--用途:分页存储过程(对有主键的表效率极高)
3
--说明:
4
------------------------------------
5![](/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![](/Images/OutliningIndicators/None.gif)
16
declare @strSQL varchar(6000) -- 主语句
17
declare @strTmp varchar(100) -- 临时变量
18
declare @strOrder varchar(400) -- 排序类型
19![](/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![](/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![](/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![](/Images/OutliningIndicators/None.gif)
44
if @PageIndex = 1
45
begin
46
set @strTmp =''
47
if @strWhere != ''
48
set @strTmp = ' where ' + @strWhere
49![](/Images/OutliningIndicators/None.gif)
50
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
51
+ @tblName + ']' + @strTmp + ' ' + @strOrder
52
end
53![](/Images/OutliningIndicators/None.gif)
54![](/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![](/Images/OutliningIndicators/None.gif)
59
exec (@strSQL)
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/None.gif)
11
![](/Images/OutliningIndicators/None.gif)
12
![](/Images/OutliningIndicators/None.gif)
13
![](/Images/OutliningIndicators/None.gif)
14
![](/Images/OutliningIndicators/None.gif)
15
![](/Images/OutliningIndicators/None.gif)
16
![](/Images/OutliningIndicators/None.gif)
17
![](/Images/OutliningIndicators/None.gif)
18
![](/Images/OutliningIndicators/None.gif)
19
![](/Images/OutliningIndicators/None.gif)
20
![](/Images/OutliningIndicators/None.gif)
21
![](/Images/OutliningIndicators/None.gif)
22
![](/Images/OutliningIndicators/None.gif)
23
![](/Images/OutliningIndicators/None.gif)
24
![](/Images/OutliningIndicators/None.gif)
25
![](/Images/OutliningIndicators/None.gif)
26
![](/Images/OutliningIndicators/None.gif)
27
![](/Images/OutliningIndicators/None.gif)
28
![](/Images/OutliningIndicators/None.gif)
29
![](/Images/OutliningIndicators/None.gif)
30
![](/Images/OutliningIndicators/None.gif)
31
![](/Images/OutliningIndicators/None.gif)
32
![](/Images/OutliningIndicators/None.gif)
33
![](/Images/OutliningIndicators/None.gif)
34
![](/Images/OutliningIndicators/None.gif)
35
![](/Images/OutliningIndicators/None.gif)
36
![](/Images/OutliningIndicators/None.gif)
37
![](/Images/OutliningIndicators/None.gif)
38
![](/Images/OutliningIndicators/None.gif)
39
![](/Images/OutliningIndicators/None.gif)
40
![](/Images/OutliningIndicators/None.gif)
41
![](/Images/OutliningIndicators/None.gif)
42
![](/Images/OutliningIndicators/None.gif)
43
![](/Images/OutliningIndicators/None.gif)
44
![](/Images/OutliningIndicators/None.gif)
45
![](/Images/OutliningIndicators/None.gif)
46
![](/Images/OutliningIndicators/None.gif)
47
![](/Images/OutliningIndicators/None.gif)
48
![](/Images/OutliningIndicators/None.gif)
49
![](/Images/OutliningIndicators/None.gif)
50
![](/Images/OutliningIndicators/None.gif)
51
![](/Images/OutliningIndicators/None.gif)
52
![](/Images/OutliningIndicators/None.gif)
53
![](/Images/OutliningIndicators/None.gif)
54
![](/Images/OutliningIndicators/None.gif)
55
![](/Images/OutliningIndicators/None.gif)
56
![](/Images/OutliningIndicators/None.gif)
57
![](/Images/OutliningIndicators/None.gif)
58
![](/Images/OutliningIndicators/None.gif)
59
![](/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 }