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)

使用中的关键代码： 
复制代码 代码如下:
//翻页 
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"); 
}