AspnetPager+sql2005存储过程分页

create  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) 

 

 

    /// <summary>
    /// 获得记录集
    /// </summary>
    /// <param name="start"></param>
    /// <param name="end"></param>
    /// <param name="where"></param>
    /// <returns></returns>
    public DataTable GetdataSet(int start,int end,string where)
    {
        SqlParameter[] parms = { 
                               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)
                               };
        parms[0].Value = start;
        parms[1].Value = end;
        parms[2].Value = "t_user";
        parms[3].Value = "id,user_name,user_age";
        parms[4].Value = "id";
        parms[5].Value = 0;
        parms[6].Value = 0;
        parms[7].Value = where;
        return db.RunProcDataTable("[P_GetPagedReCord]", parms);
    }
    /// <summary>
    /// 取得记录总数
    /// </summary>
    /// <param name="where"></param>
    /// <returns></returns>
    public int GetAllcount(string where )
    {
        SqlParameter[] parms = { 
                               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)
                               };
        parms[0].Value = 0;
        parms[1].Value = 0;
        parms[2].Value = "t_user";
        parms[3].Value = "id,user_name,user_age";
        parms[4].Value = "id";
        parms[5].Value = 1;
        parms[6].Value = 0;
        parms[7].Value = where;
        return Convert.ToInt32(db.RunProcDataTable("[P_GetPagedReCord]", parms).Rows[0][0]) ;
    }

 

 

页面调用：

 

    BLL bll = new BLL();
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            bding("id>10");
        }
    }

    private void bding(string where )
    {
        this.AspNetPager1.PageSize=10;
        this.AspNetPager1.RecordCount = bll.GetAllcount(where);
        DataTable dt= bll.GetdataSet(this.AspNetPager1.StartRecordIndex, this.AspNetPager1.EndRecordIndex, where);
        this.GridView1.DataSource = dt;
        this.GridView1.DataBind();
    }

    protected void AspNetPager1_PageChanged(object sender, EventArgs e)
    {
        bding("id>10");
    }