DataGrid串連Access的快速分頁法(4)——動態產生SQL語句

來源:互聯網
上載者:User
access|datagrid|動態|分頁|語句 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);
}
}
}


相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。