前天看了编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中显示即可!

|