mssql access top分頁方法
雙top法相比于not in和max法，就是可以傳入一條sql語句來生成分頁sql語句，也可多字段排序；
但是有利也有弊，它要求排序字段必須構(gòu)成唯一記錄，且select后的字段列表中，不允許出現(xiàn)與排序字段同名的字段。
雖然sql2k5及以上版本已經(jīng)提供了rownum()來進(jìn)行分頁處理，但是使用sql2k進(jìn)行開發(fā)的還是較多的
using system;
using system.collections.generic;
using system.text;
/// <summary>
/// 構(gòu)造分頁后的sql語句
/// </summary>
public static class paginghelper
{
/// <summary>
/// 獲取分頁sql語句，排序字段需要構(gòu)成唯一記錄
/// </summary>
/// <param name=_recordcount>記錄總數(shù)</param>
/// <param name=_pagesize>每頁記錄數(shù)</param>
/// <param name=_pageindex>當(dāng)前頁數(shù)</param>
/// <param name=_safesql>sql查詢語句</param>
/// <param name=_orderfield>排序字段，多個(gè)則用“,”隔開</param>
/// <returns>分頁sql語句</returns>
public static string createpagingsql(int _recordcount, int _pagesize, int _pageindex, string _safesql, string _orderfield)
{
//重新組合排序字段，防止有錯(cuò)誤
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( ); //判斷是否有升降標(biāo)識
if (index > 0)
{
//替換升降標(biāo)識，分頁所需
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]);
}
}
//計(jì)算總頁數(shù)
_pagesize = _pagesize == 0 ? _recordcount : _pagesize;
int pagecount = (_recordcount + _pagesize - 1) / _pagesize;
//檢查當(dāng)前頁數(shù)
if (_pageindex < 1)
{
_pageindex = 1;
}
else if (_pageindex > pagecount)
{
_pageindex = pagecount;
}
stringbuilder sbsql = new stringbuilder();
//第一頁時(shí)，直接使用top n，而不進(jìn)行分頁查詢
if (_pageindex == 1)
{
sbsql.appendformat( select top {0} * , _pagesize);
sbsql.appendformat( from ({0}) as t , _safesql);
sbsql.appendformat( order by {0} , sboriginalorder.tostring());
}
//最后一頁時(shí)，減少一個(gè)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());
}
//前半頁數(shù)時(shí)的分頁
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());
}
//后半頁數(shù)時(shí)的分頁
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>
/// 獲取記錄總數(shù)sql語句
/// </summary>
/// <param name=_n>限定記錄數(shù)</param>
/// <param name=_safesql>sql查詢語句</param>
/// <returns>記錄總數(shù)sql語句</returns>
public static string createtopnsql(int _n, string _safesql)
{
return string.format( select top {0} * from ({1}) as t , _n, _safesql);
}
/// <summary>
/// 獲取記錄總數(shù)sql語句
/// </summary>
/// <param name=_safesql>sql查詢語句</param>
/// <returns>記錄總數(shù)sql語句</returns>
public static string createcountingsql(string _safesql)
{
return string.format( select count(1) as recordcount from ({0}) as t , _safesql);
}
}