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