存储过程分页

久没上blog了,根据邹建的分页存储过程写了个返回数据的方法

PROC Code:

/*Sql Code 2004-08-27 */
CREATE PROCEDURE dbo.P_SplitPage  

 @SQL nvarchar(4000),  --要执行的sql语句
 @CurrentPage int,   --要显示的页码
 @PageSize int,   --每页的大小
 @RecordCount int=0 out, --总记录数
 @PageCount int = 0 out  --总页数
 
AS

 SET NoCount ON
 DECLARE @P1 int

 EXEC SP_CursorOpen @P1 output,@SQL,@Scrollopt=1,@ccopt=1,@RowCount=@PageCount OUTPUT

 SELECT @RecordCount=@PageCount,@PageCount=ceiling(1.0*@PageCount/@PageSize)   ,@CurrentPage=(@CurrentPage-1)*@PageSize+1 
 SELECT @RecordCount RecordCount ,@PageCount  PageCount,@CurrentPage  CurrentPage
 EXEC SP_Cursorfetch @P1,16,@CurrentPage,@PageSize
 EXEC SP_CursorClose @P1
GO

建立一个表,如:

Accounts

{ID, AccountName, FullName,Password....}

查询过程 SQL语句:

Select * from Accounts

将该语句创建成存储过程:

/* PROC Code 2004-08-27   */
CREATE PROCEDURE dbo.P_Accounts_Select
 
 @intPage int,
 @intPageSize int,
 @intRecordCount int out,
 @intPageAmount int out
AS

 EXEC P_SplitPage 'Select * From Accounts',@intPage,@intPageSize,@intRecordCount out,@intPageAmount out

GO

通过"查询分析器"  

declare @pageAmount int,@recordcount int
exec p_Accounts_select 2,1,@pageAmount out,@recordcount out

会发现返回的结果集为三个,
第一个结果集为空, 第二个结果集为返回的 数据记录总数, 页码数量

因为SQL不可以同时获取结果集及OUTPUT值, 但又不可能为了获取记录集和页码数量进行两次的查询, 利用C#的DataReader.Nextresult 可以实现. 方法如下:

  /// <summary>
  /// custom Run stored procedure.
  /// </summary>
  /// <param name="procName">Name of stored procedure.</param>
  /// <param name="prams">Stored procedure params.</param>
  /// <param name="dataReader">Return result of procedure.</param>
  /// <param name="NextResult">DataReader NextResult</param>
  /// <param name="RecordCount">DataReader RecordCount</param>
  /// <param name="PageCount">DataReader PageCount</param>
  public void RunProc(string procName, SqlParameter[] prams, out SqlDataReader dataReader, out int RecordCount, out int PageCount)
  {
   int _iRecordCount = 0;
   int _iPageCount = 0;

   SqlCommand cmd = CreateCommand(procName, prams);
   dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

   //Return Next Result, Because Proc Return More Data Result.
   dataReader.NextResult();
   while (dataReader.Read())
   {
    _iRecordCount = (int)dataReader["RecordCount"];
    _iPageCount = (int)dataReader["PageCount"];
    
   }
   RecordCount = _iRecordCount;
   PageCount = _iPageCount;

   dataReader.NextResult();
  }

这样就可以不用DataSet进行数据绑定, 直接用DataReader ,并利用存储过程分页, 快很多.

2004-08-29