作者：未知 来源：月光软件站 加入时间：2005-2-28　月光软件站

前天看了编datagrid access分页的文章,很不错,但只提到用主键分页,本人整理了一下,写了个通用的分页方法,大家可以参考一下,有什么问题请指出,如下:

public class FastPageSql
 {
  private int allCount; //表中记录总数
  private bool isDesc; //排序方式
  private string primaryKey; //表的主键
  private string orderKey; //排序键
  private string selectFields; //要选择的字段
  private string queryCondition; //筛选条件
  private string tableName; //表名称
  private int perPageCount; //每页显示数
  private int pageIndex;  //显示页的索引
  private int totalIndex;     //总页数
  private int middleIndex; //中间页数;

  public FastPageSql(int allcount,bool isdesc,string primarykey,string orderkey,string tablename,int perpagecount,int pageindex,string selectfields,string querycondition)
  {
   allCount=allcount;
   isDesc=isdesc;
   primaryKey=primarykey;
   orderKey=orderkey;
   selectFields=selectfields;
   queryCondition=querycondition;
   tableName=tablename;
   perPageCount=perpagecount;
   pageIndex=pageindex;
   totalIndex=getTotalIndex();
   middleIndex=(int)(totalIndex/2);
  }
  private int getTotalIndex()
  {
   if(allCount%perPageCount>0)
    return (int)(allCount/perPageCount)+1;
   else
    return allCount/perPageCount;

  }
  public string GetFastPageSql()
  {
   string sql="";
   if(pageIndex<=1)
   {
    #region 第一页代码
    sql="select top "+perPageCount+" "+selectFields+" from "+tableName+" "+((queryCondition!="")?"where "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"desc":"asc")+"";
    #endregion
   }
   else if(1   {
    #region 中间页之前
    if(primaryKey==orderKey)
     sql="select top "+perPageCount+" "+selectFields+" from "+tableName+" where "+primaryKey+""+((isDesc)?"<":">")+"(select "+((isDesc)?"min":"max")+"("+primaryKey+") from(select top "+perPageCount*(pageIndex-1)+" "+primaryKey+" from "+tableName+" "+((queryCondition!="")?"where "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"desc":"asc")+" )) "+((queryCondition!="")?"and "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"desc":"asc")+"";
    else
     sql="select "+selectFields+" from (select top "+perPageCount+" "+selectFields+" from (select top "+perPageCount*pageIndex+" "+selectFields+" from "+tableName+" "+((queryCondition!="")?"where "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"desc":"asc")+") order by "+orderKey+" "+((isDesc)?"asc":"desc")+") order by "+orderKey+" "+((isDesc)?"desc":"asc")+"";
    #endregion

   }
   else if(middleIndex   {
    #region 中间页到最后页之间
    if(primaryKey==orderKey)
     sql="select "+selectFields+" from (select top "+perPageCount+" "+selectFields+" from "+tableName+" where "+primaryKey+""+((isDesc)?">":"<")+"(select "+((isDesc)?"max":"min")+"("+primaryKey+") from (select top "+(allCount-perPageCount*pageIndex)+" "+primaryKey+" from "+tableName+" "+((queryCondition!="")?"where "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"asc":"desc")+" )) "+((queryCondition!="")?"and "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"asc":"desc")+") order by "+orderKey+" "+((isDesc)?"desc":"asc")+"";
    else
     sql="select top "+perPageCount+" "+selectFields+" from (select top "+(allCount-(pageIndex-1)*perPageCount)+" "+selectFields+" from "+tableName+" "+((queryCondition!="")?"where "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"asc":"desc")+") order by "+orderKey+" "+((isDesc)?"desc":"asc")+"";

    #endregion

   }
   else if(pageIndex>=totalIndex)
   {
    #region 最后页
    sql="select "+selectFields+" from (select top "+(allCount-(totalIndex-1)*perPageCount)+" "+selectFields+" from "+tableName+" "+((queryCondition!="")?"where "+queryCondition+"":"")+" order by "+orderKey+" "+((isDesc)?"asc":"desc")+") order by "+orderKey+" "+((isDesc)?"desc":"asc")+"";
    #endregion

   }
   return sql;
  }
  }

返回的sql语句将只选择当前页所需要的记录,程序中直接填充到DataSet中显示即可!

相关软件：