存储过程代码如下:
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 ' + @Tables + ' ) AS D
WHERE rownum BETWEEN ' + @START_ID + ' AND ' + @END_ID + ' ORDER BY ' + @Sort
END
END
-- print @sql
EXEC ( @sql )
@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 ' + @Tables + ' ) AS D
WHERE rownum BETWEEN ' + @START_ID + ' AND ' + @END_ID + ' ORDER BY ' + @Sort
END
END
-- print @sql
EXEC ( @sql )
可以通过封装一个静态函数来执行:(EnterpriseLibrary 3。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
}
}
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> " ;
}
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> " ;
}
相当方便。