Access分页查询-双Top方式

Access分页帮助类。使用双Top方式很好的实现了Access的分页功能。

namespace Eternal.Util
{
    /// <summary>
    /// Access分页帮助类
    /// </summary>
    public static class AccessPagingHelper
    {
        /// <summary>
        /// 获取分页SQL语句，排序字段需要构成唯一记录
        /// </summary>
        /// <param name="_safeSql">SQL查询语句</param>
        /// <param name="_recordCount">记录总数</param>
        /// <param name="_pageSize">每页记录数</param>
        /// <param name="_pageIndex">当前页数</param>        
        /// <returns>分页SQL语句</returns>
        public static string CreatePagingSql(string strSql, int _recordCount, int _pageSize, int _pageIndex)
        {
            string strOrderBy = "";
            string distinctStr = string.Empty;
            string loweredString = strSql.ToLower();
            int orderByIndex = loweredString.IndexOf("order by");
            if (orderByIndex != -1)
            {
                strOrderBy = strSql.Substring(orderByIndex + 8);//存储Order by
                strSql = strSql.Substring(0, orderByIndex - 1);//去掉Order by           
            }
            if (string.IsNullOrEmpty(strOrderBy))
            {   //没有Order by 不能分页

                throw new Exception("请配置Order by字段！");
            }
            if (loweredString.TrimStart().StartsWith("select"))
            {
                int index = 6;
                if (loweredString.StartsWith("select distinct"))
                {
                    distinctStr = "DISTINCT ";
                    index = 15;
                }
                strSql = strSql.Substring(index);//去掉select DISTINCT
            }

            //重新组合排序字段，防止有错误
            string[] arrStrOrders = strOrderBy.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            StringBuilder sbOriginalOrder = new StringBuilder(); //原排序字段
            StringBuilder sbReverseOrder = 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]);
                }
            }

            string orderbyOri = sbOriginalOrder.ToString();
            string orderbyReverse = sbReverseOrder.ToString();
            if (orderbyOri != null && orderbyOri.Length > 0)
            {
                orderbyOri = "ORDER BY  " + orderbyOri;
                orderbyReverse = "ORDER BY  " + orderbyReverse;
            }

            //计算总页数
            _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 {0} TOP {1} {2} {3}", distinctStr, _pageSize, strSql, orderbyOri);
            }
            //最后一页时，减少一个TOP n 
            else if (_pageIndex == pageCount)
            {
                string strInnerSql = string.Format(" SELECT {0} TOP {1} {2} {3}", distinctStr, _recordCount - _pageSize * (_pageIndex - 1), strSql, orderbyReverse);
                sbSql.AppendFormat(" SELECT * FROM  ({0} ) t  {1}", strInnerSql, orderbyOri);
            }
            //前半页数时的分页
            else if (_pageIndex < (pageCount / 2 + pageCount % 2))
            {
                string strInnerSql = string.Format(" SELECT {0} TOP {1} {2} {3}", distinctStr, _pageSize * _pageIndex, strSql, orderbyOri);
                sbSql.AppendFormat(@"SELECT * FROM  (SELECT TOP {0} * FROM ({1}) t  {2}  ) t1 {3}",
                    _pageSize, strInnerSql, orderbyReverse, orderbyOri);
            }
            //后半页数时的分页
            else
            {
                string strInnerSql = string.Format(" SELECT {0} TOP {1} {2} {3}", distinctStr, ((_recordCount % _pageSize) + _pageSize * (pageCount - _pageIndex)), strSql, orderbyReverse);
                sbSql.AppendFormat(@"SELECT TOP {0} * FROM ({1}) t  {2}",
                    _pageSize, strInnerSql, orderbyOri);
            }

            return sbSql.ToString();
        }
    }
}