博客开张，先发以前的几个老物件儿，以前写下来的，现在发上来权当记录和分享。这个类是用来生成分页SQL的，记得那时思考写一个只传一条查询语句就能生成分页SQL的方法，然后发现了双TOP分页法，不过双TOP法在后半页就很慢，后来一个同学发过来的一篇文章：2分法-通用存储过程分页(top max模式)版本(性能相对之前的not in版本极大提高)通过它，发现了还二分法这东东，甚感神奇，不过它是用MAX的方式，逐改为双TOP法。

代码如下：

 
    
  1. view sourceprint?using System; 
  2. using System.Collections.Generic; 
  3. using System.Text; 
  4. /// <summary> 
  5. /// 构造分页后的SQL语句 
  6. /// </summary> 
  7. public static class PagingHelper 
  8. { 
  9.  /// <summary> 
  10.  /// 获取分页SQL语句，排序字段需要构成唯一记录 
  11.  /// </summary> 
  12.  /// <param name="_recordCount">记录总数</param> 
  13.  /// <param name="_pageSize">每页记录数</param> 
  14.  /// <param name="_pageIndex">当前页数</param> 
  15.  /// <param name="_safeSql">SQL查询语句</param> 
  16.  /// <param name="_orderField">排序字段，多个则用“,”隔开</param> 
  17.  /// <returns>分页SQL语句</returns> 
  18.  public static string CreatePagingSql(int _recordCount, int _pageSize, int _pageIndex, string _safeSql, string _orderField) 
  19.  { 
  20.  //重新组合排序字段，防止有错误 
  21.  string[] arrStrOrders = _orderField.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); 
  22.  StringBuilder sbOriginalOrder = new StringBuilder(); //原排序字段 
  23.  StringBuilder sbReverseOrder = new StringBuilder(); //与原排序字段相反，用于分页 
  24.  for (int i = 0; i < arrStrOrders.Length; i++) 
  25.  { 
  26.  arrStrOrders[i] = arrStrOrders[i].Trim(); //去除前后空格 
  27.  if (i != 0) 
  28.  { 
  29.  sbOriginalOrder.Append(", "); 
  30.  sbReverseOrder.Append(", "); 
  31.  } 
  32.  sbOriginalOrder.Append(arrStrOrders[i]); 
  33.  int index = arrStrOrders[i].IndexOf(" "); //判断是否有升降标识 
  34.  if (index > 0) 
  35.  { 
  36.  //替换升降标识，分页所需 
  37.  bool flag = arrStrOrders[i].IndexOf(" DESC", StringComparison.OrdinalIgnoreCase) != -1; 
  38.  sbReverseOrder.AppendFormat("{0} {1}", arrStrOrders[i].Remove(index), flag ? "ASC" : "DESC"); 
  39.  } 
  40.  else 
  41.  { 
  42.  sbReverseOrder.AppendFormat("{0} DESC", arrStrOrders[i]); 
  43.  } 
  44.  } 
  45.  //计算总页数 
  46.  _pageSize = _pageSize == 0 ? _recordCount : _pageSize; 
  47.  int pageCount = (_recordCount + _pageSize - 1) / _pageSize; 
  48.  //检查当前页数 
  49.  if (_pageIndex < 1) 
  50.  { 
  51.  _pageIndex = 1; 
  52.  } 
  53.  else if (_pageIndex > pageCount) 
  54.  { 
  55.  _pageIndex = pageCount; 
  56.  } 
  57.  StringBuilder sbSql = new StringBuilder(); 
  58.  //第一页时，直接使用TOP n，而不进行分页查询 
  59.  if (_pageIndex == 1) 
  60.  { 
  61.  sbSql.AppendFormat(" SELECT TOP {0} * ", _pageSize); 
  62.  sbSql.AppendFormat(" FROM ({0}) AS T ", _safeSql); 
  63.  sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString()); 
  64.  } 
  65.  //最后一页时，减少一个TOP n 
  66.  else if (_pageIndex == pageCount) 
  67.  { 
  68.  sbSql.Append(" SELECT * FROM "); 
  69.  sbSql.Append(" ( "); 
  70.  sbSql.AppendFormat(" SELECT TOP {0} * ", _recordCount - _pageSize * (_pageIndex - 1)); 
  71.  sbSql.AppendFormat(" FROM ({0}) AS T ", _safeSql); 
  72.  sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString()); 
  73.  sbSql.Append(" ) AS T "); 
  74.  sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString()); 
  75.  } 
  76.  //前半页数时的分页 
  77.  else if (_pageIndex < (pageCount / 2 + pageCount % 2)) 
  78.  { 
  79.  sbSql.Append(" SELECT * FROM "); 
  80.  sbSql.Append(" ( "); 
  81.  sbSql.AppendFormat(" SELECT TOP {0} * FROM ", _pageSize); 
  82.  sbSql.Append(" ( "); 
  83.  sbSql.AppendFormat(" SELECT TOP {0} * ", _pageSize * _pageIndex); 
  84.  sbSql.AppendFormat(" FROM ({0}) AS T ", _safeSql); 
  85.  sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString()); 
  86.  sbSql.Append(" ) AS T "); 
  87.  sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString()); 
  88.  sbSql.Append(" ) AS T "); 
  89.  sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString()); 
  90.  } 
  91.  //后半页数时的分页 
  92.  else 
  93.  { 
  94.  sbSql.AppendFormat(" SELECT TOP {0} * FROM ", _pageSize); 
  95.  sbSql.Append(" ( "); 
  96.  sbSql.AppendFormat(" SELECT TOP {0} * ", ((_recordCount % _pageSize) + _pageSize * (pageCount - _pageIndex))); 
  97.  sbSql.AppendFormat(" FROM ({0}) AS T ", _safeSql); 
  98.  sbSql.AppendFormat(" ORDER BY {0} ", sbReverseOrder.ToString()); 
  99.  sbSql.Append(" ) AS T "); 
  100.  sbSql.AppendFormat(" ORDER BY {0} ", sbOriginalOrder.ToString()); 
  101.  } 
  102.  return sbSql.ToString(); 
  103.  } 
  104.  /// <summary> 
  105.  /// 获取记录总数SQL语句 
  106.  /// </summary> 
  107.  /// <param name="_n">限定记录数</param> 
  108.  /// <param name="_safeSql">SQL查询语句</param> 
  109.  /// <returns>记录总数SQL语句</returns> 
  110.  public static string CreateTopnSql(int _n, string _safeSql) 
  111.  { 
  112.  return string.Format(" SELECT TOP {0} * FROM ({1}) AS T ", _n, _safeSql); 
  113.  } 
  114.  /// <summary> 
  115.  /// 获取记录总数SQL语句 
  116.  /// </summary> 
  117.  /// <param name="_safeSql">SQL查询语句</param> 
  118.  /// <returns>记录总数SQL语句</returns> 
  119.  public static string CreateCountingSql(string _safeSql) 
  120.  { 
  121.  return string.Format(" SELECT COUNT(1) AS RecordCount FROM ({0}) AS T ", _safeSql); 
  122.  } 
  123. } 
 
    
 
     
 
      

        双TOP法相比于NOT IN和MAX法，就是可以传入一条SQL语句来生成分页SQL语句，也可多字段排序；但是有利也有弊，它要求排序字段必须构成唯一记录，且SELECT后的字段列表中，不允许出现与排序字段同名的字段。虽然SQL2K5及以上版本已经提供了ROWNUM()来进行分页处理，但是使用SQL2K进行开发的还是较多的。 
      
 
     
 
    
 
   
 
  
 
  
原文链接：http://www.cnblogs.com/jitian/archive/2011/03/22/1991961.html