mssql access top分页方法

双top法相比于not in和max法，就是可以传入一条sql语句来生成分页sql语句，也可多字段排序；

但是有利也有弊，它要求排序字段必须构成唯一记录，且select后的字段列表中，不允许出现与排序字段同名的字段。

虽然sql2k5及以上版本已经提供了rownum()来进行分页处理，但是使用sql2k进行开发的还是较多的

using system;

using system.collections.generic;

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);

}

}

更多信息请查看IT技术专栏