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];
}



相关阅读:
精通JavaScript 纠正 cleanWhitespace函数
提高代码性能技巧谈—以创建千行表格为例
改变浏览者的鼠标型状
iis中为每个应用程序池单独设置aspnet.config配置文件
PHP新手上路(九) 投票系统
Oracle教程:讲述表与表见得连接
模拟弹出菜单的代码
win2008 IIS7安装教程
Document和Document.all区别分析
base href 使用方法详解
Linux中QQ软件的安装和配置
SQL 判断给定日期值(或时间段)所在星期的星期一和星期天的日期
使用批处理清除事件查看器所有Windows EventLog日志的代码
css命名不能以数字开头
快速导航

Copyright © 2016 phpStudy | 皖ICP备18014864号-4