ASP.NET 2.0 數據教程（二十六）：自訂分頁資料排序實作與儲存程序動態 ORDER BY 技巧

返回 ASP.NET 2.0 數據教程目錄

導言

相比預設分頁，自訂分頁能提升數個數量級效率。大量資料分頁需考慮自訂分頁，但實作較預設分頁更複雜。排序自訂分頁資料同理，本教程將擴充前例實作自訂分頁資料排序。

注意： 本教程基於前一教程，需將 EfficientPaging.aspx 的 <asp:Content> 程式碼複製至本教程 SortParameter.aspx。複製操作參考 為刪除數據添加客戶端確認。

步驟 1：回顧自訂分頁技術

實現自訂分頁需根據起始列索引與最大列數參數回傳記錄子集的方法。前教程探討使用 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 輸入參數，採用以下任一方法：

1. 為所有排序運算式硬編碼查詢，用 IF/ELSE T-SQL 決定執行哪個查詢
2. 用 CASE 陳述式依 @sortExpression 動態實現 ORDER BY 運算式，詳見 The Power of SQL CASE Statements 中的 Used to Dynamically Sort Query Results 章節
3. 用字串保存查詢語句再用 sp_executesql 系統儲存程序動態執行

每種方法皆有缺點。方案一維護性較差，需為每可能查詢運算式建立查詢；若 GridView 新增可排序欄位仍需修改儲存程序。方案二若資料庫欄位非字串類型排序有效能問題，維護性同樣不佳。方案三動態組合 SQL 若允許用戶輸入參數傳入儲存程序可能引發 SQL 注入風險。

雖無完美方案，但我認為第三種最佳：動態 SQL 靈活性最好；SQL 注入僅當攻擊者能隨意傳參數給儲存程序才可能。DAL 使用參數化查詢，ADO.NET 會阻擋惡意參數進入資料庫，除非攻擊者直接執行儲存程序才有隱患。

實作：建立 GetProductsPagedAndSorted 儲存程序

在 Northwind 資料庫建立 GetProductsPagedAndSorted 儲存程序，接受三參數：

• @sortExpression，nvarchar(100) 輸入參數，指定排序方式，直接拼接在 ORDER BY 後
• @startRowIndex、@maximumRows 整數輸入參數，同前教程

參考下列腳本建立儲存程序：