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;
}
}
}
--以上代碼是針對之前寫的TOP MAX模式的分頁預存程序修改
--以上分頁演算法對SQL SERVER 和 ACCESS同樣有效
