asp.net结合aspnetpager使用SQL2005的存储过程分页

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)   


使用中的关键代码： C#

//翻页   
protected void anpager_PageChanged(object sender, EventArgs e)   
{   
        bind();   
}   
string strWhere             = " 1 = 1 ";   
ETHaiNan.BLL.ET_Video bll   = new ETHaiNan.BLL.ET_Video();   
this.anpager.RecordCount    = int.Parse(bll.GetRecordCount(strWhere).Tables[0].Rows[0][0].ToString());   
this.anpager.PageSize       = 10;   
this.anpager.AlwaysShow     = true;   
DataSet ds                  = bll.GetList(anpager.StartRecordIndex, anpager.EndRecordIndex,strWhere, 0);   
this.rpt.DataSource         = ds;   
ds.Dispose();  

数据访问层：

/// <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 = "ET_Video";   
            parameters[3].Value = "VideoID,Video_Name,Video_TypeID,Video_OrderCode,Video_Type,Video_AddDate";   
            parameters[4].Value = "VideoID";   
            parameters[5].Value = IsReCount;   
            parameters[6].Value = 1;   
            parameters[7].Value = strWhere;   
            return DbHelperSQL.RunProcedure("P_GetPagedReCord", parameters, "ds");   
}