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 + <br> " 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;
}
}
}
--以上代碼是針對之前寫的TOP MAX模式的分頁存儲過程修改
--以上分頁算法對SQL Server 和 Access同樣有效
參見:http://www.cnblogs.com/hertcloud/archive/2005/12/21/301327.Html
//調用函數例子
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;
}
http://blog.csdn.Net/todaywlq/archive/2007/01/29/1497418.ASPx
