分享一下两种方式的分页代码
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];
}
您可能感兴趣的文章:- asp.net中让Repeater和GridView支持DataPager分页
- Asp.net GridView使用大全(分页实现)
- ASP.NET MVC 5使用X.PagedList.Mvc进行分页教程(PagedList.Mvc)
- asp.net中的GridView分页问题
- asp.net使用AJAX实现无刷新分页
- asp.net gridview分页:第一页 下一页 1 2 3 4 上一页 最末页
- 在ASP.NET 2.0中操作数据之二十:定制数据修改界面
- 在ASP.NET 2.0中操作数据之二十一:实现开放式并发
- 在ASP.NET 2.0中操作数据之二十二:为删除数据添加客户端确认
- 在ASP.NET 2.0中操作数据之二十三:基于用户对修改数据进行限制
- 在ASP.NET 2.0中操作数据之二十四:分页和排序报表数据
- 在ASP.NET 2.0中操作数据之二十五:大数据量时提高分页的效率