同事那的一个成熟 多字段 排序 分页 存储过程
sql中调用 :
exec CMS_SearchGetDataByPage 'tblschool','id,IsEmp,IsNom','IsEmp , IsNom','IsEmp desc, IsNom desc','id',10,1,''
c# 中调用:
- /// <summary>
- /// 分页获取数据列表2
- /// </summary>
- /// <param name="tableName">表名</param>
- /// <param name="fldNames">选择字段列表,以,分隔</param>
- /// <param name="selectOrderFldName">排序字段列表,以,分隔(不能含keyFldName指定的字段,可为空)</param>
- /// <param name="orderFldDesc">排序字段及方向,如addDate desc,id desc(排序字段需通过selectOrderFldName指
- 定,可为空)</param>
- /// <param name="keyFldName">主键字段(没有主键请指定不重复的字段)</param>
- /// <param name="PageSize">每页记录数</param>
- /// <param name="PageIndex">当前页</param>
- /// <param name="strWhere">SQL条件</param>
- /// <returns>数据列表</returns>
- public static DataSet GetList(string tableName, string fldNames, string selectOrderFldName, string
- orderFldDesc, string keyFldName, int PageSize, int PageIndex, string strWhere)
- {
- SqlParameter[] parameters = {
- new SqlParameter("@tblName", SqlDbType.VarChar, 255),
- new SqlParameter("@fldNames", SqlDbType.VarChar, 1000),
- new SqlParameter("@selectOrderFldName", SqlDbType.VarChar, 500),
- new SqlParameter("@orderFldDesc", SqlDbType.VarChar, 500),
- new SqlParameter("@keyFldName", SqlDbType.VarChar, 255),
- new SqlParameter("@PageSize", SqlDbType.Int),
- new SqlParameter("@PageIndex", SqlDbType.Int),
- new SqlParameter("@strWhere", SqlDbType.VarChar,1000)
- };
- parameters[0].Value = tableName;
- parameters[1].Value = fldNames;
- parameters[2].Value = selectOrderFldName;
- parameters[3].Value = orderFldDesc;
- parameters[4].Value = keyFldName;
- parameters[5].Value = PageSize;
- parameters[6].Value = PageIndex;
- parameters[7].Value = strWhere;
- return DbHelperSQL.RunProcedure("CMS_SearchGetDataByPage", parameters, "ds");
- }
存储过程本身:
- create PROCEDURE CMS_SearchGetDataByPage
- @tblName varchar(255), -- 表名
- @fldNames varchar(1000), -- 选择的字段列表以,分隔
- @selectOrderFldName varchar(500), -- 排序字段以,分隔(不能含keyFldName指定的字段,可为空)
- @orderFldDesc varchar(500), -- 排序字段及排序方向,如addDate desc,id desc(排序字段需通过selectOrderFldName指定,可为空)
- @keyFldName varchar(255), -- 主键字段
- @PageSize int = 10, -- 页尺寸
- @PageIndex int = 1, -- 页码
- @strWhere varchar(1000) = '' -- 查询条件(注意: 不要加where)
- AS
- declare @strWhereA varchar(1200) -- 临时变量,给sqlwhere加where
- declare @strOrderA varchar(2000) -- 第一次排序类型
- declare @strOrderB varchar(2000) -- 第二次排序类型
- declare @strSqlA varchar(4000) -- 第一次选出
- declare @strSqlB varchar(8000) -- 第二次选出
- declare @strSQL varchar(8000) -- 最后选出
- /* 条件*/
- if @strWhere != ''
- set @strWhereA = ' where ' + @strWhere
- else
- set @strWhereA = ''
- /* 选择字段列表*/
- if @fldNames is null or rtrim(@fldNames) = ''
- set @fldNames = '*'
- /* 排序字段列表*/
- if not(@selectOrderFldName is null or rtrim(@selectOrderFldName) = '')
- if rtrim(@selectOrderFldName) = 'id'
- set @selectOrderFldName = ''
- else
- set @selectOrderFldName = ',' + @selectOrderFldName
- /* 构建order,按指定方式排序*/
- if @orderFldDesc is null or rtrim(@orderFldDesc) = ''
- set @orderFldDesc = ' order by id desc'
- else
- set @orderFldDesc = ' order by ' + @orderFldDesc
- set @strOrderA = UPPER(@orderFldDesc)
- set @strOrderB = replace(@strOrderA,'DESC','DESC1')
- set @strOrderB = replace(@strOrderB,'ASC','DESC')
- set @strOrderB = replace(@strOrderB,'DESC1','ASC')
- /* 第一页*/
- if @PageIndex = 1
- set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldNames + ' from [' + @tblName + '] with(nolock)' + @strWhereA + ' ' + @strOrderA
- else
- begin
- --取得总记录数
- declare @sql nvarchar(500)
- declare @maxCount int
- declare @maxPage int
- declare @tempRowCount int
- set @sql ='select @maxCount = count('+@keyFldName+') from [' + @tblName + ']' + @strWhereA
- exec sp_executesql @sql,N'@maxCount int output',@maxCount output
- set @maxPage = @maxCount / @PageSize
- if(@maxCount % @PageSize > 0)
- set @maxPage = @maxPage + 1
- /* 最后一页*/
- if @PageIndex >= @maxPage
- begin
- set @PageIndex = @maxPage
- set @strSqlA = char(13) + '(select top '+str(@maxCount % @PageSize)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + ' )' + char(13)
- set @strSqlB = char(13) + '(select ' + @keyFldName +' from ' + @strSqlA + ' as b )' + char(13)
- set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA + char(13)
- end
- else
- begin
- /* 不是第一页,也不是最后一页*/
- if(@PageIndex <= @maxPage / 2)
- begin
- --前半数的页
- set @tempRowCount = @PageIndex * @PageSize
- /* 构建SQL,本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
- /* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
- set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderA + ' )' + char(13)
- /* 2、再从选出的记录中按升序选出perPage条记录*/
- set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA + ' as b ' + @strOrderB + ' )' + char(13)
- /* 3、从数据库中选出主键在第二次选出的记录中的记录,按降序排列,分页完成*/
- set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
- end
- else
- begin
- --后半数的页
- set @tempRowCount = @maxCount - (@PageIndex -1) * @PageSize
- /* 构建SQL,本分页算法的目的是为了实现高效的非主键排序的分页。by tony */
- /* 1、先按指定字段+主键字段按降序选出perPage*pageNum条记录*/
- set @strSqlA = char(13) + '(select top '+str(@tempRowCount)+' ' + @keyFldName + @selectOrderFldName + ' from [' + @tblName + '] as a with(nolock) ' + @strWhereA + @strOrderB + ' )' + char(13)
- /* 2、再从选出的记录中按升序选出perPage条记录*/
- set @strSqlB = char(13) + '(select top '+str(@PageSize)+' ' + @keyFldName + ' from ' + @strSqlA + ' as b ' + @strOrderA + ' )' + char(13)
- /* 3、从数据库中选出主键在第二次选出的记录中的记录,按降序排列,分页完成*/
- set @strSQL = 'select ' + @fldNames + ' from [' + @tblName + '] where ([' + @keyFldName + '] in '+@strSqlB+')' + @strOrderA
- end
- end
- end
- set nocount on
- /*print @strSQL*/ --显示SQL
- exec (@strSQL)
- set nocount off
- RETURN