针对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>";
        }

相当方便。

文章来自：http://www.cnblogs.com/edobnet/archive/2008/01/23/1049985.html

转载于:https://www.cnblogs.com/xiao_haun/archive/2011/01/23/1942580.html