欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

asp.net中用DataReader高效率分页

程序员文章站 2024-03-06 19:54:56
分享一下两种方式的分页代码 1.用datareader分页 复制代码 代码如下: /// /// pagelist for dataread...
分享一下两种方式的分页代码
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];
}