asp.net中用DataReader高效率分页

分享一下两种方式的分页代码
1.用datareader分页

/// <summary>
/// pagelist for datareader
/// </summary>
/// <param name="connectionstring"></param>
/// <param name="sql"></param>
/// <param name="pagesize"></param>
/// <param name="curpage"></param>
/// <param name="pagecount"></param>
/// <param name="count"></param>
/// <param name="cmdparms"></param>
/// <returns></returns>
public datatable pagelistreader(string connectionstring, string sql, int pagesize, int curpage, out int pagecount, out int count, params dbparameter[] cmdparms)
{
int first = 0;
int last = 0;
int fieldcount = 0;
using (sqlconnection conn = new sqlconnection(connectionstring))
{
sqlcommand cmd = conn.createcommand();
preparecommand(cmd, conn, null, commandtype.text, sql, cmdparms);
sqldatareader reader = cmd.executereader(commandbehavior.closeconnection);
datatable dt = new datatable();
fieldcount = reader.fieldcount;
for (int i = 0; i < fieldcount; i++)
{
datacolumn col = new datacolumn();
col.columnname = reader.getname(i);
col.datatype = reader.getfieldtype(i);
dt.columns.add(col);
}
count = 0;
first = (curpage - 1) * pagesize+1;
last = curpage * pagesize;
while (reader.read())
{
count++;
if (count >= first && last >= count)
{
datarow r = dt.newrow();
for (int i = 0; i < fieldcount; i++)
{
r[i] = reader[i];
}
dt.rows.add(r);
}
}
reader.close();
pagecount = convert.toint32(math.ceiling((double)count / (double)pagesize));
return dt;
}
}

2.用row_number()分页

/// <summary>
/// 分页获取数据(sql server 2005) for row_number()
/// </summary>
/// <param name="connectionstring">数据库链接</param>
/// <param name="sql">获取数据集的sql</param>
/// <param name="fldsort">排序字段,可以多个</param>
/// <param name="pagesize">每页显示多少条</param>
/// <param name="curpage">当前页码</param>
/// <param name="pagecount">总页数</param>
/// <param name="count">总记录数</param>
///<param name="cmdparms">dbparameter</param>
/// <returns>datatable</returns>
public datatable pagelist(string connectionstring, string sql, string fldsort, int pagesize, int curpage, out int pagecount, out int count, params dbparameter[] cmdparms)
{
stringbuilder strsql = new stringbuilder();
strsql.appendformat(@"select count(0) from {0} as mytablecount;
select * from (
select row_number() over(order by {1}) rownumber,*
from {0} mytable
) mytable2
where rownumber between {2} and {3}"
, sql, fldsort, convert.tostring((curpage - 1) * pagesize + 1), convert.tostring((curpage * pagesize)));
dataset ds = executequery(connectionstring, commandtype.text, strsql.tostring(), cmdparms);
count = convert.toint32(ds.tables[0].rows[0][0]);
pagecount = convert.toint32(math.ceiling((double)count / (double)pagesize));
return ds.tables[1];
}