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