mssql access top分頁方法
來源:易賢網(wǎng) 閱讀:829 次 日期:2014-10-22 09:53:16
溫馨提示:易賢網(wǎng)小編為您整理了“mssql access top分頁方法”,方便廣大網(wǎng)友查閱!

mssql access top分頁方法

雙top法相比于not in和max法,就是可以傳入一條sql語句來生成分頁sql語句,也可多字段排序;

但是有利也有弊,它要求排序字段必須構(gòu)成唯一記錄,且select后的字段列表中,不允許出現(xiàn)與排序字段同名的字段。

雖然sql2k5及以上版本已經(jīng)提供了rownum()來進行分頁處理,但是使用sql2k進行開發(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">當前頁數(shù)</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]);

}

}

//計算總頁數(shù)

_pagesize = _pagesize == 0 ? _recordcount : _pagesize;

int pagecount = (_recordcount + _pagesize - 1) / _pagesize;

//檢查當前頁數(shù)

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

}

//前半頁數(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ù)時的分頁

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

}

}

更多信息請查看IT技術(shù)專欄

更多信息請查看數(shù)據(jù)庫
易賢網(wǎng)手機網(wǎng)站地址:mssql access top分頁方法
由于各方面情況的不斷調(diào)整與變化,易賢網(wǎng)提供的所有考試信息和咨詢回復僅供參考,敬請考生以權(quán)威部門公布的正式信息和咨詢?yōu)闇剩?/div>

2025國考·省考課程試聽報名

  • 報班類型
  • 姓名
  • 手機號
  • 驗證碼
關(guān)于我們 | 聯(lián)系我們 | 人才招聘 | 網(wǎng)站聲明 | 網(wǎng)站幫助 | 非正式的簡要咨詢 | 簡要咨詢須知 | 加入群交流 | 手機站點 | 投訴建議
工業(yè)和信息化部備案號:滇ICP備2023014141號-1 云南省教育廳備案號:云教ICP備0901021 滇公網(wǎng)安備53010202001879號 人力資源服務許可證:(云)人服證字(2023)第0102001523號
聯(lián)系電話:0871-65099533/13759567129 獲取招聘考試信息及咨詢關(guān)注公眾號:hfpxwx
咨詢QQ:526150442(9:00—18:00)版權(quán)所有:易賢網(wǎng)