排序自訂的分頁資料 (VB)

發行項
03/05/2024

在上一個教學課程中，我們已瞭解如何在網頁上呈現數據時實作自定義分頁。在本教學課程中，我們將瞭解如何擴充上述範例，以包含排序自定義分頁的支援。

簡介

相較於預設分頁，自定義分頁可以藉由數個數量級來改善分頁的效能，讓自定義分頁在分頁大量數據時，選擇事實分頁實作。不過，實作自定義分頁比實作默認分頁更為相關，特別是在將排序新增至混合時。在本教學課程中，我們將擴充上述範例，以包含排序和自定義分頁的支援。

注意

由於本教學課程是以上述教學課程為基礎，因此在開始之前，請先花點時間從上一個教學課程網頁的元素中<asp:Content>複製宣告式語法，EfficientPaging.aspx () 並貼到SortParameter.aspx頁面的元素之間<asp:Content>。請參閱將驗證控件新增至編輯和插入介面教學課程的步驟 1，以取得將一個 ASP.NET 頁面的功能複寫到另一個頁面的更詳細討論。

步驟 1：重新探索自定義分頁技術

若要讓自定義分頁正常運作，我們必須實作一些技術，以有效率地擷取特定子集的記錄，指定起始數據列索引和最大數據列參數。有一些技術可用來達成此目標。在上述教學課程中，我們已瞭解如何使用 Microsoft SQL Server 2005 的新ROW_NUMBER()排名函式來完成這項作業。簡單地說， ROW_NUMBER() 排名函式會將數據列編號指派給依指定排序順序排序的查詢所傳回的每個數據列。然後傳回編號結果的特定區段，即可取得適當的記錄子集。下列查詢說明如何使用這項技術，在依字母順序 ProductName排序結果時，傳回編號為 11 到 20 的產品：

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY ProductName) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

這項技術適用於使用特定排序順序 (ProductName 依字母順序排序的分頁，在此情況下) ，但查詢必須經過修改，才能顯示以不同排序表達式排序的結果。在理想情況下，上述查詢可以重寫為在子句中使用 OVER 參數，如下所示：

SELECT ProductID, ProductName, ...
FROM
   (SELECT ProductID, ProductName, ..., ROW_NUMBER() OVER
    (ORDER BY @sortExpression) AS RowRank
    FROM Products) AS ProductsWithRowNumbers
WHERE RowRank > 10 AND RowRank <= 20

不幸的是，不允許參數化 ORDER BY 子句。相反地，我們必須建立可接受 @sortExpression 輸入參數的預存程式，但會使用下列其中一個因應措施：

針對每個可能使用的排序表示式撰寫硬式編碼查詢;然後，使用 IF/ELSE T-SQL 語句來判斷要執行的查詢。
使用語句根據 n 輸入 CASE 參數提供動態 ORDER BY 表達式 @sortExpressio ;如需詳細資訊，請參閱 T-SQL CASE 語句中用來動態排序查詢結果一節。
將適當的查詢製作為預存程式中的字串，然後使用sp_executesql系統預存程式來執行動態查詢。

上述每個因應措施都有一些缺點。第一個選項不如其他兩個選項可維護，因為您需要為每個可能的排序表示式建立查詢。因此，如果您稍後決定將新的可排序欄位新增至 GridView，您也需要返回並更新預存程式。第二種方法有一些細微之處，會在依非字串數據列排序時引入效能考慮，而且也會受到與第一個相同的可維護性問題所影響。而第三個選項會使用動態 SQL，如果攻擊者能夠執行預存程式，以傳入所選輸入參數值的輸入參數值，就會造成 SQL 插入式攻擊的風險。

雖然這些方法都不是完美的，但我認為第三個選項是這三種方法的最佳選項。使用動態 SQL 時，它提供其他兩者不具彈性的層級。此外，如果攻擊者能夠執行傳入所選輸入參數的預存程式，則只能利用 SQL 插入式攻擊。由於 DAL 使用參數化查詢，ADO.NET 會透過架構保護傳送至資料庫的參數，這表示只有在攻擊者可以直接執行預存程式時，才會存在 SQL 插入式攻擊弱點。

若要實作這項功能，請在名為 GetProductsPagedAndSorted的 Northwind 資料庫中建立新的預存程式。此預存程式應該接受三個輸入參數：、@sortExpression類型為) 的輸入參數，指定結果的排序方式，並直接插入子句中的OVER文字;ORDER BY以及和 @startRowIndex@maximumRows，與上一個教學課程中所檢查預存程式中的相同兩個整數輸入參數GetProductsPagednvarchar(100 。使用下列文稿建立 GetProductsPagedAndSorted 預存程式：

CREATE PROCEDURE dbo.GetProductsPagedAndSorted
(
    @sortExpression nvarchar(100),
    @startRowIndex int,
    @maximumRows int
)
AS
-- Make sure a @sortExpression is specified
IF LEN(@sortExpression) = 0
    SET @sortExpression = 'ProductID'
-- Issue query
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,
                   UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
                   CategoryName, SupplierName
            FROM (SELECT ProductID, ProductName, p.SupplierID, p.CategoryID,
                         QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
                         ReorderLevel, Discontinued,
                  c.CategoryName, s.CompanyName AS SupplierName,
                   ROW_NUMBER() OVER (ORDER BY ' + @sortExpression + ') AS RowRank
            FROM Products AS p
                    INNER JOIN Categories AS c ON
                        c.CategoryID = p.CategoryID
                    INNER JOIN Suppliers AS s ON
                        s.SupplierID = p.SupplierID) AS ProductsWithRowNumbers
            WHERE     RowRank > ' + CONVERT(nvarchar(10), @startRowIndex) +
                ' AND RowRank <= (' + CONVERT(nvarchar(10), @startRowIndex) + ' + '
                + CONVERT(nvarchar(10), @maximumRows) + ')'
-- Execute the SQL query
EXEC sp_executesql @sql

預存程式一開始會確保已指定參數的值 @sortExpression 。如果遺漏，結果會依 ProductID排名。接下來，會建構動態 SQL 查詢。請注意，此處的動態 SQL 查詢與先前用來從 Products 數據表擷取所有數據列的查詢稍有不同。在先前的範例中，我們使用子查詢取得每個產品相關聯的類別和供應商名稱。此決策是在建立數據存取層教學課程中做出的，但已取代使用 JOIN ，因為 TableAdapter 無法自動建立這類查詢的相關插入、更新和刪除方法。不過，預 GetProductsPagedAndSorted 存程式必須針對依類別或供應商名稱排序的結果使用 JOIN 。

此動態查詢的建置方式是串連靜態查詢部分和 @sortExpression、 @startRowIndex和 @maximumRows 參數。由於 @startRowIndex 和 @maximumRows 是整數參數，因此必須轉換成 nvarchars，才能正確串連。建構此動態 SQL 查詢之後，即會透過 sp_executesql執行。

請花點時間測試此預存程式，並針對、 @startRowIndex和 @maximumRows 參數使用不同的值@sortExpression。從 [伺服器總管] 中，以滑鼠右鍵按下預存程式名稱，然後選擇 [執行]。這會顯示 [執行預存程式] 對話框，您可以在其中輸入輸入參數 (請參閱圖 1) 。若要依類別名稱排序結果，請使用 CategoryName 做為 @sortExpression 參數值;若要依供貨商的公司名稱排序，請使用 CompanyName。提供參數值之後，按兩下 [確定]。結果會顯示在 [輸出] 視窗中。圖 2 顯示以遞減順序排序 UnitPrice 時，傳回排名為 11 到 20 的產品時的結果。

針對預存程式的三個輸入參數嘗試不同的值

圖 1：針對預存程式三個輸入參數嘗試不同的值

圖 2：預存程式的結果會顯示在 [輸出] 視窗中， (按兩下即可檢視完整大小的影像)

注意

依子句中OVER指定ORDER BY數據行排序結果時，SQL Server 必須排序結果。如果數據行上有叢集索引， () 排序結果，或有涵蓋索引，但成本可能更高，則這是快速作業。若要改善足夠大型查詢的效能，請考慮為排序結果的數據行新增非叢集索引。如需詳細資訊，請參閱 SQL Server 2005 中的排名函式和效能。

步驟 2：增強數據存取和商業規則層

建立預存程序之後 GetProductsPagedAndSorted ，下一個步驟是提供透過應用程式架構執行該預存程式的方法。這需要將適當的方法新增至 DAL 和 BLL。讓我們從將方法新增至 DAL 開始。開啟 [ Northwind.xsd 具類型的數據集]，以滑鼠右鍵按兩下 ProductsTableAdapter，然後從操作功能表選擇 [新增查詢] 選項。如同我們在上一個教學課程中所做的一樣，我們想要設定這個新的 DAL 方法，以使用現有的預存程式 - GetProductsPagedAndSorted在此案例中為。首先，表示您想要新的 TableAdapter 方法使用現有的預存程式。

選擇使用現有的預存程式

圖 3：選擇使用現有的預存程式

若要指定要使用的預存程式，請從下一個畫面的下拉式清單中選取 GetProductsPagedAndSorted 預存程式。

使用 GetProductsPagedAndSorted 預存程式

圖 4：使用 GetProductsPagedAndSorted 預存程式

這個預存程式會傳回一組記錄做為其結果，因此在下一個畫面中，指出它會傳回表格式數據。

指出預存程式會傳回表格式數據

圖 5：指出預存程式會傳回表格式數據

最後，建立使用 Fill a DataTable 和 Return a DataTable 模式的 DAL 方法，分別命名方法和 FillPagedAndSortedGetProductsPagedAndSorted。

選擇方法名稱

圖 6：選擇方法名稱

既然我們已擴充 DAL，我們就可以開始前往 BLL。開啟類別 ProductsBLL 檔案，並新增方法 GetProductsPagedAndSorted。此方法必須接受三個輸入參數 sortExpression、 startRowIndex和 maximumRows ，而且應該直接呼叫 DAL s GetProductsPagedAndSorted 方法，如下所示：

<System.ComponentModel.DataObjectMethodAttribute( _
    System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductsPagedAndSorted(ByVal sortExpression As String, _
    ByVal startRowIndex As Integer, ByVal maximumRows As Integer) _
    As Northwind.ProductsDataTable
    Return Adapter.GetProductsPagedAndSorted(sortExpression, startRowIndex, maximumRows)
End Function

步驟 3：設定 ObjectDataSource 以傳入 SortExpression 參數

增強 DAL 和 BLL 以包含利用 GetProductsPagedAndSorted 預存程式的方法，剩下的一切都是將頁面中的 ObjectDataSource SortParameter.aspx 設定為使用新的 BLL 方法，並根據使用者要求排序結果依據的數據行傳入 SortExpression 參數。

從將 ObjectDataSource s SelectMethod 從 GetProductsPaged 變更為 GetProductsPagedAndSorted開始。這可以透過設定數據源精靈、從屬性視窗，或直接透過宣告式語法來完成。接下來，我們需要提供 ObjectDataSource s SortParameterName 屬性的值。如果已設定此屬性，ObjectDataSource 會嘗試將 GridView 的 SortExpression 屬性 SelectMethod傳入。特別是，ObjectDataSource 會尋找名稱等於屬性值的 SortParameterName 輸入參數。由於 BLL s GetProductsPagedAndSorted 方法具有名為 sortExpression的排序表示式輸入參數，因此請將 ObjectDataSource s SortExpression 屬性設定為 sortExpression 。

進行這兩項變更之後，ObjectDataSource 的宣告式語法看起來應該如下所示：

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    OldValuesParameterFormatString="original_{0}" TypeName="ProductsBLL"
    SelectMethod="GetProductsPagedAndSorted" EnablePaging="True"
    SelectCountMethod="TotalNumberOfProducts" SortParameterName="sortExpression">
</asp:ObjectDataSource>

注意

如同上述教學課程，請確定 ObjectDataSource 在其 SelectParameters 集合中 不包含 sortExpression、startRowIndex 或 maximumRows 輸入參數。

若要在 GridView 中啟用排序，只要核取 GridView 智慧標記中的 [啟用排序] 複選框，即可將 GridView 的 AllowSorting 屬性 true 設定為，並導致每個數據行的標頭文字轉譯為 LinkButton。當使用者按兩下其中一個標頭 LinkButtons 時，回傳會接著執行下列步驟：

GridView 會將其SortExpression屬性更新為已按下標頭連結之欄位的值SortExpression
ObjectDataSource 會叫用 BLL s 方法，傳入 GridView s GetProductsPagedAndSortedSortExpression 屬性做為方法 sortExpression 輸入參數的值， (以及適當的 startRowIndex 和 maximumRows 輸入參數值)
BLL 會叫用 DAL s GetProductsPagedAndSorted 方法
DAL 會 GetProductsPagedAndSorted 執行預存程式，並傳入 @sortExpression 參數 (@startRowIndex@maximumRows 和輸入參數值)
預存程式會將適當的數據子集傳回至 BLL，而該子集會將它傳回至 ObjectDataSource;此數據接著會系結至 GridView、轉譯為 HTML，並向下傳送給終端使用者

圖 7 顯示以遞增順序排序 UnitPrice 時的結果第一頁。

圖 7：結果會依 UnitPrice 排序 (按兩下以檢視大小完整的影像)

雖然目前的實作可以正確地依產品名稱、類別名稱、單位數量和單價排序結果，但嘗試依供應商名稱排序結果會導致運行時間例外狀況 (請參閱圖 8) 。

在下列運行時間例外狀況中，嘗試依供應商結果排序結果

圖 8：在下列運行時間例外狀況中嘗試依供貨商結果排序結果

發生這個例外狀況的原因是 SortExpression GridView s SupplierName BoundField 的設定為 SupplierName。不過，資料表中的 Suppliers 供應商名稱實際上 CompanyName 稱為，我們已將此資料行名稱別名為 SupplierName。不過，函 OVER 式所使用的 ROW_NUMBER() 子句無法使用別名，而且必須使用實際的數據行名稱。因此，將 SupplierName BoundField s SortExpression 從 SupplierName 變更為 CompanyName (請參閱圖 9) 。如圖 10 所示，在這項變更之後，可依供應商排序結果。

將 SupplierName BoundField s SortExpression 變更為 CompanyName

圖 9：將 SupplierName BoundField s SortExpression 變更為 CompanyName

圖 10：結果現在可以依供貨商排序 (按兩下以檢視大小完整的影像)

摘要

我們在上一個教學課程中檢查的自定義分頁實作，需要在設計時間指定排序結果的順序。簡單來說，這表示我們實作的自定義分頁實作無法同時提供排序功能。在本教學課程中，我們會藉由從第一個 @sortExpression 延伸預存程式來包含可排序結果的輸入參數，來克服這項限制。

在 DAL 和 BLL 中建立這個預存程式並建立新的方法之後，我們可以實作 GridView，藉由設定 ObjectDataSource 將 GridView 的目前 SortExpression 屬性傳遞至 BLL SelectMethod來提供排序和自定義分頁。

快樂的程序設計！

關於作者

Scott Mitchell 是七份 ASP/ASP.NET 書籍的作者，以及自 1998 年以來與 Microsoft Web 技術合作的 4GuysFromRolla.com 作者。 Scott 是獨立顧問、訓練員和作者。他的最新書籍是 Sams 在 24 小時內自行 ASP.NET 2.0。您可以透過mitchell@4GuysFromRolla.com部落格來連線到，您可以在找到http://ScottOnWriting.NET。

特別感謝

本教學課程系列是由許多實用的檢閱者檢閱。本教學課程的首席檢閱者是 Carlos Reviewers。有興趣檢閱即將推出的 MSDN 文章嗎？如果是，請將一行 mitchell@4GuysFromRolla.com放在。

上一個下一個

共用方式為