存储过程分页

最新推荐文章于 2024-09-05 18:50:50 发布

kangsoft

最新推荐文章于 2024-09-05 18:50:50 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： MicroSoft.NET/XML/WebServices C#/ADO.NET/SQL 文章标签：存储 sql output dataset string go

本文链接：https://blog.youkuaiyun.com/kangsoft/article/details/88099

C#/ADO.NET/SQL 同时被 2 个专栏收录

6 篇文章

订阅专栏

MicroSoft.NET/XML/WebServices

4 篇文章

订阅专栏

久没上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