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技術專欄