asp.net中用DataReader高效率分页
更新时间:2021年9月22日 10:19 点击:1615
分享一下两种方式的分页代码
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];
}
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];
}
相关文章
- 今天小编就为大家分享一篇关于C#使用SQL DataReader访问数据的优点和实例,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...2020-06-25
详细说明asp.net中datareader 和 dataset 的区别
asp.net中datareader 和 dataset的区别主要是在获取数据的机制和获取数据的方式方面,下面我们来看教程...2021-09-22C# OleDbDataReader快速数据读取方式(3种)
这篇文章主要介绍了C# OleDbDataReader快速数据读取方式(3种),文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-06-25- 这篇文章主要为大家详细介绍了SqlDataReader指定转换无效的解决方法,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2021-09-22
c#中SqlHelper封装SqlDataReader的方法
这篇文章主要介绍了c#中SqlHelper封装SqlDataReader的方法,涉及C#针对数据库相关操作封装与使用的技巧,需要的朋友可以参考下...2020-06-25DataReader、DataSet、DataAdapter和DataView使用介绍
ADO.NET提供两个对象用于检索关系型数据并把它存储在内存中,分别是DataSet和DataReader,本文将详细介绍这几个对象的应用,有需求的朋友可以了解下...2020-06-25- 这篇文章主要介绍了DataReader不能使用using的详细示例,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2021-09-22
已有打开的与此命令相关联的DataReader,必须首先将它关闭。对于此异常的理解
今天与大家分享一下 已有打开的与此命令相关联的DataReader,必须首先将它关闭。这个异常的个人理解...2021-09-22- 自从用Sql2005版本以后一直用ROW_NUMBER()分页,最近一个项目维护sqlserver是2000,没办法重写了分页。写完测试分析比ROW_NUMBER()明显快啊...2021-09-22
- 这篇文章主要介绍了SqlDataReader生成动态Lambda表达式,需要的朋友可以参考下...2021-09-22