程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> 淺談mssql access數據庫 top分頁辦法

淺談mssql access數據庫 top分頁辦法

編輯:MSSQL

淺談mssql access數據庫 top分頁辦法。本站提示廣大學習愛好者:(淺談mssql access數據庫 top分頁辦法)文章只能為提供參考,不一定能成為您想要的結果。以下是淺談mssql access數據庫 top分頁辦法正文


然則有益也有弊,它請求排序字段必需組成獨一記載,且select後的字段列表中,不許可湧現與排序字段同名的字段。
固然sql2k5及以上版本曾經供給了rownum()來停止分頁處置,然則應用sql2k停止開辟的照樣較多的

uusing system.collections.generic;
sing system;
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 sbrever搜索引擎優化教程rder = new stringbuilder(); //與原排序字段相反,用於分頁
        for (int i = 0; i < arrstrorders.length; i++)
        {
            arrstrorders[i] = arrstrorders[i].trim();  //去除前後空格
            if (i != 0)
            {
                sboriginalorder.append(", ");
                sbrever搜索引擎優化rder.append(", ");
            }
            sboriginalorder.append(arrstrorders[i]);

            int index = arrstrorders[i].indexof(" "); //斷定能否有起落標識
            if (index > 0)
            {
                //調換起落標識,分頁所需
                bool flag = arrstrorders[i].indexof(" desc", stringcomparison.ordinalignorecase) != -1;
                sbrever搜索引擎優化rder.appendformat("{0} {1}", arrstrorders[i].remove(index), flag ? "asc" : "desc");
            }
            else
            {
                sbrever搜索引擎優化rder.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} ", sbrever搜索引擎優化rder.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} ", sbrever搜索引擎優化rder.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} ", sbrever搜索引擎優化rder.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);
    }
}

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved