C/S框架-WebService架构下分页查询数据解决方案

C# Code:

/// <summary>
/// 缓存数据接口
/// </summary>
public interface IMyPageDataCache
{
   /// <summary>
   /// 每页记录数
   /// </summary>
   int PageSize { get; set; }
   
   /// <summary>
   /// 本次查询总页数
   /// </summary>
   int PageCount { get; }
   
   /// <summary>
   /// 当前页号
   /// </summary>
   int CurrentPage { get; }
   
   /// <summary>
   /// 已缓存的页数
   /// </summary>
   int CachedPage { get; }
   
   /// <summary>
   /// 按页号取出缓存数据
   /// </summary>
   /// <param name="pageNo">页号</param>
   /// <returns></returns>
   DataTable GetPageData(int pageNo);
   
   /// <summary>
   /// 初始化缓存数据管理器，第一次搜索时调用此方法。
   /// </summary>
   /// <param name="searchConditions"></param>
   void InitializeSearch(object[] searchConditions);
   
   /// <summary>
   /// 清空缓期数据
   /// </summary>
   void ClearCache();
}


//来源:C/S框架网(www.csframework.com) QQ:1980854898

C# Code:


/// <summary>
/// 支持按页搜索的接口，由具体的BLL层实现, 如:SO,Customer
/// </summary>
public interface ISupportPageSearch
{
   /// <summary>
   /// 搜索指定页面的数据
   /// </summary>
   /// <param name="searchConditions">查询条件(数组)</param>
   /// <param name="pageNo">页号</param>
   /// <param name="pageSize">每页记录数</param>
   /// <returns></returns>
   DataTable SearchByPage(object[] searchConditions, int pageNo, int pageSize);
   
   /// <summary>
   /// 获取本次搜索返回的页数
   /// </summary>
   /// <param name="searchConditions">查询条件(数组)</param>
   /// <returns></returns>
   int GetPageCount(object[] searchConditions, int pageSize);
}

//来源:C/S框架网(www.csframework.com) QQ:1980854898

C# Code:


/// <summary>
/// 数据视图接口
/// </summary>
public interface IMyDataView
{
   /// <summary>
   /// 设置数据源
   /// </summary>
   /// <param name="dataSource">数据源</param>
   void SetDataSource(object dataSource);
}

SQL:
 

ALTER PROCEDURE [dbo].[usp_SearchSO]

  @BeginDate DATETIME,

  @EndDate DATETIME,

  @PageNo INT,

  @PageSize INT,

  @IsGetPageCount CHAR(1) --Y/N

AS

BEGIN

/******************************************************

    说明：按页查询数据

    原创：C/S框架网www.csframework.com

 

    usp_SearchSO '2011-01-01','2011-12-30',2,5,'N'

 

*******************************************************/

    IF @PageNo=0 SET @PageNo=1

    IF @PageSize=0 SET @PageSize=1

    --------------------------------------------------------------------  

 

    IF (@IsGetPageCount='Y')

    BEGIN

       PRINT '取本次查询返回的页数'

 

       DECLARE @ROWS INT,@PAGE_COUNT INT

       SET @PAGE_COUNT=0

       SELECT @ROWS=COUNT(ISID) FROM tb_SO WHERE DocDate BETWEEN @BeginDate AND @EndDate

       IF (@ROWS>0)

       BEGIN

         SET @PAGE_COUNT=@ROWS/@PageSize

         IF (@ROWS % @PageSize)>0 SET @PAGE_COUNT=@PAGE_COUNT+1

       END

       SELECT @PAGE_COUNT AS PAGE_COUNT

       RETURN

    END

 

    -----------------------------------------------------------------------

    DECLARE @SQL NVARCHAR(2000),@WHERE NVARCHAR(100),@PriorPageMaxID INT,@PriorPageRows INT

    --DROP TABLE ##PRIOR_PAGE_MAX_ID  

    CREATE TABLE ##PRIOR_PAGE_MAX_ID(MAX_ID INT)

 

    SET @PriorPageRows=(@PageNo-1)*@PageSize

   

    SET @WHERE=' WHERE CONVERT(VARCHAR,DocDate,112) BETWEEN '+CONVERT(VARCHAR,@BeginDate,112) + ' AND '+CONVERT(VARCHAR,@EndDate,112)

 

    SET @SQL=' INSERT INTO ##PRIOR_PAGE_MAX_ID SELECT MAX(T.ISID) AS MAX_ID FROM (SELECT TOP '+CAST(@PriorPageRows AS VARCHAR)+' ISID FROM tb_SO '+@WHERE+') T '

    PRINT @SQL

    EXEC(@SQL)

 

    SELECT @PriorPageMaxID=ISNULL(MAX_ID,0) FROM ##PRIOR_PAGE_MAX_ID

 

    SET @SQL=' SELECT TOP '+CAST(@PageSize AS VARCHAR)+' * FROM tb_SO '+@WHERE

    SET @SQL=@SQL+' AND ISID>'+CAST(@PriorPageMaxID AS VARCHAR)

 

    -----------------------------------------------------------------------------

    PRINT @SQL

    EXEC(@SQL)

 

    DROP TABLE ##PRIOR_PAGE_MAX_ID 

 

END