AspNetPager1+SQL2000 分页存储过程(asp.net2.0)

//SQL2000 分布储存过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[futures_GetPagedReCord]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@fldName varchar(255)='', -- 排序的字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@doCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere nvarchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL nvarchar(4000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型

if @doCount != 0 --如果@doCount传递过来的不是0，就执行总数统计
begin
if @strWhere !=''
set @strSQL = 'SELECT COUNT(*) AS Total FROM ' + @tblName + ' WHERE ' + @strWhere
else
set @strSQL = 'SELECT COUNT(*) AS Total FROM ' + @tblName
end

else

begin

if @OrderType != 0

begin
set @strTmp = '<(SELECT MIN'
set @strOrder = ' ORDER BY [' + @fldName +'] DESC'

--如果@OrderType不是0，就执行降序
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

--如果是第一页就执行以上代码，这样会加快执行速度
end

else

begin

--以下代码赋予了@strSQL以真正执行的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)

// 获取数据列表

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[futures_GetRecordCount]
@strSQL varchar(1000)
AS
begin
select @strSQL=@strSQL
end
exec (@strSQL)

//aspx.cs

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
             InitDataBind();

        }
    }

void InitDataBind()
    {
        string strBread = DDListBreed.SelectedItem.Text;
        string strName = DDListName.SelectedItem.Text;

        string strWhere = "1=1";
        if (strBread!= "")
        {
            strWhere += " and breed='" + strBread + "'";
        }
        if (strName != "")
        {
            strWhere += " and marketname='" + strName + "'";
        }

        this.AspNetPager1.RecordCount = int.Parse(breedBll.GetListCount(strWhere).Tables[0].Rows[0][0].ToString());       
        DataSet ds = breedBll.GetList(this.AspNetPager1.PageSize, this.AspNetPager1.CurrentPageIndex, strWhere, "1");
        this.breedGV.DataSource = ds;
        this.breedGV.DataBind();
        ds.Dispose();

    }

protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        InitDataBind();
    }

//BLL

public DataSet GetListCount(string strWhere)
        {
            return dal.GetListCount(strWhere);
        }
  
        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string OrderType)
        {
            return dal.GetList(PageSize, PageIndex, strWhere, OrderType);
        }

//DAL

     　/// <summary>
        /// 计算数据总数
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataSet GetListCount(string strWhere)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@strSQL", SqlDbType.VarChar,1000)
            };
            string strSql = "select count(*) from A";
            if (strWhere != "")
            {
                strSql += " where " + strWhere + "";
            }
            parameters[0].Value = strSql;
            return DbHelperSQL.RunProcedure("futures_GetRecordCount", parameters, "ds");
        }

        /// <summary>
  　　/// 获得数据列表
  　　/// </summary>
        public DataSet GetList(int PageSize, int PageIndex, string strWhere, string OrderType)
        {
            SqlParameter[] parameters = {
            new SqlParameter("@tblName", SqlDbType.VarChar, 255),
            new SqlParameter("@fldName", SqlDbType.VarChar, 255),
            new SqlParameter("@PageSize", SqlDbType.Int),
            new SqlParameter("@PageIndex", SqlDbType.Int),
            new SqlParameter("@doCount", SqlDbType.Bit),
            new SqlParameter("@OrderType", SqlDbType.Bit),
            new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
            };
            parameters[0].Value = "A";
            parameters[1].Value = "updatedate";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = int.Parse(OrderType);
            parameters[6].Value = strWhere;
            return DbHelperSQL.RunProcedure("futures_GetPagedReCord", parameters, "ds");
        }