SQL Server 、access数据库 top分页方法

这篇文章主要为大家详细介绍了SQL Server 、access数据库 top分页方法，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

但是有利也有弊，它要求排序字段必须构成唯一记录，且select后的字段列表中，不允许出现与排序字段同名的字段。虽然sql2k5及以上版本已经提供了rownum()来进行分页处理，但是使用sql2k进行开发的还是较多的

代码如下:

uusing system.collections.generic;
sing system;
using system.text;
/// <summary>
/// 构造分页后的sql语句
/// </summary>
public static class paginghelper
{
 /// <summary>
 /// 获取分页sql语句，排序字段需要构成唯一记录
 /// </summary>
 /// <param name="_recordcount">记录总数</param>
 /// <param name="_pagesize">每页记录数</param>
 /// <param name="_pageindex">当前页数</param>
 /// <param name="_safesql">sql查询语句</param>
 /// <param name="_orderfield">排序字段，多个则用“,”隔开</param>
 /// <returns>分页sql语句</returns>
 public static string createpagingsql(int _recordcount, int _pagesize, int _pageindex, string _safesql, string _orderfield)
 {
 //重新组合排序字段，防止有错误
 string[] arrstrorders = _orderfield.split(new char[] { ',' }, stringsplitoptions.removeemptyentries);
 stringbuilder sboriginalorder = new stringbuilder(); //原排序字段
 stringbuilder sbreverseo教程rder = new stringbuilder(); //与原排序字段相反，用于分页
 for (int i = 0; i < arrstrorders.length; i++)
 {
 arrstrorders[i] = arrstrorders[i].trim(); //去除前后空格
 if (i != 0)
 {
 sboriginalorder.append(", ");
 sbreverseorder.append(", ");
 }
 sboriginalorder.append(arrstrorders[i]);

int index = arrstrorders[i].indexof(" "); //判断是否有升降标识 if (index > 0) { //替换升降标识，分页所需 bool flag = arrstrorders[i].indexof(" desc", stringcomparison.ordinalignorecase) != -1; sbreverseorder.appendformat("{0} {1}", arrstrorders[i].remove(index), flag ? "asc" : "desc"); } else { sbreverseorder.appendformat("{0} desc", arrstrorders[i]); } }

//计算总页数 _pagesize = _pagesize == 0 ? _recordcount : _pagesize; int pagecount = (_recordcount + _pagesize - 1) / _pagesize;

//检查当前页数 if (_pageindex < 1) { _pageindex = 1; } else if (_pageindex > pagecount) { _pageindex = pagecount; }

stringbuilder sbsql = new stringbuilder(); //第一页时，直接使用top n，而不进行分页查询 if (_pageindex == 1) { sbsql.appendformat(" select top {0} * ", _pagesize); sbsql.appendformat(" from ({0}) as t ", _safesql); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring()); } //最后一页时，减少一个top n else if (_pageindex == pagecount) { sbsql.append(" select * from "); sbsql.append(" ( "); sbsql.appendformat(" select top {0} * ", _recordcount - _pagesize * (_pageindex - 1)); sbsql.appendformat(" from ({0}) as t ", _safesql); sbsql.appendformat(" order by {0} ", sbreverseorder.tostring()); sbsql.append(" ) as t "); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring()); } //前半页数时的分页 else if (_pageindex < (pagecount / 2 + pagecount % 2)) { sbsql.append(" select * from "); sbsql.append(" ( "); sbsql.appendformat(" select top {0} * from ", _pagesize); sbsql.append(" ( "); sbsql.appendformat(" select top {0} * ", _pagesize * _pageindex); sbsql.appendformat(" from ({0}) as t ", _safesql); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring()); sbsql.append(" ) as t "); sbsql.appendformat(" order by {0} ", sbreverseorder.tostring()); sbsql.append(" ) as t "); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring()); } //后半页数时的分页 else { sbsql.appendformat(" select top {0} * from ", _pagesize); sbsql.append(" ( "); sbsql.appendformat(" select top {0} * ", ((_recordcount % _pagesize) + _pagesize * (pagecount - _pageindex))); sbsql.appendformat(" from ({0}) as t ", _safesql); sbsql.appendformat(" order by {0} ", sbreverseorder.tostring()); sbsql.append(" ) as t "); sbsql.appendformat(" order by {0} ", sboriginalorder.tostring()); }

return sbsql.tostring(); }

/// <summary> /// 获取记录总数sql语句 /// </summary> /// <param name="_n">限定记录数</param> /// <param name="_safesql">sql查询语句</param> /// <returns>记录总数sql语句</returns> public static string createtopnsql(int _n, string _safesql) { return string.format(" select top {0} * from ({1}) as t ", _n, _safesql); }

/// <summary> /// 获取记录总数sql语句 /// </summary> /// <param name="_safesql">sql查询语句</param> /// <returns>记录总数sql语句</returns> public static string createcountingsql(string _safesql) { return string.format(" select count(1) as recordcount from ({0}) as t ", _safesql); }}

注：关于SQL Server 、access数据库 top分页方法的内容就先介绍到这里，更多相关文章的可以留意