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");
}