针对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 --是否获得总记录数

--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 '+@Tables+') AS D

WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort

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 = "记录总数：" + AspNetPager1.RecordCount.ToString() + "";

AspNetPager1.CustomInfoText += " 总页数：" + AspNetPager1.PageCount.ToString() + "";

AspNetPager1.CustomInfoText += " 当前页：" + AspNetPager1.CurrentPageIndex.ToString() + "";

}

相当方便。

Tag标签: 针对sql 2005优化的高性能分页存储过程

posted on 2008-01-23 13:41 edobnet 阅读(4281) 评论(50) 编辑收藏所属分类: .Net