通用分页算法一
通用分页算法一
2007年09月05日 星期三 15:03
|
using System;
namespace CountryPark.DAL { /**//// <summary> /// PageList 的摘要说明。 /// </summary> public sealed class PageList { static PageList() { } /**//// <summary> /// 分页查询数据记录总数获取 /// </summary> /// <param name="_tbName">----要显示的表或多个表的连接</param> /// <param name="_ID">----主表的主键</param> /// <param name="_strCondition">----查询条件,不需where</param> /// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param> /// <returns></returns> public static string getPageListCounts(string _tbName, string _ID, string _strCondition, int _Dist) { //---存放取得查询结果总数的查询语句 //---对含有DISTINCT的查询进行SQL构造 //---对含有DISTINCT的总数查询进行SQL构造 string strTmp="", SqlSelect="", SqlCounts=""; if (_Dist == 0) { SqlSelect = "Select "; SqlCounts = "COUNT(*)"; } else { SqlSelect = "Select DISTINCT "; SqlCounts = "COUNT(DISTINCT "+ _ID +")"; } if (_strCondition == string.Empty) { strTmp = SqlSelect +" @Counts="+ SqlCounts +" FROM "+ _tbName; } else { strTmp = SqlSelect +" @Counts="+ SqlCounts +" FROM "+ " Where (1=1) "+ _strCondition; } return strTmp; } /**//// <summary> /// 获取分页数据查询SQL /// </summary> /// <param name="_tbName">----要显示的表或多个表的连接</param> /// <param name="_fldName">----要显示的字段列表</param> /// <param name="_PageSize">----每页显示的记录个数</param> /// <param name="_Page">----要显示那一页的记录</param> /// <param name="_PageCount">----查询结果分页后的总页数</param> /// <param name="_Counts">----查询到的记录数</param> /// <param name="_fldSort">----排序字段列表或条件(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')</param> /// <param name="_Sort">----排序方法，0为升序，1为降序</param> /// <param name="_strCondition">----查询条件,不需where</param> /// <param name="_ID">----主表的主键</param> /// <param name="_Dist">----是否添加查询字段的 DISTINCT 默认0不添加/1添加</param> /// <returns></returns> public static string getPageListSql(string _tbName, string _fldName, int _PageSize, int _Page, out int _PageCount, int _Counts, string _fldSort, int _Sort, string _strCondition, string _ID, int _Dist) { string strTmp=""; //---strTmp用于返回的SQL语句 string SqlSelect="", strSortType="", strFSortType=""; if (_Dist == 0) { SqlSelect = "Select "; } else { SqlSelect = "Select DISTINCT "; } if (_Sort == 0) { strFSortType = " ASC"; strSortType = " DESC"; } else { strFSortType = " DESC"; strSortType = " ASC"; } // ----取得查询结果总数量----- int tmpCounts = 1; if (_Counts != 0) { tmpCounts = _Counts; } // --取得分页总数 _PageCount = (tmpCounts + _PageSize - 1)/_PageSize; // /**//**当前页大于总页数 取最后一页**/ if (_Page > _PageCount) { _Page = _PageCount; } if (_Page <= 0) { _Page = 1; } // --/*-----数据分页2分处理-------*/ int pageIndex = tmpCounts/_PageSize; int lastCount = tmpCounts%_PageSize; if (lastCount > 0) { pageIndex = pageIndex + 1; } else { lastCount = _PageSize; } if (_strCondition == string.Empty) // --没有设置显示条件 { if (pageIndex < 2 || _Page <= (pageIndex/2 + pageIndex%2)) //--前半部分数据处理 { if (_Page == 1) { strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +" orDER BY "+ _fldSort +" "+ strFSortType; } else { strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +" Where "+ _ID +" <(Select MIN("+ _ID +") FROM ("+ SqlSelect +" TOP "+ _PageSize*(_Page-1) +" "+ _ID +" FROM "+ _tbName + " orDER BY "+ _fldSort +" "+ strFSortType +") AS TBMinID) orDER BY "+ _fldSort +" "+ strFSortType; } } else { _Page = pageIndex - _Page + 1; //后半部分数据处理 if (_Page <= 1) //--最后一页数据显示 { strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ lastCount +" "+ _fldName +" FROM "+ _tbName +" orDER BY "+ _fldSort +" "+ strSortType +") AS TempTB"+ " orDER BY "+ _fldSort +" "+ strFSortType; } else { strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName + " Where "+ _ID +" >(Select MAX("+ _ID +") FROM("+ SqlSelect +" TOP "+ (_PageSize*(_Page-2)+lastCount) +" "+ _ID +" FROM "+ _tbName + " orDER BY "+ _fldSort +" "+ strSortType +") AS TBMaxID) orDER BY "+ _fldSort +" "+ strSortType +") AS TempTB orDER BY "+ _fldSort +" "+ strFSortType; } } } else // --有查询条件 { if (pageIndex < 2 || _Page <=(pageIndex/2 + pageIndex%2))//--前半部分数据处理 { if (_Page == 1) { strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName +"Where 1=1 "+ _strCondition +" orDER BY "+ _fldSort +" "+ strFSortType; } else { strTmp = SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName + " Where "+ _ID +" <(Select MIN("+ _ID +") FROM ("+ SqlSelect +" TOP "+ (_PageSize*(_Page-1)) +" "+ _ID +" FROM " +_tbName + " Where 1=1 "+ _strCondition +" orDER BY "+ _fldSort +" "+ strFSortType +") AS TBMaxID) "+ _strCondition + " orDER BY "+ _fldSort +" "+ strFSortType; } } else //--后半部分数据处理 { _Page = pageIndex-_Page+1; if (_Page <= 1) //--最后一页数据显示 { strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ lastCount +" "+ _fldName +" FROM "+ _tbName + " Where 1=1 "+ _strCondition +" orDER BY "+ _fldSort +" "+ strSortType +") AS TempTB orDER BY "+ _fldSort +" "+ strFSortType; } else { strTmp = SqlSelect +" * FROM ("+ SqlSelect +" TOP "+ _PageSize +" "+ _fldName +" FROM "+ _tbName + " Where "+ _ID +" >(Select MAX("+ _ID +") FROM("+ SqlSelect +" TOP "+ (_PageSize*(_Page-2)+ lastCount) +" "+ _ID +" FROM "+ _tbName + " Where 1=1 "+ _strCondition +" orDER BY "+ _fldSort +" "+ strSortType +") AS TBMaxID) "+ _strCondition + " orDER BY "+ _fldSort +" "+ strSortType +") AS TempTB orDER BY "+ _fldSort +" "+ strFSortType; } } } return strTmp; } } } public IList getParkDataList(string key, int curPage, out int pageCount, int pageSize, int Counts) { IList list = new ArrayList(); string SECLECT_FIELD = "T_Park.ParkID, T_Park.ParkTitle, T_Park.ParkLetter, T_ParkArea.AreaName, T_ParkType.ParkTypeName "; string SECLECT_TABLE = "T_ParkType INNER JOIN (T_ParkArea INNER JOIN T_Park ON T_ParkArea.ParkAreaID = T_Park.ParkAreaID) ON T_ParkType.ParkTypeID = T_Park.ParkTypeID"; string SECLECT_CONDITION = string.Empty; if (key != string.Empty) { SECLECT_CONDITION = " AND T_Park.ParkTitle like '%"+ key +"%'"; } string Select_ID = "ParkID"; string Select_FLDSORT = "ParkID"; int Select_SORT = 1; int Select_DIST = 0; string SQL = PageList.getPageListSql(SECLECT_TABLE, SECLECT_FIELD, pageSize, curPage, out pageCount, Counts, Select_FLDSORT, Select_SORT, SECLECT_CONDITION, Select_ID, Select_DIST); //string strCondition; OleDb db = new OleDb(); ParkBE park; using(OleDbDataReader dr = (OleDbDataReader)db.ExecuteReader(CommonFun.GetConnectionString(), CommandType.Text, SQL)) { while (dr.Read()) { park = new ParkBE(); park.ParkID = Convert.ToInt32(dr[0]); park.ParkTitle = dr[1].ToString(); park.ParkLetter = dr[2].ToString(); park.ParkAreaName = dr[3].ToString(); park.ParkTypeName = dr[4].ToString(); list.Add(park); } } return list; } |
