--阿會楠根據(jù)網(wǎng)絡(luò)上的代碼進(jìn)行了修改，版權(quán)歸原作者所有2009-4-5
--修改輸出總記錄數(shù)


create PROCEDURE [dbo].[pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000), -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 頁尺寸
@PageIndex int, -- 頁碼
@OrderType bit, -- 設(shè)置排序類型, 非 0 值則降序
@strWhere varchar(1500), -- 查詢條件 (注意: 不要加 where)
@rowCount int output --查詢到的記錄數(shù)
AS

declare @strSQL varchar(5000) -- 主語句
declare @strTmp varchar(110) -- 臨時變量
declare @strOrder varchar(400) -- 排序類型
declare @strCountTmp nvarchar(100) --記錄數(shù)
declare @timediff datetime --耗時

begin
select @timediff = Getdate()
if @strWhere !=''
set @strCountTmp = 'select @rowCount = count(*) from [' + @tblName + '] where '+@strWhere
else
set @strCountTmp = 'select @rowCount = rowCount_tmp from tmp where (table_tmp = ''' + @tblName +''')'

exec sp_executesql @strCountTmp,N'@rowCount int out',@rowCount out --輸出總記錄數(shù)

if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @fldName +' desc'
--如果@OrderType不是0，就執(zhí)行降序，這句很重要！
end

else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @fldName +' asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
--如果是第一頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
end

else
begin
--以下代碼賦予了@strSQL以真正執(zhí)行的SQL代碼
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end

end
exec (@strSQL)
select datediff(ms,@timediff,GetDate()) as runtime

USE [data_Test]
GO

DECLARE @return_value int,
@rowCount int

EXEC @return_value = [dbo].[pagination]
@tblName = 'userinfo',
@strGetFields = N'id',
@fldName = N'id',
@PageSize = 10,
@PageIndex = 300,
@OrderType = 0,
@strWhere = null,
@rowCount = @rowCount OUTPUT

SELECT @rowCount as N'@rowCount'

SELECT 'Return Value' = @return_value

GO

public DataSet GetList(int PageIndex, string strWhere,ref int rowCount)
{
SqlParameter[] parameter = sosuo8.DBUtility.DbHelperSQL.pagePara();

parameter[0].Value = "userinfo";
parameter[1].Value = "id,userName,userWebName,createDate";
parameter[2].Value = "id";
parameter[3].Value = 10;
parameter[4].Value = PageIndex;
parameter[5].Value = 0;
parameter[6].Value = strWhere;
parameter[7].Direction = ParameterDirection.Output;//聲明為輸出類型

DataSet ds = sosuo8.DBUtility.DbHelperSQL.RunProcedure("pagination",parameter,"userinfo");
rowCount = Convert.ToInt32(parameter[7].Value);
return ds;

}

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
bind();
}
}


private void bind()//綁定數(shù)據(jù)
{
this.anPager.PageSize = 10;//每頁記錄數(shù)
this.anPager.AlwaysShow = true;//是否一直顯示分頁
int rowCount = 0;//初始化記錄數(shù)為0
string wherestr = string.Empty;//搜索關(guān)鍵字，這部分后面我在講講優(yōu)化
sosuo8.BLL.bll_test bll = new sosuo8.BLL.bll_test();
this.rpt.DataSource = bll.GetList(this.anPager.CurrentPageIndex,wherestr,ref rowCount);//rowCount在經(jīng)過這個函數(shù)后，返回的是重新賦值的記錄總數(shù)
this.anPager.RecordCount = rowCount;
this.rpt.DataBind();
}

protected void anPager_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
this.anPager.CurrentPageIndex = e.NewPageIndex;
bind();
}
}

<%@ OutputCache Duration="360" VaryByParam="page" %>