SQL:分页存储过程(优化了部分语句,附C#调用接口)
网上分页存储过程的代码很多,这个是我一直在用的经典的三层架构里提取出来的,并做了部分语句的优化...
如有更好的欢迎讨论!
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ------------------------------------ --用途:分页存储过程(对有主键的表效率极高) --说明:(优化了部分语句) ------------------------------------ ALTER PROCEDURE [dbo].[TP_GetRecordByPage] @tblName varchar(255), -- 表名 @fldName varchar(255), -- 主键字段名 @PageSize int = 10, -- 页尺寸 @PageIndex int = 1, -- 页码 @IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where) AS declare @strSQL varchar(6000) -- 主语句 declare @strTmp varchar(800) -- 临时变量(查询条件过长时可能会出错,可修改100为1000) declare @strOrder varchar(400) -- 排序类型 if @OrderType != 0 begin set @strTmp = '<(select min' set @strOrder = ' order by [' + @fldName +'] desc' end else begin set @strTmp = '>(select max' set @strOrder = ' order by [' + @fldName +'] asc' end set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)' + @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder if @PageIndex = 1 begin set @strTmp ='' if @strWhere != '' set @strTmp = ' where ' + @strWhere set @strSQL = 'select top ' + str(@PageSize) + ' * from [' + @tblName + ']' + @strTmp + ' ' + @strOrder end if @IsReCount != 0 set @strSQL = 'select count(*) as Total from [' + @tblName + ']'+' where ' + @strWhere exec (@strSQL)
以下是C#调用的接口方法
使用时注意一下命名空间改为你的,需要引用DBUtility操作类。
/// <summary> /// 得到记录总数 /// </summary> /// <param name="tableName">表名</param> /// <returns></returns> public static int GetRecordCount(string tableName) { return GetRecordCount(tableName, ""); } /// <summary> /// 得到记录总数 /// </summary> /// <param name="tableName">表名</param> /// <param name="strWhere">筛选条件(可以不用加where)</param> /// <returns></returns> public static int GetRecordCount(string tableName, string strWhere) { string strsql = string.Empty; if (string.IsNullOrEmpty(strWhere)) strsql = "select count(*) from " + tableName; else strsql = "select count(*) from " + tableName + " where " + strWhere; object obj = TopkeeOA.DBUtility.DbHelperSQL.GetSingle(strsql); if (obj == null) { return 0; } else { return int.Parse(obj.ToString()); } } /// <summary> /// 分页获取数据列表(分页) /// </summary> /// <param name="tableName">表名</param> /// <param name="PageSize">每页显示多少要记录</param> /// <param name="PageIndex">当前页</param> /// <param name="strWhere">筛选条件(可为空)</param> /// <param name="FieldName">排序字段(一般为主键ID)</param> /// <param name="OrderType">排序类型(1为降序,0为升序)</param> /// <returns></returns> public static DataSet GetList(string tableName, int PageSize, int PageIndex, string strWhere, string FieldName, int OrderType) { SqlParameter[] parameters = { new SqlParameter("@tblName", SqlDbType.VarChar, 255), new SqlParameter("@fldName", SqlDbType.VarChar, 255), new SqlParameter("@PageSize", SqlDbType.Int), new SqlParameter("@PageIndex", SqlDbType.Int), new SqlParameter("@IsReCount", SqlDbType.Bit), new SqlParameter("@OrderType", SqlDbType.Bit), new SqlParameter("@strWhere", SqlDbType.VarChar,1000), }; parameters[0].Value = tableName; parameters[1].Value = FieldName; parameters[2].Value = PageSize; parameters[3].Value = PageIndex; parameters[4].Value = 0; parameters[5].Value = OrderType; parameters[6].Value = strWhere; return TopkeeOA.DBUtility.DbHelperSQL.RunProcedure("TP_GetRecordByPage", parameters, "ds"); }
第一次使用请把“ALTER PROCEDURE ”改为“CREATE PROCEDURE ”以便创建一个新的存储过程。
AspNetPager调用示例:
private void DataBinder() { DataList1.DataSource = GetList("Test",AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strWhere, "", 0); DataList1.DataBind(); } protected void AspNetPager1_PageChanged(object src, EventArgs e) { DataBinder(); }
上面的是通用型的比较适用于sql2000,只支持主键排序,需要其它字段的大家可以改一下,下面的这个适用于sql 2005的通用分页排序存储过程,支持多字段排序,可以不一定要求是以主键排序
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER Procedure [dbo].[TP_GetRecordByPage2005] @tableName varchar(50), --表名 @fields varchar(2000) = '*', --字段名(全部字段为*) @orderField varchar(500), --排序字段(必须!支持多字段) @sqlWhere varchar(2000) = Null, --条件语句(不用加where) @pageSize int, --每页显示多少条记录 @pageIndex int = 1 , --指定当前为第几页 @orderType int = 1, --排序方式,非0则降序 @TotalPage int output --返回总页数 as begin Begin Tran --开始事务 Declare @sql nvarchar(4000); Declare @totalRecord int; Declare @sort varchar(10); if @orderType != 0 set @sort = ' DESC' else set @sort = ' ASC' --计算总记录数 if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'select @totalRecord = count(*) from ' + @tableName else set @sql = 'select @totalRecord = count(*) from ' + @tableName + ' where ' + @sqlWhere EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 --计算总页数 select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize) if (@SqlWhere='' or @sqlWhere=NULL) set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderField + @sort + ') as RowNum,' + @fields + ' from ' + @tableName else set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderField + @sort + ') as RowNum,' + @fields + ' from ' + @tableName + ' where ' + @SqlWhere --处理页数超出范围情况 if @PageIndex<=0 Set @pageIndex = 1 if @pageIndex>@TotalPage Set @pageIndex = @TotalPage --处理开始点和结束点 Declare @StartRecord int Declare @EndRecord int set @StartRecord = (@pageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @pageSize - 1 --继续合成sql语句 set @Sql = @Sql + ') as ' + @tableName + ' where RowNum between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) Exec(@Sql) --------------------------------------------------- If @@Error <> 0 Begin RollBack Tran Return -1 End Else Begin Commit Tran Return @totalRecord ---返回记录总数 End end
以下是C#通用调用接口,需要引用DBUtility操作类
#region 公用存储过程分页相关操作 /// <summary> /// 得到记录总数 /// </summary> /// <param name="tableName">表名</param> /// <returns></returns> public static int GetRecordCount(string tableName) { return GetRecordCount(tableName, ""); } /// <summary> /// 得到记录总数 /// </summary> /// <param name="tableName">表名</param> /// <param name="strWhere">筛选条件(可以不用加where)</param> /// <returns></returns> public static int GetRecordCount(string tableName, string strWhere) { string strsql = string.Empty; if (string.IsNullOrEmpty(strWhere)) strsql = "select count(*) from " + tableName; else strsql = "select count(*) from " + tableName + " where " + strWhere; object obj = TK.DBUtility.DbHelperSQL.GetSingle(strsql); if (obj == null) { return 0; } else { return int.Parse(obj.ToString()); } } /// <summary> /// 分页获取数据列表(分页) /// </summary> /// <param name="tableName">表名</param> /// <param name="orderField">排序字段(必须!支持多字段,PS:多个字段用","分割,一般不为空,为空时默认为"ID")</param> /// <param name="sqlWhere">附加条件语句(不用加where),可为空</param> /// <param name="pageSize">每页显示多少条记录</param> /// <param name="pageIndex">指定当前为第几页</param> /// <param name="OrderType">排序方式,非0则降序</param> /// <returns></returns> public static DataSet GetList(string tableName, string orderField, string sqlWhere, int pageSize, int pageIndex, int OrderType) { return GetList(tableName, "*", orderField, sqlWhere, pageSize, pageIndex, OrderType); } /// <summary> /// 分页获取数据列表(分页) /// </summary> /// <param name="tableName">表名</param> /// <param name="fields">字段名(PS:可为空,为空时显示全部字段,显示全部字段为"*")</param> /// <param name="orderField">排序字段(必须!支持多字段,PS:多个字段用","分割,一般不为空,为空时默认为"ID")</param> /// <param name="sqlWhere">附加条件语句(不用加where),可为空</param> /// <param name="pageSize">每页显示多少条记录</param> /// <param name="pageIndex">指定当前为第几页</param> /// <param name="OrderType">排序方式,非0则降序</param> /// <returns></returns> public static DataSet GetList(string tableName, string fields, string orderField, string sqlWhere, int pageSize, int pageIndex, int OrderType) { SqlParameter[] parameters = { new SqlParameter("@tableName", SqlDbType.VarChar, 50), new SqlParameter("@fields", SqlDbType.VarChar, 2000), new SqlParameter("@orderField", SqlDbType.VarChar, 500), new SqlParameter("@sqlWhere", SqlDbType.VarChar,2000), new SqlParameter("@pageSize", SqlDbType.Int), new SqlParameter("@pageIndex", SqlDbType.Int), new SqlParameter("@orderType", SqlDbType.Int), new SqlParameter("@TotalPage", SqlDbType.Int) }; parameters[0].Value = tableName; parameters[1].Value = string.IsNullOrEmpty(fields) ? "*" : fields; parameters[2].Value = string.IsNullOrEmpty(orderField) ? "ID" : orderField; parameters[3].Value = string.IsNullOrEmpty(sqlWhere) ? "" : sqlWhere; parameters[4].Value = pageSize; parameters[5].Value = pageIndex; parameters[6].Value = OrderType; parameters[7].Direction = ParameterDirection.Output; return TK.DBUtility.DbHelperSQL.RunProcedure("TP_GetRecordByPage2005", parameters, "ds"); } #endregion