DataGrid连接Access的快速分页法（4）——动态生成SQL语句

datagrid贯穿access的赶快分页法（4）——动静天生sql语句using system;using system.text;namespace paging{ /// <summary> /// fastpaging 的纲要证明。 /// </summary> public class fastpaging { private fastpaging() { } /// <summary> /// 获得按照指定字段排序并分页查问的 select 语句。 /// </summary> /// <param name="pagesize">每页要表露的记载的数量。</param> /// <param name="pageindex">要表露的页的索引。</param> /// <param name="recordcount">数据表中的记载总额。</param> /// <param name="tablename">要查问的数据表。</param> /// <param name="queryfields">要查问的字段。</param> /// <param name="primarykey">主键字段。</param> /// <param name="ascending">能否为叶序陈设。</param> /// <param name="condition">查问的挑选前提。</param> /// <returns>归来排序并分页查问的 select 语句。</returns> public static string paging( int pagesize, int pageindex, int recordcount, string tablename, string queryfields, string primarykey, bool ascending, string condition ) { #region 实行 stringbuilder sb = new stringbuilder(); int pagecount = getpagecount(recordcount,pagesize); //分页的总额 int middleindex = getmidpageindex(pagecount); //中央页的索引 int firstindex = 0; //第一页的索引 int lastindex = pagecount - 1; //结果一页的索引 #region @pageindex <= @firstindex 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); sb.append(" order by ").append(primarykey).append(" ") .append(getsorttype(ascending)); } #endregion #region @firstindex < @pageindex <= @middleindex 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 )"); if (condition != string.empty) sb.append(" and ").append(condition); sb.append(" order by ").append(primarykey).append(" ") .append(getsorttype(ascending)); } #endregion #region @middleindex < @pageindex < @lastindex else if (pageindex > middleindex && pageindex < lastindex) { sb.append("select * from ( 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(" and ").append(condition); sb.append(" order by ").append(primarykey).append(" ") .append(getsorttype(!ascending)) .append(" ) tableb order by ").append(primarykey).append(" ") .append(getsorttype(ascending)); } #endregion #region @pageindex >= @lastindex else if (pageindex >= lastindex) { 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 order by ").append(primarykey).append(" ") .append(getsorttype(ascending)); } #endregion return sb.tostring(); #endregion } /// <summary> /// 获得按照指定字段排序并分页查问的 select 语句。 /// </summary> /// <param name="pagesize">每页要表露的记载的数量。</param> /// <param name="pageindex">要表露的页的索引。</param> /// <param name="recordcount">数据表中的记载总额。</param> /// <param name="tablename">要查问的数据表。</param> /// <param name="queryfields">要查问的字段。</param> /// <param name="primarykey">主键字段。</param> public static string paging( int pagesize, int pageindex, int recordcount, string tablename, string queryfields, string primarykey ) { return paging(pagesize, pageindex, recordcount, tablename, queryfields, primarykey, true, string.empty); } /// <summary> /// 获得按照指定字段排序并分页查问的 select 语句。 /// </summary> /// <param name="pagesize">每页要表露的记载的数量。</param> /// <param name="pageindex">要表露的页的索引。</param> /// <param name="recordcount">数据表中的记载总额。</param> /// <param name="tablename">要查问的数据表。</param> /// <param name="queryfields">要查问的字段。</param> /// <param name="primarykey">主键字段。</param> /// <param name="ascending">能否为叶序陈设。</param> /// <returns>归来排序并分页查问的 select 语句。</returns> public static string paging( int pagesize, int pageindex, int recordcount, string tablename, string queryfields, string primarykey, bool ascending ) { return paging(pagesize, pageindex, recordcount, tablename, queryfields, primarykey, ascending, string.empty); } /// <summary> /// 获得按照指定字段排序并分页查问的 select 语句。 /// </summary> /// <param name="pagesize">每页要表露的记载的数量。</param> /// <param name="pageindex">要表露的页的索引。</param> /// <param name="recordcount">数据表中的记载总额。</param> /// <param name="tablename">要查问的数据表。</param> /// <param name="queryfields">要查问的字段。</param> /// <param name="primarykey">主键字段。</param> /// <param name="condition">查问的挑选前提。</param> /// <returns>归来排序并分页查问的 select 语句。</returns> public static string paging( int pagesize, int pageindex, int recordcount, string tablename, string queryfields, string primarykey, string condition ) { return paging(pagesize, pageindex, recordcount, tablename, queryfields, primarykey, true, condition); } /// <summary> /// 计划分页数。 /// </summary> /// <param name="recordcount">表中得记载总额。</param> /// <param name="pagesize">每页表露的记载数。</param> /// <returns>分页数。</returns> public static int getpagecount(int recordcount, int pagesize) { return (int)math.ceiling((double)recordcount/pagesize); } /// <summary> /// 计划中央页的页索引。 /// </summary> /// <param name="pagecount">分页数。</param> /// <returns>中央页的页索引。</returns> public static int getmidpageindex(int pagecount) { return (int)math.ceiling((double)pagecount/2) - 1; } /// <summary> /// 获得排序的办法（"asc" 表白叶序，"desc" 表白降序）。 /// </summary> /// <param name="ascending">能否为叶序。</param> /// <returns>排序的办法（"asc" 表白叶序，"desc" 表白降序）。</returns> public static string getsorttype(bool ascending) { return (ascending ? "asc" : "desc"); } /// <summary> /// 获得一个布尔值，该值引导排序的办法能否为叶序。 /// </summary> /// <param name="ordertype">排序的办法（"asc" 表白叶序，"desc" 表白降序）。</param> /// <returns>"asc"则为 true；"desc"则为 false；其它的为 true。</returns> public static bool isascending(string ordertype) { return ((ordertype.toupper() == "desc") ? false : true); } }}