C#中常用的分页存储过程小结

<br>SELECT TOP 10 * <br>FROM TestTable <br>WHERE (ID NOT IN <br>(SELECT TOP 20 id <br>FROM TestTable <br>ORDER BY id)) <br>ORDER BY ID <br><br><br>SELECT TOP 页大小 * <br>FROM TestTable <br>WHERE (ID NOT IN <br>(SELECT TOP 页大小*页数 id <br>FROM 表 <br>ORDER BY id)) <br>ORDER BY ID <br> <br>2.分页方案二：(利用ID大于多少和SELECT TOP分页） <br>语句形式： <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>SELECT TOP 10 * <br>FROM TestTable <br>WHERE (ID > <br>(SELECT MAX(id) <br>FROM (SELECT TOP 20 id <br>FROM TestTable <br>ORDER BY id) AS T)) <br>ORDER BY ID <br><br><br>SELECT TOP 页大小 * <br>FROM TestTable <br>WHERE (ID > <br>(SELECT MAX(id) <br>FROM (SELECT TOP 页大小*页数 id <br>FROM 表 <br>ORDER BY id) AS T)) <br>ORDER BY ID <br> <br>3.分页方案三：(利用SQL的游标存储过程分页) <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>create procedure SqlPager <br>@sqlstr nvarchar(4000), --查询字符串 <br>@currentpage int, --第N页 <br>@pagesize int --每页行数 <br>as <br>set nocount on <br>declare @P1 int, --P1是游标的id <br>@rowcount int <br>exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output <br>select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页 <br>set @currentpage=(@currentpage-1)*@pagesize+1 <br>exec sp_cursorfetch @P1,16,@currentpage,@pagesize <br>exec sp_cursorclose @P1 <br>set nocount off <br> <br>4. <br><span><u></u></span> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>CREATE Procedure FramWorkPage <br>@TableName varchar(50), --表名 <br>@Fields varchar(5000) = '*', --字段名(全部字段为*) <br>@OrderField varchar(5000), --排序字段(必须!支持多字段) <br>@sqlWhere varchar(5000) = Null,--条件语句(不用加where) <br>@pageSize int, --每页多少条记录 <br>@pageIndex int = 1 , --指定当前为第几页 <br>@TotalPage int output, --返回条数 <br>@OrderType bit -- 设置排序类型,1 升序 0 值则降序 <br>as <br>begin <br>declare @strOrder varchar(400) -- 排序类型 <br><br>Begin Tran --开始事务 <br>Declare @sql nvarchar(4000); <br>Declare @totalRecord int; <br>--计算总记录数 <br>if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL) <br>set @sql = 'select @totalRecord = count(*) from ' + @TableName <br>else <br>set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere <br>EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数 <br><br>--计算总页数 <br><br>select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize) <br><br>if @OrderType = 0 <br>begin <br>set @strOrder = ' order by [' + @OrderField +'] desc' <br>--如果@OrderType是0，就执行降序，这句很重要！ <br>end <br>else <br>begin <br>set @strOrder = ' order by [' + @OrderField +'] asc' <br>end <br><br>if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL) <br>set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName <br>else <br>set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere <br>--处理页数超出范围情况 <br>if @PageIndex<=0 <br>Set @pageIndex = 1 <br><br>if @pageIndex>@TotalPage <br>Set @pageIndex = @TotalPage <br><br>--处理开始点和结束点 <br>Declare @StartRecord int <br>Declare @EndRecord int <br><br>set @StartRecord = (@pageIndex-1)*@PageSize + 1 <br>set @EndRecord = @StartRecord + @pageSize - 1 <br><br>if @OrderType = 0 <br>begin <br>set @strOrder = ' order by rowid desc' <br>--如果@OrderType是0，就执行降序，这句很重要！ <br>end <br>else <br>begin <br>set @strOrder = ' order by rowid asc' <br>end <br>--继续合成sql语句 <br>set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) + ' '+@strOrder <br>-- print @sql <br>Exec(@Sql) <br>--------------------------------------------------- <br>If @@Error <> 0 <br>Begin <br>RollBack Tran <br>Return -1 <br>End <br>Else <br>Begin <br>Commit Tran <br>Return @totalRecord ---返回记录总数 <br>End <br>end <br> <br>通过上面这些东西的学习。我想一定可以找到一种满意的答案。。。</li><li>            </li><li>                                </li></ol></pre></li></ol></pre></li></ol></pre>