ACCESS分页算法

using System;
using System.Collections.Generic;
using System.Text;

namespace hsyzero.Common
{
/// <summary>
/// 双TOP二分法生成分页SQL类(帮忙MSSQL、ACCESS)
/// </summary>
public static class PagingHelper
{
/// <summary>
/// 获取分页SQL语句,排序字段须要整合唯1记录
/// </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 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]);
}
}

//总计总页数
_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());
}
//最终1页时,收缩贰个TOP
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());
}
//前半页数时的分页
else if (_pageIndex <= (pageCount / 2 + pageCount % 2) + 1)
{
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());
}
//后半页数时的分页
else
{
sbSql.AppendFormat(” SELECT TOP {0} * FROM “, _pageSize);
sbSql.Append(” ( “);
sbSql.AppendFormat(” SELECT TOP {0} * “, ((_recordCount % _pageSize)

  • _pageSize * (pageCount – _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());
    }
    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);
}
}
}

相关文章