SQL Access 分页

using System;
using System.Text;

namespace qc.BLL
{
 /// <summary>
 /// Paging 的摘要说明。
 /// </summary>
 public class Paging
 {
  public Paging()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  #region  分页的主方法
  public static string  myPaged(
  
   int pageSize,
   //每页要显示的记录的数目。
   int pageIndex,           //要显示的页的索引。

   int recordCount,          //数据表中的记录总数。

   String tableName,      //要查询的数据表。

   String queryFields,   //要查询的字段。

   String primaryKey,   //主键字段。

   bool ascending,      //是否为升序排列。

   String condition   //查询的筛选条件。
  )
  {

   StringBuilder sb  = new StringBuilder();

   int pageCount    = GetPageCount(recordCount,pageSize);     //分页的总数

   int middleIndex  = GetMidPageIndex(pageCount);                   //中间页的索引

   int firstIndex     = 0;                                                         //第一页的索引

   int lastIndex     = pageCount - 1;

   if (pageIndex <= firstIndex)
   {
     sb.Append("SELECT TOP ").Append(pageSize).Append(" ")

     .Append(queryFields).Append(" FROM ").Append(tableName);
                    
      if (condition != String.Empty)

       sb.Append(" where ").Append(condition);

    if(ascending)
    {
     sb.Append(" ORDER BY ").Append(primaryKey).Append(" ").Append("ASC");      
    }
    else
    {
     sb.Append(" ORDER BY ").Append(primaryKey).Append(" ").Append("DESC");
    }
    

   }
   else if (pageIndex > firstIndex && pageIndex <= middleIndex)
   {

    sb.Append("SELECT TOP ").Append(pageSize).Append(" ")

     .Append(queryFields).Append(" FROM ").Append(tableName)

     .Append(" WHERE ").Append(primaryKey);

    if (ascending)

     sb.Append(" > (").Append(" SELECT MAX(");

    else

     sb.Append(" < (").Append(" SELECT MIN(");

    sb.Append(primaryKey).Append(") FROM ( SELECT TOP ")

     .Append(pageSize*pageIndex).Append(" ").Append(primaryKey)

     .Append(" FROM ").Append(tableName);

    if (condition != String.Empty)

     sb.Append(" where ").Append(condition);

    sb.Append(" ORDER BY ").Append(primaryKey).Append(" ")

     .Append(GetSortType(ascending)).Append(" ) TableA )");
    
    sb.Append(" ORDER BY ").Append(primaryKey).Append(" ")
     .Append(GetSortType(ascending));

 

   }

   else if (pageIndex > middleIndex && pageIndex < lastIndex)
   {
    sb.Append("SELECT TOP ").Append(pageSize).Append(" ")

     .Append(queryFields).Append(" FROM ").Append(tableName)

     .Append(" WHERE ").Append(primaryKey);

    if (ascending)

     sb.Append(" < (").Append(" SELECT MIN(");

    else

     sb.Append(" > (").Append(" SELECT MAX(");

    sb.Append(primaryKey).Append(") FROM ( SELECT TOP ")

     .Append(recordCount-pageSize*(pageIndex+1)).Append(" ").Append(primaryKey)

     .Append(" FROM ").Append(tableName);

    if (condition != String.Empty)

     sb.Append(" WHERE ").Append(condition);

    sb.Append(" ORDER BY ").Append(primaryKey).Append(" ")

     .Append(GetSortType(!ascending)).Append(" ) TableA )");

    if (condition != String.Empty)

     sb.Append(" WHERE ").Append(condition);

    sb.Append(" ORDER BY ").Append(primaryKey).Append(" ")

     .Append(GetSortType(!ascending)).Append(" ) TableB");

    sb.Append(" ORDER BY ").Append(primaryKey).Append(" ")

     .Append(GetSortType(ascending));
   

   }
   else if (pageIndex >= lastIndex)
   {
    // SELECT TOP ")

    //.Append(pageSize*pageIndex).Append(" ").Append(primaryKey)

    sb.Append("SELECT * FROM (SELECT TOP ").Append(recordCount-pageSize*lastIndex).Append(" ")
     .Append(queryFields )
     .Append(" FROM ").Append(tableName);
    if (condition != String.Empty)
    {
     sb.Append(" where ").Append(condition);
    }
    sb.Append(" ORDER BY ").Append(primaryKey).Append(" ")
     .Append(GetSortType(!ascending)).Append(" ) TableA");

    sb.Append(" ORDER BY ").Append(primaryKey).Append(" ")
     .Append(GetSortType(ascending));    

   }

   return sb.ToString();

  }

  #endregion

  // 根据记录总数和分页大小计算分页数。

  public static int GetPageCount(int recordCount, int pageSize)
  {

   return (int)Math.Ceiling((double)recordCount/pageSize);

  }

  // 计算中间页的页索引。

  public static int GetMidPageIndex(int pageCount)
  {

   return (int)Math.Ceiling((double)pageCount/2) - 1;

  }

  // 获取排序的方式（"ASC"表示升序，"DESC"表示降序）

  public static String GetSortType(bool ascending)
  {

   return (ascending ? "ASC" : "DESC");

  }

  // 获取一个布尔值，该值指示排序的方式是否为升序。

  public static bool IsAscending(String orderType)
  {

   return ((orderType.ToUpper() == "DESC") ? false : true);

  } 

}
}