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

//SQL2005 分页存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[P_GetPagedReCord]
(@startIndex INT, -- 开始索引号
@endindex INT, -- 结束索引号
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 显示字段名
@OrderfldName varchar(255), -- 排序字段名
@IsReCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType!=0
Begin
set @strOrder='Desc'
End
else
Begin
set @strOrder='Asc'
End
set @strSQL ='WITH orderList AS ( '+
'SELECT ROW_NUMBER() OVER (ORDER BY '+@OrderfldName+' '+@strOrder+')AS Row, '+@fldName+' '+
'from '+@tblName
if @strWhere!=''
set @strSQL = @strSQL+' where ' + @strWhere
set @strSQL=@strSQL+')'+
'SELECT '+@fldName+' '+
'FROM orderlist '+
'WHERE Row between '+str(@startIndex)+' and '+str(@endIndex)+''

if @IsReCount != 0
Begin
set @strSQL = ' select count(1) as Total from [' + @tblName + ']'
if @strWhere!=''
set @strSQL = @strSQL+' where ' + @strWhere
End
--print(@strSQL)
exec (@strSQL)

//获取数据列表

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[P_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 strType = DDListType.SelectedValue;
        string strStation = DDListStation.SelectedValue;
        string strName = DDListName.SelectedItem.Text;
        string strWhere = "1=1 ";
        if (strType != "")
        {
            strWhere += "and type='" + strType + "'";
        }
        if (strStation != "")
        {
            strWhere += "and station='" + strStation + "'";
        }
        if (strAutoName != "")
        {
            strWhere += "and name='" + strName + "'";
        }
        this.AspNetPager1.RecordCount = int.Parse(photoBll.GetListCount(strWhere).Tables[0].Rows[0][0].ToString());
        this.AspNetPager1.PageSize = 50;
        this.AspNetPager1.AlwaysShow = true;
        DataSet ds = photoBll.GetList(AspNetPager1.StartRecordIndex, AspNetPager1.EndRecordIndex, strWhere, 0);
        this.gvPhoto.DataSource = ds;
        gvPhoto.DataBind();
        ds.Dispose();
    }

 

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

 

 // BLL

 

       /// <summary>
       /// 获得数据列表
       /// </summary>
        public DataSet GetListCount(string strwhere)
        {
            return dal.GetListCount(strwhere);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(int startIndex, int endindex, string strWhere, int IsReCount)
        {
            return dal.GetList(startIndex, endindex, strWhere, IsReCount);
        }

 

 //DAL

public DataSet GetListCount(string strWhere)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@strSQL", SqlDbType.VarChar,1000)
            };
            string strSql = "select count(*) from A where "+strWhere +"";
            parameters[0].Value = strSql;
            return DbHelperSQL.RunProcedure("P_GetRecordCount", parameters, "ds");           
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>  
        public DataSet GetList(int startIndex, int endindex, string strWhere, int IsReCount)
        {
            SqlParameter[] parameters = {
            new SqlParameter("@startIndex", SqlDbType.Int),
            new SqlParameter("@endindex", SqlDbType.Int),
            new SqlParameter("@tblName", SqlDbType.VarChar, 255),
            new SqlParameter("@fldName", SqlDbType.VarChar, 255),
            new SqlParameter("@OrderfldName", SqlDbType.VarChar, 255),
            new SqlParameter("@IsReCount", SqlDbType.Bit),
            new SqlParameter("@OrderType", SqlDbType.Bit),
            new SqlParameter("@strWhere", SqlDbType.VarChar,1000)
            };
            parameters[0].Value = startIndex;
            parameters[1].Value = endindex;
            parameters[2].Value = " A ";
            parameters[3].Value = " *";
            parameters[4].Value = "Updated_Date";
            parameters[5].Value = IsReCount;
            parameters[6].Value = 1;
            parameters[7].Value = strWhere;
            return DbHelperSQL.RunProcedure("P_GetPagedReCord", parameters, "ds");
        }