using System;
using System.Collections.Generic;
using System.Text;
/// <summary>
/// 构造分页后的SQL语句
/// </summary>
public static class PagingHelper
{
/// <summary>
/// 获取分页SQL语句，排序字段需要构成唯一记录
/// </summary>
/// <param name="recordCount">记录总数</param>
/// <param name="pageSize">每页记录数</param>
/// <param name="pageNum">当前页数</param>
/// <param name="safeSql">SQL查询语句</param>
/// <param name="orderField">排序字段，多个则用“,”隔开</param>
/// <returns>分页SQL语句</returns>
public static string CreatePagingSql(int recordCount, int pageSize, int pageNum, 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 (pageNum < 1)
{
pageNum = 1;
}
else if (pageNum > pageCount)
{
pageNum = pageCount;
}
StringBuilder sbSql = new StringBuilder();
if (pageNum == 1)
{
sbSql.AppendFormat(" SELECT TOP {0} * ", pageSize);
sbSql.AppendFormat(" FROM ({0}) AS T ", safeSql);
sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString());
}
else if (pageNum == pageCount)
{
sbSql.Append(" SELECT * FROM ");
sbSql.Append(" ( ");
sbSql.AppendFormat(" SELECT TOP {0} * ", recordCount - pageSize * (pageNum - 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 (pageNum < (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 * pageNum);
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 * (pageCount - 1)) + pageSize * (pageCount - pageNum)));
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);
}
}
| 
