OEA ORM中的分頁支援

    本篇部落客要描述分頁的常見技術方案，以及在 OEA 架構中的分頁的應用及實現原理。

 

分頁的幾種方案

    分頁是解決大資料量顯示的有效方法。根據分頁技術應用的位置不同，大致可以把分頁分為以下幾種：

• 介面層分頁 

    介面層的分頁，類似於介面的虛擬化技術，是只顯示需要的資料的一種技術。OEA 的 WPF 介面中目前已經實現了 UI 虛擬化，所以不再實現介面層分頁。

優點：

* 簡單。許多控制項都支援在介面層直接進行分頁。

* 換頁時，響應快。（在 C/S 結構下使用這種方案，資料都已經到達用戶端，所以在分頁時不需要額外的資料查詢，響應速度較快。）

缺點：

* 不用於太大的資料分頁。由於沒有減少網路傳輸，首次載入時較慢，需要把所有資料都傳輸到用戶端。

• 實體層分頁

    在實體層進行分頁操作的方案，很少會被使用。它是把查詢出來的資料，在伺服器端都轉換為實體，然後再找到具體頁的實體資料，其它的資料則直接丟棄。

優點：

* 減少了首次的網路傳輸，對於用戶端而言，調用的是分頁的 API。

* 簡單。

* 通用性強，與資料庫無關，方案可以跨多種資料庫。

* 統計總行數不需要發起二次查詢。

缺點：

* 佔用記憶體，依然不能用於太大的資料分頁。

• 資料層分頁 

    這種方案一般使用 IDataReader 實現。查詢的 SQL 依然是查詢所有的資料，但是在對查詢出的 IDataReader 進行遍曆讀取每一行時，唯讀取對應頁的資料，其它頁的資料則忽略。同時，遍曆到記錄集的最後一行，即可獲得資料的總行數。

優點：

* 不佔用大量記憶體。只把需要的資料讀取到記憶體中。

* 簡單。

* 通用性強，與資料庫無關，方案可以跨多種資料庫。

* 統計總行數不需要發起二次查詢。

缺點：

* 查詢的 SQL 會查詢很大的一張表。遍曆依然需要耗費一定的時間。

• 資料庫分頁 

    分頁的最終方案，自然是在資料庫中進行分頁。這也是大多數情況會選用的方案。

優點：

* 效能最好。速度快、佔用記憶體小。

* 統計行數時，往往需要重新發起查詢。

缺點：

* 對於架構開發而言，要產生分頁相關的 SQL，較麻煩。

* 方案與特定資料庫相關。通用性低。

    雖然提到了這幾種不同層面的分頁方案。但是對應應用開發而言，資料庫的分頁是最常用的。只是在做 OEA 架構開發時，由於要支援多種資料庫，所以需要在合適時採用不同的方案。同時，也不會考慮使用預存程序來輔助分頁。

OEA 分頁 - 應用程式層介面

    在說明 OEA 的分頁前。先介紹一個 PagingInfo 類型（老版本中，該類名為 PagerInfo），這關係到整個分頁方案的介面設計：

圖1 位於 Common(原 hxy)程式集中的 PagingInfo 類型

圖2 PagingInfo 類型介面

    在查詢資料時，我們指定了查詢的具體頁碼 PageIndex、一頁所含資料行數 PageSize，就可以把該頁的資料顯示在介面上了。但是，在分頁時，往往要在介面中顯示一個分頁尾，用於顯示當前頁號、所有頁數。所以在進行查詢的同時，往往還需要對結果集中所有資料的總行數進行統計，並把之與查詢出的實體列表資料一同返回。所以，我為 PagingInfo 添加了額外的兩個屬性，IsNeedCount、TotalCount，當 IsNeedCount 被設定為真時，架構在資料層進行查詢時，會把統計出來的總行數賦值給 TotalCount。

 

OEA 分頁 - 使用方法

    下面以分頁查詢所有資料為例，簡單說明如何使用分頁查詢。先是應用程式層使用的代碼：

應用程式層需要構造 PagingInfo，並指定需要統計行數。查詢後，直接使用 PagingInfo.TotalCount。（這種介面方案從 06 年使用至今，比較好用。）

下面是 Repository 類型上的公有介面：

最後，再實現該查詢對應的資料層即可：

可以看到，在資料訪問層的 ORM 架構中，主要是在 IQuery 條件類型上添加了一個 Paging 方法。使用這個方法指定了 PagingInfo 後，即按給定的分頁資訊分頁查詢實體資料了。

OEA 中的資料層分頁實現 

    OEA 中用到的分頁有：介面層分頁、DataReader 分頁、資料庫分頁。

• 介面層分頁
  其實在 OEA 中就是 UI 虛擬化。相關內容，可以查看《OEA 中 WPF 樹型表格虛擬化設計方案》 及 《 精通 WPF UI Virtualization》。 

 

• 資料庫分頁（分頁SQL）

    目前，OEA 已經支援了 SqlServer 2005+、Oracle 10+、SqlCE4+，但是架構的設計目標則是應對所有資料庫（接下來很可能需要對 MySql 進行支援）。這三種資料庫中，OEA 只支援前兩種大型資料庫的資料庫分頁，主要是產生分頁 SQL 進行查詢。

    經過對比、挑選，我選用了一種可以在 SqlServer、Oracle 上的一種通用方案，即使用 RowNumber。例如，如果一個 SQL 查詢是：

select ...... from ...... order by xxxx asc, yyyy desc

，則只需要把它轉換為以下格式就行了：

select * from (select ......, row_number() over(order by xxxx asc, yyyy desc) _rowNumber from ......) x where x._rowNumber<10 and x._rowNumber>5 。

    同時，當需要統計總行數時，資料層會產生 SELECT COUNT(0) FROM ...... 的 SQL 陳述式重新進行查詢，並把結果賦值給 PagingInfo.TotalCount，以及 EntityList.TotalCount。

    在 SQLCE 中，並不支援 rowNumber 函數。所以只能考慮使用 NOT IN 的 SQL 方案。其實在OEA中，鑒於實現 NOT IN 方案比較麻煩，所以決定暫時使用 DataReader 完成 SQLCE 的記憶體分頁。

 

• DataReader 記憶體分頁 

    提供 DataReader 方案主要是簡單、同時還能與資料庫無關，解決跨庫問題。主要邏輯代碼如下：

/// <summary>
///使用 IDataReader 的記憶體分頁讀取方案。
///
///注意！！！
/// 此方法中會釋放 Reader。外層不能再用 Using。
/// </summary>
/// <param name="reader"></param>
/// <param name="rowReader">每一行資料，會調用此方法進行調取。</param>
/// <param name="pagingInfo">分頁資訊。如果這個參數不為空白，則使用其中描述的分頁規則進行記憶體分頁查詢。</param>
public static void MemoryPaging(IDataReader reader, Action<IDataReader> rowReader, PagingInfo pagingInfo = null)
{
    bool isPaging = pagingInfo != null;
    bool needCount = isPaging && pagingInfo.IsNeedCount;
    int totalCount = 0;
    int startRow = 1;//從一開始的行號
    int endRow = int.MaxValue;

    if (isPaging)
    {
        startRow = pagingInfo.PageSize * pagingInfo.PageIndex + 1;
        endRow = startRow + pagingInfo.PageSize - 1;
    }

    using (reader)
    {
        while (reader.Read())
        {
            totalCount++;

            if (totalCount >= startRow)
            {
                if (totalCount <= endRow)
                {
                    rowReader(reader);
                }
                else
                {
                    //如果已經超出該頁，而且需要統計行數，則直接快速迴圈到最後。
                    if (needCount)
                    {
                        while (reader.Read()) { totalCount++; }
                        break;
                    }
                }
            }
        }
    }

    if (needCount)
    {
        pagingInfo.TotalCount = totalCount;
    }
}

通用，又簡單。

 

待改進點

目前實現上，可能存在的缺陷是：

• 對分頁 SQL 的轉換不支援複雜的嵌套 SQL。這時可能出錯。

 

希望大夥拍磚。