1 USE [database] 2 GO 3 /****** Object: StoredProcedure [dbo].[SP_Com_SelectByPage] Script Date: 03/03/2014 13:01:19 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROCEDURE [dbo].[SP_Com_SelectByPage] 9 ( 10 @tblName nvarchar(Max), ----要显示的表或多个表的连接 11 @fldName nvarchar(max) = '*', ----要显示的字段列表 12 @pageSize int = 1, ----每页显示的记录个数 13 @page int = 1, ----要显示那一页的记录 14 @fldSort nvarchar(max) = null, ----排序字段列表或条件 15 @Sort bit = 1, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ') 16 @strCondition nvarchar(max) = null, ----查询条件,不需where 17 @ID nvarchar(150), ----主表的主键 18 @Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 19 ) 20 AS 21 SET NOCOUNT ON 22 Declare @sqlTmp nvarchar(max) ----存放动态生成的SQL语句 23 Declare @strTmp nvarchar(max) ----存放取得查询结果总数的查询语句 24 Declare @strID nvarchar(max) ----存放取得查询开头或结尾ID的查询语句 25 Declare @pageCount int ----查询结果分页后的总页数 26 Declare @Counts int ----查询到的记录数 27 Declare @strSortType nvarchar(10) ----数据排序规则A 28 Declare @strFSortType nvarchar(10) ----数据排序规则B 29 30 Declare @SqlSelect nvarchar(max) ----对含有DISTINCT的查询进行SQL构造 31 Declare @SqlSelectCount nvarchar(max) 32 Declare @SqlCounts nvarchar(max) ----对含有DISTINCT的总数查询进行SQL构造 33 Declare @FSort nvarchar(max) 34 Declare @DSort nvarchar(max) 35 set @pageCount=1 36 set @Counts=1 37 38 if @Dist = 0 39 begin 40 set @SqlSelect = 'select ' 41 set @SqlSelectCount = 'select ' 42 set @SqlCounts = 'Count(*)' 43 end 44 else 45 begin 46 set @SqlSelect = 'select distinct ' 47 --set @SqlCounts = 'Count(DISTINCT '+@ID+')' 48 set @SqlSelectCount = 'count(*) from ( ' 49 set @SqlCounts = 'select distinct '+@ID+' from '+@tblName+')as T' 50 end 51 52 53 if @Sort=0 54 begin 55 set @strFSortType=' ASC ' 56 set @strSortType=' DESC ' 57 end 58 else 59 begin 60 set @strFSortType=' DESC ' 61 set @strSortType=' ASC ' 62 end 63 64 if @fldSort IS NOT NULL or @fldSort <>'' 65 begin 66 set @FSort=' order by '+ @fldSort +' '+ @strFSortType 67 set @DSort=' order by '+ @fldSort +' '+ @strSortType 68 end 69 ELSE 70 Begin 71 SET @fldSort='' 72 END 73 74 75 76 77 --------生成查询语句-------- 78 --此处@strTmp为取得查询结果数量的语句 79 --print @strCondition 80 if @strCondition is null or @strCondition='' --没有设置显示条件 81 begin 82 if @Dist = 0 83 begin 84 set @sqlTmp = @fldName + ' From ' + @tblName 85 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName 86 set @strID = ' From ' + @tblName 87 end 88 else 89 begin 90 set @sqlTmp = @fldName + ' From ' + @tblName 91 set @strTmp = @SqlSelect+' @Counts='+@SqlSelectCount+@SqlCounts 92 set @strID = ' From ' + @tblName 93 end 94 end 95 else 96 begin 97 if @Dist =0 98 begin 99 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition100 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition101 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition102 end103 else104 begin105 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition106 set @strTmp = @SqlSelect+' @Counts='+@SqlSelectCount+'select distinct '+@ID+' from '+@tblName+' where (1>0) ' + @strCondition+')as T'107 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition108 end109 end110 --print @strTmp111 ----取得查询结果总数量-----112 exec sp_executesql @strTmp,N'@Counts int out ',@Counts out113 declare @tmpCounts int114 if @Counts = 0115 set @tmpCounts = 1116 else117 set @tmpCounts = @Counts118 119 --取得分页总数120 set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize121 122 /**//**当前页大于总页数 取最后一页**/123 if @page>@pageCount124 set @page=@pageCount125 126 --/*-----数据分页2分处理-------*/127 declare @pageIndex int --总数/页大小128 declare @lastcount int --总数%页大小 129 130 set @pageIndex = @tmpCounts/@pageSize131 set @lastcount = @tmpCounts%@pageSize132 if @lastcount > 0133 set @pageIndex = @pageIndex + 1134 else135 set @lastcount = @pageSize136 137 --//***显示分页138 if @strCondition is null or @strCondition='' --没有设置显示条件139 begin140 --if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理141 --begin 142 set @strTmp= @SqlSelect+' * from ('+@SqlSelect+' '+@fldName+',Row_number() over('+ @FSort+') as IDRank from '+@tblName+')'143 +' AS IDWithRowNumber where IDRank>'+CAST(@pageSize*(@page-1) AS Varchar(20)) +' and IDRank<'+CAST(@pageSize*@page+1 AS Varchar(20))144 --end145 --else146 -- begin147 -- set @page = @pageIndex-@page+1 --后半部分数据处理148 -- if @page <= 1 --最后一页数据显示149 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName150 -- +@DSort+') AS TempTB'+@FSort151 -- else 152 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName153 -- +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName154 -- +@DSort+')'155 -- +@DSort+') AS TempTB'+@FSort156 -- end157 end158 159 else --有查询条件160 begin161 --if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理162 --begin 163 set @strTmp= @SqlSelect+' * from ('+@SqlSelect+' '+@fldName+',Row_number() over('+ @FSort+') as IDRank from '+@tblName+' Where (1>0) '+@strCondition+')'164 +' AS IDWithRowNumber where IDRank>'+CAST(@pageSize*(@page-1) as Varchar(20))+' and IDRank<'+CAST(@pageSize*@page+1 as Varchar(20)) 165 --end166 --else167 --begin 168 -- set @page = @pageIndex-@page+1 --后半部分数据处理169 -- if @page <= 1 --最后一页数据显示170 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName171 -- +' where (1>0) '+ @strCondition +@DSort+') AS TempTB'+@FSort172 -- else173 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName174 -- +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName175 -- +' where (1>0) '+ @strCondition +@DSort+')'176 -- + @strCondition +@DSort+') AS TempTB'+@FSort 177 --end 178 end179 --print @strTmp180 ------返回查询结果-----181 182 exec sp_executesql @strTmp183 SELECT @pageCount AS PageCount,@Counts AS Counts184 --print @strTmp185 SET NOCOUNT OFF 相应的类和方法
public class PageResultDTO { public PageResultDTO(); public int Counts { get; set; } public int PageCount { get; set; } public DataTable Result { get; set; } } public PageResultDTO GetPagingData(PageSearchDTO page) { SqlParameter[] sqlPar = new SqlParameter[] { new SqlParameter("@tblName",page.TblName), new SqlParameter("@fldName",page.FieldName), new SqlParameter("@pageSize",page.PageSize), new SqlParameter("@page",page.Page), new SqlParameter("@fldSort",page.FieldSort), new SqlParameter("@Sort",page.Sort), new SqlParameter("@strCondition",page.Condition), new SqlParameter("@ID",page.ID), new SqlParameter("@Dist",page.IsDistint), }; DataSet ds_result = SqlHelper.ExecuteStoreProcedureDataSet(spName.GetPagingData, sqlPar); PageResultDTO pageresult = new PageResultDTO(); if (ds_result != null) { pageresult.Result = ds_result.Tables[0]; pageresult.PageCount = Convert.ToInt32(ds_result.Tables[1].Rows[0]["PageCount"]); pageresult.Counts = Convert.ToInt32(ds_result.Tables[1].Rows[0]["Counts"]); } return pageresult; }