mssql access top分頁方法

　　mssql access top分頁方法

　　雙top法相比于not in和max法就是可以傳入一條sql語句來生成分頁sql語句也可多字段排序
但是有利也有弊它要求排序字段必須構成唯一記錄且select后的字段列表中不允許出現與排序字段同名的字段
雖然sqlk及以上版本已經提供了rownum()來進行分頁處理但是使用sqlk進行開發的還是較多的

　　using system;
using systemcollectionsgeneric;
using systemtext;

　　/// <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 = _orderfieldsplit(new char[] { } stringsplitoptionsremoveemptyentries);
        stringbuilder sboriginalorder = new stringbuilder(); //原排序字段
        stringbuilder sbreverseo教程rder = new stringbuilder(); //與原排序字段相反用于分頁
        for (int i = ; i < arrstrorderslength; i++)
        {
            arrstrorders[i] = arrstrorders[i]trim();  //去除前后空格
            if (i != )
            {
                sboriginalorderappend(" ");
                sbreverseorderappend(" ");
            }
            sboriginalorderappend(arrstrorders[i]);

　　int index = arrstrorders[i]indexof(" "); //判斷是否有升降標識
            if (index > )
            {
                //替換升降標識分頁所需
                bool flag = arrstrorders[i]indexof(" desc" stringcomparisonordinalignorecase) != ;
                sbreverseorderappendformat("{} {}" arrstrorders[i]remove(index) flag ? "asc" : "desc");
            }
            else
            {
                sbreverseorderappendformat("{} desc" arrstrorders[i]);
            }
        }

　　//計算總頁數
        _pagesize = _pagesize == ? _recordcount : _pagesize;
        int pagecount = (_recordcount + _pagesize ) / _pagesize;

　　//檢查當前頁數
        if (_pageindex < )
        {
            _pageindex = ;
        }
        else if (_pageindex > pagecount)
        {
            _pageindex = pagecount;
        }

　　stringbuilder sbsql = new stringbuilder();
        //第一頁時直接使用top n而不進行分頁查詢
        if (_pageindex == )
        {
            sbsqlappendformat(" select top {} * " _pagesize);
            sbsqlappendformat(" from ({}) as t " _safesql);
            sbsqlappendformat(" order by {} " sboriginalordertostring());
        }
        //最后一頁時減少一個top n 
        else if (_pageindex == pagecount)
        {
            sbsqlappend(" select * from ");
            sbsqlappend(" ( ");
            sbsqlappendformat(" select top {} * " _recordcount _pagesize * (_pageindex ));
            sbsqlappendformat(" from ({}) as t " _safesql);
            sbsqlappendformat(" order by {} " sbreverseordertostring());
            sbsqlappend(" ) as t ");
            sbsqlappendformat(" order by {} " sboriginalordertostring());
        }
        //前半頁數時的分頁
        else if (_pageindex < (pagecount / + pagecount % ))
        {
            sbsqlappend(" select * from ");
            sbsqlappend(" ( ");
            sbsqlappendformat(" select top {} * from " _pagesize);
            sbsqlappend(" ( ");
            sbsqlappendformat(" select top {} * " _pagesize * _pageindex);
            sbsqlappendformat(" from ({}) as t " _safesql);
            sbsqlappendformat(" order by {} " sboriginalordertostring());
            sbsqlappend(" ) as t ");
            sbsqlappendformat(" order by {} " sbreverseordertostring());
            sbsqlappend(" ) as t ");
            sbsqlappendformat(" order by {} " sboriginalordertostring());
        }
        //后半頁數時的分頁
        else
        {
            sbsqlappendformat(" select top {} * from " _pagesize);
            sbsqlappend(" ( ");
            sbsqlappendformat(" select top {} * " ((_recordcount % _pagesize) + _pagesize * (pagecount _pageindex)));
            sbsqlappendformat(" from ({}) as t " _safesql);
            sbsqlappendformat(" order by {} " sbreverseordertostring());
            sbsqlappend(" ) as t ");
            sbsqlappendformat(" order by {} " sboriginalordertostring());
        }

　　return sbsqltostring();
    }

　　/// <summary>
    /// 獲取記錄總數sql語句
    /// </summary>
    /// <param name="_n">限定記錄數</param>
    /// <param name="_safesql">sql查詢語句</param>
    /// <returns>記錄總數sql語句</returns>
    public static string createtopnsql(int _n string _safesql)
    {
        return stringformat(" select top {} * from ({}) as t " _n _safesql);
    }

　　/// <summary>
    /// 獲取記錄總數sql語句
    /// </summary>
    /// <param name="_safesql">sql查詢語句</param>
    /// <returns>記錄總數sql語句</returns>
    public static string createcountingsql(string _safesql)
    {
        return stringformat(" select count() as recordcount from ({}) as t " _safesql);
    }
}

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈