針對sql 2005優化的高性能分頁存儲過程

存儲過程代碼如下：

ALTER PROCEDURE [dbo].[Pg_Paging]
@Tables varchar(1000), --表名,多紅表是請使用 tA a inner join tB b On a.AID = b.AID
@PK varchar(100),    --主鍵，可以帶表頭 a.AID
@Sort varchar(200) = '', --排序字段
@PageNumber int = 1,    --開始頁碼
@PageSize int = 10,        --頁大小
@FIElds varchar(1000) = '*',--讀取字段
@Filter varchar(1000) = NULL,--Where條件
@Group varchar(1000) = NULL,  --分組
@isCount bit = 0     --1    --是否獲得總記錄數
AS
--
--select * from GL_NEWS order by GN_UPDATE_DATE DESC
--exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@FIElds = '*', @Group = '', @isCount = 0
DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
IF @Filter IS NOT NULL AND @Filter != ''
  BEGIN
   SET @strFilter = ' WHERE ' + @Filter + ' '
  END
ELSE
  BEGIN
   SET @strFilter = ''
  END

if @isCount = 1 --只獲得記錄條數
    begin
        set @sql = 'SELECT  Count(*) FROM ' + @Tables + @strFilter  
    end
else
begin
if @Sort = ''
  set @Sort = @PK + ' DESC '

IF @PageNumber < 1
  SET @PageNumber = 1

if @PageNumber = 1 --第一頁提高性能
begin 
  set @sql = 'select top ' + str(@PageSize) +' '+@FIElds+ '  from ' + @Tables + ' ' + @strFilter + ' ORDER BY  '+ @Sort
end 
else
  begin
  /**//**//**//*Execute dynamic query*/    
   DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
    set @sql =  ' SELECT '+@FIElds+ '
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
     '+@FIElds+ '
      FROM ' ' +@strFilter++@Tables+'') AS D
   WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
  END

END
--print @sql

EXEC(@sql)

可以通過封裝一個靜態函數來執行：（EnterpriseLibrary3。1）

 

using System;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.XML;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
namespace Glenet.EjiaShop.SqlData
{
    /// <summary>
    ///  Pageing
    /// </summary>
    public class Pageing
    {
        public Pageing()
        {
            //
            // TODO: 在此處添加構造函數邏輯
            //
        }

        #region Pg_Paging
        /// <summary>
        /// Pg_Paging
        /// </summary>
        /// <param name="Tables"></param>
        /// <param name="PK"></param>
        /// <param name="Filter"></param>
        /// <returns></returns>
        public static int Pg_PageCount(string Tables,string PK,string Filter)
        {
            //創建數據庫實例
            Database db = DatabaseFactory.CreateDatabase();
            //獲得命令
            string sqlCommand = "Pg_Paging";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            //設置參數
            db.AddInParameter(dbCommand, "Tables", DbType.String, Tables);
            db.AddInParameter(dbCommand, "PK", DbType.String, PK);
            db.AddInParameter(dbCommand, "Sort", DbType.String, "");
            db.AddInParameter(dbCommand, "PageNumber", DbType.Double, 0);
            db.AddInParameter(dbCommand, "PageSize", DbType.Double, 0);
            db.AddInParameter(dbCommand, "FIElds", DbType.String, "*");
            db.AddInParameter(dbCommand, "Filter", DbType.String, Filter);
            db.AddInParameter(dbCommand, "Group", DbType.String, "");
            db.AddInParameter(dbCommand, "isCount", DbType.Boolean, 1);

            //執行
            return int.Parse(db.ExecuteScalar(dbCommand).ToString());    
            //取得輸出參數


        }
        #endregion

        #region Pg_Paging
        /// <summary>
        /// Pg_Paging
        /// </summary>
        /// <param name="Tables"></param>
        /// <param name="PK"></param>
        /// <param name="Sort"></param>
        /// <param name="PageNumber"></param>
        /// <param name="PageSize"></param>
        /// <param name="FIElds"></param>
        /// <param name="Filter"></param>
        /// <param name="Group"></param>
        /// <returns></returns>
        public static DataSet Pg_Paging(string Tables,string PK,string Sort,int PageNumber,int PageSize,string FIElds,string Filter,string Group)
        {
            //創建數據庫實例
            Database db = DatabaseFactory.CreateDatabase();
            //獲得命令
            string sqlCommand = "Pg_Paging";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            //設置參數            
            db.AddInParameter(dbCommand, "Tables", DbType.String, Tables);
            db.AddInParameter(dbCommand, "PK", DbType.String, PK);
            db.AddInParameter(dbCommand, "Sort", DbType.String, Sort);
            db.AddInParameter(dbCommand, "PageNumber", DbType.Double, PageNumber);
            db.AddInParameter(dbCommand, "PageSize", DbType.Double, PageSize);
            db.AddInParameter(dbCommand, "Fields", DbType.String, FIElds);
            db.AddInParameter(dbCommand, "Filter", DbType.String, Filter);
            db.AddInParameter(dbCommand, "Group", DbType.String, Group);
            db.AddInParameter(dbCommand, "isCount", DbType.Boolean, 0);

            //執行
            return db.ExecuteDataSet(dbCommand);    
        }
        #endregion


    }

}

前台：調用如下：
 

string filter = "  1 = 1 ";

        ASPNetPager1.RecordCount = Glenet.EjiaShop.SqlData.Pageing.Pg_PageCount("tb_NewsInfo", "News_ID", filter);
        using (DataSet ds = Glenet.EjiaShop.SqlData.Pageing.Pg_Paging("tb_NewsInfo", "News_ID", "News_AddTime DESC", AspNetPager1.CurrentPageIndex, ASPNetPager1.PageSize, "*", filter, ""))
        {
            this.Re_ContentList.DataSource = ds.Tables[0].DefaultVIEw;
            this.Re_ContentList.DataBind();

            ASPNetPager1.CustomInfoText = "記錄總數：<font color=\"#00007f\"><b>" + ASPNetPager1.RecordCount.ToString() + "</b></font>";
            ASPNetPager1.CustomInfoText += " 總頁數：<font color=\"#00007f\"><b>" + ASPNetPager1.PageCount.ToString() + "</b></font>";
            ASPNetPager1.CustomInfoText += " 當前頁：<font color=\"red\"><b>" + ASPNetPager1.CurrentPageIndex.ToString() + "</b></font>";
        }