SQLServer2005分页存储过程
参考网络上多个例子之后，总结出适合自己的版本：
存储过程pro_PageList：
1 /** 2 数据分页存储过程 3 配合类使用：\App_Code\PageList.cs 4 ====================================== 5 ▲支持 联表、left/right/inner join 6 ▲支持 select top n 7 ▲支持 group by 8 ▲不支持 select distinct ————请使用group by代替distinct 9 */ 10 if exists (select 1 11 from sysobjects 12 where id = object_id('dbo.pro_PageList') 13 and type in ('P','PC')) 14 drop procedure dbo.pro_PageList 15 go 16 17 18 /** 19 数据分页存储过程 20 1.详细参数意义，请参考类/App_Code/PageList.cs，在这里只是简单备注 21 2.不支持distinct，请使用group by代替distinct 22 */ 23 create procedure dbo.pro_PageList ( 24 @strTables varchar(max), --表名或视图名称 25 @strFields varchar(max), --要显示的字段 26 @strWhere varchar(max), --where条件 27 @pageIndex int, --当页页码 28 @pageSize int, --页码容量 29 @strOrder varchar(255), --排序字段及规则 30 @isGetCount int, --是否计算记录总数（0：是 1：否[此时值为记录总数]） 31 @strGroupby varchar(255), --GROUP BY字段 32 @strOrderWithoutPrefix varchar(255), --不带表名前缀的排序字段（a.name desc --> name desc）；仅当@isGroupby=1时才有意义 33 @recCount int output, --返回：记录总数 34 @pageCount int output, --返回：总页数 35 @exeSql varchar(max) output, --返回：当前页面执行的sql 36 @allSql varchar(max) output --返回：不加分页查询的sql 37 ) as 38 begin 39 declare @countSql nvarchar(max) 40 declare @isGroupby int --是否Group by （0：否 1：是）；带有group by的查询及计算count的方式有很大不同，所以必须指定是否Group by，以节省性能开销 41 set nocount on; 42 43 --查询的字段 44 if @strFields is null or @strFields = '' 45 set @strFields='*' 46 47 --查询的条件 48 if @strWhere is null or @strWhere='' 49 set @strWhere='' 50 else 51 set @strWhere=' WHERE ' + @strWhere 52 53 --GROUP BY 54 if @strGroupby is null or @strGroupby='' 55 begin 56 set @strGroupby='' 57 set @isGroupby = 0 58 end 59 else 60 begin 61 set @strGroupby=' GROUP BY ' + @strGroupby 62 set @isGroupby = 1 63 end 64 65 --查询所有数据的sql 66 if(@isGroupby = 0) --带有Group by 67 begin 68 --注：这一行不要轻易改动，因为PageList.cs中有使用正则删除PageList__RowNum 69 set @allSql = 'SELECT ' + @strFields + ',ROW_NUMBER() OVER(ORDER BY ' + @strOrder + ') AS PageList__RowNum FROM ' + @strTables + @strWhere + @strGroupby 70 end 71 else --带有group by的查询及计算count的方式有很大不同，所以分开写 72 begin 73 --注：这一行不要轻易改动，因为PageList.cs中有使用正则删除PageList__RowNum 74 set @allSql = 'SELECT ' + @strFields + ' FROM ' + @strTables + @strWhere + @strGroupby 75 set @allSql = 'SELECT *,ROW_NUMBER() OVER(ORDER BY ' + @strOrderWithoutPrefix + ') AS PageList__RowNum FROM ('+ @allSql +') AS __tmpTable' 76 end 77 78 --查询数据集的sql 79 set @exeSql=' SELECT * FROM ( '+ @allSql + ') AS Dwhere WHERE PageList__RowNum BETWEEN ' + CAST(((@pageIndex-1)*@pageSize + 1) as nvarchar(20)) + ' AND ' + cast((@pageIndex*@pageSize) as nvarchar(20)) 80 81 --执行查询 82 exec (@exeSql) 83 84 --计算记录数 85 if(@isGetCount = 0) 86 begin 87 if(@isGroupby = 0) --带有Group by 88 set @countSql='SELECT @recCount=COUNT(*) FROM ' + @strTables + @strWhere + @strGroupby 89 else --带有group by的查询及计算count的方式有很大不同，所以分开写 90 set @countSql='SELECT @recCount=COUNT(*) FROM ( 91 SELECT COUNT(*) AS iCount FROM ' + @strTables + @strWhere + @strGroupby +' 92 ) AS __tmpTable' 93 --计算 94 exec sp_executesql @countSql, N'@recCount INT OUT ',@recCount output 95 end 96 else 97 begin 98 set @recCount = @isGetCount 99 end 100 101 --计算分页总数 102 SET @pageCount=(@recCount + @pageSize - 1) / @pageSize 103 104 --返回 105 select @recCount as recCount 106 select @pageCount as pageCount 107 select @exeSql as exeSql 108 select @allSql as allSql 109 110 set nocount off; 111 end 112 go
调用类PageList.cs：
1 using System; 2 using System.Collections.Generic; 3 using System.Data; 4 using System.Linq; 5 using System.Text.RegularExpressions; 6 using System.Web; 7 using XL.DB.DbOperaClass; 8 9 /// <summary> 10 /// 通用分页类 11 /// </summary> 12 public class PageList 13 { 14 /// <summary> 15 /// 执行分页查询 16 /// </summary> 17 /// <param name="pageListData">输入、输出参数对象，具体参数说明请参考PageListData类</param> 18 public static PageListData GetPageListData(ref PageListData pageListData) 19 { 20 #region 存储过程参数赋值 21 DbParameterStruct[] para = null; 22 para = new DbParameterStruct[13]; 23 para[0] = new DbParameterStruct(); 24 para[0].ParaName = "strTables"; 25 para[0].ParaValue = pageListData.strTables; 26 para[1] = new DbParameterStruct(); 27 para[1].ParaName = "strFields"; 28 para[1].ParaValue = pageListData.strFields; 29 para[2] = new DbParameterStruct(); 30 para[2].ParaName = "strWhere"; 31 para[2].ParaValue = pageListData.strWhere; 32 para[3] = new DbParameterStruct(); 33 para[3].ParaName = "pageIndex"; 34 para[3].ParaValue = pageListData.pageIndex; 35 para[4] = new DbParameterStruct(); 36 para[4].ParaName = "pageSize"; 37 para[4].ParaValue = pageListData.pageSize; 38 para[5] = new DbParameterStruct(); 39 para[5].ParaName = "strOrder"; 40 para[5].ParaValue = pageListData.strOrder; 41 para[6] = new DbParameterStruct(); 42 para[6].ParaName = "isGetCount"; 43 para[6].ParaValue = pageListData.isGetCount; 44 para[7] = new DbParameterStruct(); 45 para[7].ParaName = "recCount"; 46 para[7].ParaValue = pageListData.recCount; 47 para[8] = new DbParameterStruct(); 48 para[8].ParaName = "pageCount"; 49 para[8].ParaValue = pageListData.pageCount; 50 para[9] = new DbParameterStruct(); 51 para[9].ParaName = "exeSql"; 52 para[9].ParaValue = pageListData.exeSql; 53 para[10] = new DbParameterStruct(); 54 para[10].ParaName = "allSql"; 55 para[10].ParaValue = pageListData.allSql; 56 para[11] = new DbParameterStruct(); 57 para[11].ParaName = "strGroupby"; 58 para[11].ParaValue = pageListData.strGroupby; 59 para[12] = new DbParameterStruct(); 60 para[12].ParaName = "strOrderWithoutPrefix"; 61 para[12].ParaValue = pageListData.strOrderWithoutPrefix; 62 #endregion 63 64 DataSet dsResult = XL.WEB.PageBaseClass.DbPageBaseClass.DBConnOperaObj.ExecuteProcedureDs("pro_PageList", para); 65 pageListData.dtResult = dsResult.Tables[0]; 66 pageListData.recCount = int.Parse(dsResult.Tables[1].Rows[0]["recCount"].ToString()); 67 pageListData.pageCount = int.Parse(dsResult.Tables[2].Rows[0]["pageCount"].ToString()); 68 pageListData.exeSql = dsResult.Tables[3].Rows[0]["exeSql"].ToString(); 69 pageListData.allSql = dsResult.Tables[4].Rows[0]["allSql"].ToString(); 70 71 return pageListData; 72 } 73 } 74 75 /// <summary> 76 /// 分页查询功能的 输入、输出参数 77 /// </summary> 78 public class PageListData 79 { 80 #region 定义变量 ————详细意义，请参下面公有变量的注释 81 private string _strTables = "";//表名或视图名称 82 private string _strFields = "*";//要显示的字段（默认为所有字段） 83 private string _strWhere = "";//查询条件 84 85 private int _pageIndex = 1;//当前第n页 86 private int _pageSize = 30;//请求页面大小 87 88 private string _strOrder = "";//排序字段及规则 89 private int _isGetCount = 0;//是否计算记录总数（默认计算） 90 91 private int _recCount = 0;//总行数 92 private int _pageCount = 0;//总页数 93 94 private string _exeSql = "";//当前页面执行的sql 95 private string _allSql = "";//不加分页查询的sql（即返回所有数据的sql） 96 97 private string _strGroupby = "";//Group by字段 98 private string _strOrderWithoutPrefix = "";//不带表名前缀的排序字段（此参数值根据@strOrder自动生成） 99 100 private DataTable _dtResult = null;//当前页面的查询结果数据集 101 #endregion 102 103 /// <summary> 104 /// [入参，必填]表名或视图名称 105 /// </summary> 106 /// <remarks> 107 /// 支持联表、left/right/inner join 等，例如：t_um_user a left join t_um_department b on (b.id=a.fid) 108 /// </remarks> 109 public string strTables 110 { 111 get { return _strTables; } 112 set { _strTables = value; } 113 } 114 115 /// <summary> 116 /// [入参]字段名，默认为* 117 /// </summary> 118 public string strFields 119 { 120 get { return _strFields; } 121 set { _strFields = value; } 122 } 123 124 /// <summary> 125 /// [入参]查询条件，不加where，无查询条件时可留空 126 /// </summary> 127 public string strWhere 128 { 129 get { return _strWhere; } 130 set { _strWhere = value; } 131 } 132 133 /// <summary> 134 /// [入参]当前第几页 135 /// </summary> 136 public int pageIndex 137 { 138 get { return _pageIndex; } 139 set { _pageIndex = value; } 140 } 141 142 /// <summary> 143 /// [入参]每页显示的记录数 144 /// </summary> 145 public int pageSize 146 { 147 get { return _pageSize; } 148 set { _pageSize = value; } 149 } 150 151 /// <summary> 152 /// [入参，必填]排序字段及规则 153 /// </summary> 154 /// <remarks> 155 /// 不用加order by，例如：b.id asc,sum(a.id) desc 156 /// 如果有group by语句，则 排序字段必须出现在@strFields中（如果有别名，则排序字段必须使用别名） 157 /// </remarks> 158 public string strOrder 159 { 160 get { return _strOrder; } 161 set { _strOrder = value; } 162 } 163 164 /// <summary> 165 /// [入参]是否计算记录总数 166 /// </summary> 167 /// <remarks> 168 /// 0：计算（默认） 169 /// 其它值：不计算，此时参数值代表记录总数，即@recCount直接返回该参数值 170 /// </remarks> 171 public int isGetCount 172 { 173 get { return _isGetCount; } 174 set { _isGetCount = value; } 175 } 176 177 /// <summary> 178 /// [入参]Group by字段 179 /// </summary> 180 /// <remarks> 181 /// 不用加group by 182 /// 例如：b.id,a.id 183 /// 或者，再复杂一点：b.id,a.id having count(*)>1 184 /// </remarks> 185 public string strGroupby 186 { 187 get { return _strGroupby; } 188 set { _strGroupby = value; } 189 } 190 191 /// <summary> 192 /// [入参]不带表名前缀的排序字段（a.name desc --> name desc） 193 /// </summary> 194 /// <remarks> 195 /// 仅当@strGroupby有值时才有意义 196 /// 排序字段必须出现在@strFields中（如果有别名，则排序字段必须使用别名） 197 /// 此参数值根据@strOrder自动生成 198 /// </remarks> 199 public string strOrderWithoutPrefix 200 { 201 get 202 { 203 //删除表名前缀（a.name desc --> name desc） 204 _strOrderWithoutPrefix = Regex.Replace(_strOrder, @"(\w)+\.", ""); 205 return _strOrderWithoutPrefix; 206 } 207 set { _strOrderWithoutPrefix = value; } 208 } 209 210 /****** 下面开始为返回参数 *****/ 211 212 /// <summary> 213 /// [返参]当前页面的查询结果数据集 214 /// </summary> 215 public DataTable dtResult 216 { 217 get 218 { 219 if (_dtResult.Columns.Contains("PageList__RowNum")) 220 { 221 //删除ROW_NUMBER()生成的序号字段 222 _dtResult.Columns.Remove("PageList__RowNum"); 223 } 224 return _dtResult; 225 } 226 set { _dtResult = value; } 227 } 228 229 /// <summary> 230 /// [返参]总记录数 231 /// </summary> 232 public int recCount 233 { 234 get { return _recCount; } 235 set { _recCount = value; } 236 } 237 238 /// <summary> 239 /// [返参]总页数 240 /// </summary> 241 public int pageCount 242 { 243 get { return _pageCount; } 244 set { _pageCount = value; } 245 } 246 247 /// <summary> 248 /// [返参]当前页面执行的sql 249 /// </summary> 250 /// <remarks> 251 /// 1.用于WebGrid批量修改功能中，仅限于单表查询 252 /// 2.自动在最后1列中插入序号列：PageList__RowNum 253 /// </remarks> 254 public string exeSql 255 { 256 get { return _exeSql; } 257 set { _exeSql = value; } 258 } 259 260 /// <summary> 261 /// [返参]不加分页查询的sql（即返回所有数据的sql） 262 /// </summary> 263 /// <remarks>主要用于导出所有数据到excel中</remarks> 264 public string allSql 265 { 266 get 267 { 268 //删除ROW_NUMBER()生成的序号字段 269 _allSql = Regex.Replace(_allSql, @",ROW_NUMBER\(\) OVER\(ORDER BY ([\s\S]*?)\) AS PageList__RowNum", ""); 270 return _allSql; 271 } 272 set { _allSql = value; } 273 } 274 }
