排序自訂的分頁資料 (C#)
演講者：Scott Mitchell
在上一個教學課程中，我們已了解如何在網頁上呈現資料時實作自訂分頁。 在本教學課程中，我們將了解如何擴展前面的範例，以加入對排序自訂分頁的支援。
簡介
與預設分頁相比，自訂分頁可以顯著提高資料分頁的效能，提升數量級，使得在處理大量資料時，自訂分頁成為實務上的最佳實作選擇。 然而，實作自訂分頁比實作預設分頁更複雜，尤其是在新增排序時。 在本教學課程中，我們將擴展前一個範例，以包括對排序和自訂分頁的支援。
注意
由於本教學課程建立在前一個教學課程的基礎上，因此在開始之前請花一些時間從前一個教學課程的網頁 (EfficientPaging.aspx) 複製 <asp:Content> 元素內的宣告性語法，並將其貼到 SortParameter.aspx 頁面中的 <asp:Content> 元素之間。 如需將 ASP.NET 頁面的功能複製到另一個頁面的更詳細討論，請參閱「在編輯和插入介面中新增驗證控制項」教學課程的步驟 1。
步驟 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/ELSET-SQL 陳述式來決定要執行哪個查詢。 - 使用
CASE陳述式來根據@sortExpressio輸入參數提供動態ORDER BY運算式；有關詳細資訊，請參閱 T-SQLCASE陳述式中的用於動態排序查詢結果部分。 - 將適當的查詢製作為預存程序中的字串，然後使用
sp_executesql系統預存程序來執行動態查詢。
這些解決方法都有一些缺點。 第一個選項不像其他兩個選項那樣可維護，因為它要求您為每個可能的排序運算式建立一個查詢。 因此，如果稍後您決定向 GridView 新增新的可排序欄位，您還需要返回並更新預存程序。 第二種方法有一些微妙之處，在按非字串資料庫列排序時會導致效能問題，並且還存在與第一種方法相同的可維護性問題。 第三種選擇是使用動態 SQL。如果攻擊者能夠執行傳入其選擇的輸入參數值的預存程序，將會帶來 SQL 注入攻擊的風險。
雖然這些方法都不完美，但我認為第三種選擇是三者中最好的。 透過使用動態 SQL，它提供了其他兩者所沒有的靈活性。 此外，只有當攻擊者能夠執行傳入其選擇的輸入參數的預存程序時，才能利用 SQL 注入攻擊。 由於 DAL 使用參數化查詢，ADO.NET 將透過架構保護那些傳送到資料庫的參數，這代表只有當攻擊者可以直接執行預存程序時，SQL 注入攻擊漏洞才存在。
若要實作此功能，請在 Northwind 資料庫中建立名為 GetProductsPagedAndSorted 的新預存程序。 此預存程序應接受三個輸入參數：@sortExpression，一個類型為 nvarchar(100 的輸入參數，指定結果的排序方式，並直接注入到 OVER 子句的 ORDER BY 文字之後；@startRowIndex和 @maximumRows，這是與前面教學課程中檢查的 GetProductsPaged 預存程序中相同的兩個整數輸入參數。 使用以下指令碼建立 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 是整數參數，因此必須將它們轉換為 nvarchar 才能正確連接。 建構此動態 SQL 查詢後，將透過 sp_executesql 執行。
花點時間使用不同的 @sortExpression、@startRowIndex 和 @maximumRows 參數值來測試此預存程序。 在伺服器總管中，以滑鼠右鍵按一下預存程序名稱並選擇「執行」。 這將開啟「執行預存程序」對話方塊，您可以在其中輸入輸入參數 (請參閱圖 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 預存程序。
圖 4：使用 GetProductsPagedAndSorted 預存程序
此預存程序會傳回一組記錄作為其結果，因此在下一個畫面中指示它會傳回表格資料。
圖 5：指示預存程序傳回表格資料
最後，建立同時使用「填充資料表」和「返回資料表」模式的 DAL 方法，分別將方法命名為 FillPagedAndSorted 和 GetProductsPagedAndSorted。
圖 6：選擇方法名稱
現在我們已經擴展了 DAL，我們準備好轉向 BLL。 開啟 ProductsBLL 類別檔案並新增方法 GetProductsPagedAndSorted。 此方法需要接受三個輸入參數 sortExpression、startRowIndex 和 maximumRows，並且應該簡單地呼叫 DAL GetProductsPagedAndSorted 方法，如下所示：
[System.ComponentModel.DataObjectMethodAttribute(
System.ComponentModel.DataObjectMethodType.Select, false)]
public Northwind.ProductsDataTable GetProductsPagedAndSorted(
string sortExpression, int startRowIndex, int maximumRows)
{
return Adapter.GetProductsPagedAndSorted
(sortExpression, startRowIndex, maximumRows);
}
步驟3：設定 ObjectDataSource 傳入 SortExpression 參數
擴充了 DAL 和 BLL 以包含利用 GetProductsPagedAndSorted 預存程序的方法後，剩下的就是在 SortParameter.aspx 頁面中設定 ObjectDataSource 以使用新的 BLL 方法，並根據使用者請求的欄傳入 SortExpression 參數以對資料進行排序。
先將 ObjectDataSource SelectMethod 從 GetProductsPaged 改為 GetProductsPagedAndSorted。 這可以透過設定資料來源精靈、屬性視窗或直接透過宣告性語法來完成。 接下來，我們需要為 ObjectDataSource 的 SortParameterName 屬性 提供一個值。 如果設定了此屬性，則 ObjectDataSource 會嘗試將 GridView 的 SortExpression 屬性傳遞給 SelectMethod。 特別是，ObjectDataSource 會尋找名稱與 SortParameterName 屬性值相同的輸入參數。 由於 BLL 的 GetProductsPagedAndSorted 方法具有名為 sortExpression 的排序運算式輸入參數，因此將 ObjectDataSource 的 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 s 智慧標籤中的啟用排序核取方塊，該核取方塊將 GridView 的 AllowSorting 屬性設為 true，並導致每列的標題文字呈現為 LinkButton。 當終端使用者點擊標題 LinkButton 之一時，會發生回傳並執行以下步驟：
- GridView 將其
SortExpression屬性 更新為點擊標題連結欄位的SortExpression值 - ObjectDataSource 呼叫 BLL 的
GetProductsPagedAndSorted方法，將 GridView 的SortExpression屬性作為方法的sortExpression輸入參數的值傳遞 (以及適當的startRowIndex和maximumRows輸入參數值) - BLL 叫用 DAL 的
GetProductsPagedAndSorted方法 - DAL 執行
GetProductsPagedAndSorted預存程序，傳入@sortExpression參數 (以及@startRowIndex和@maximumRows輸入參數值) - 預存程序將適當的資料子集傳回給 BLL，BLL 將其傳回給 ObjectDataSource；然後，該資料會繫結到 GridView，呈現為 HTML，並傳送給終端使用者
圖 7 顯示了按照 UnitPrice 遞增排序的第一頁結果。
圖 7：結果會按照 UnitPrice 排序 (點擊查看完整圖片)
雖然目前的實作可以按產品名稱、類別名稱、單位數量和單價正確對結果進行排序，但嘗試按供應商名稱對結果進行排序會導致執行階段例外狀況 (請參閱圖 8)。
圖 8：嘗試按照供應商對結果進行排序會導致以下執行階段例外狀況
出現此例外狀況的原因是 GridView 的 SortExpression BoundField 的 SupplierName 設定為 SupplierName。 然而，Suppliers 表中的供應商名稱實際上稱為 CompanyName，我們將此列名稱別名為 SupplierName。 但是，ROW_NUMBER() 函式使用的 OVER 子句不能使用別名，必須使用實際的欄名稱。 因此，將 SupplierName BoundField 的 SortExpression 從 SupplierName 更改為 CompanyName (參見圖 9)。 如圖 10 所示，進行此變更後，供應商可以對結果進行排序。
圖 9：將SupplierName BoundField s SortExpression 改為 CompanyName
圖 10：結果現在可以依供應商排序 (點擊查看完整圖片)
摘要
我們在前面的教學課程中研究的自訂分頁實作要求在設計時指定結果的排序順序。 簡而言之，這代表我們實作的自訂分頁實作無法同時提供排序功能。 在本教學課程中，我們透過擴展預存程序以包含可對結果進行排序的 @sortExpression 輸入參數，從而克服了此限制。
建立此預存程序並在 DAL 和 BLL 中建立新方法後，我們能夠透過設定 ObjectDataSource 將 GridView 的目前 SortExpression 屬性傳遞給 BLL SelectMethod 來實作提供排序和自訂分頁的 GridView。
快樂程式！
關於作者
Scott Mitchell 是七本 ASP/ASP.NET 書籍的作者，也是 4GuysFromRolla.com 的創辦人，自 1998 年以來一直在使用 Microsoft 網路技術。 Scott 是一位獨立顧問、培訓師兼作家。 他的最新著作是 Sams Teach Yourself ASP.NET 2.0 in 24 Hours。 您可以透過電子郵件聯繫他：mitchell@4GuysFromRolla.com。 或者透過他的部落格與他聯繫，網址為 http://ScottOnWriting.NET。
特別感謝
本教學課程系列已經過許多熱心的審閱者檢閱。 本教學課程的主要審閱者是 Carlos Santos。 有興趣查看我即將發表的 MSDN 文章嗎？ 如果是的話，請傳送郵件給我：mitchell@4GuysFromRolla.com。