在ASP.NET 2.0中操作數據之二十六：排序自定義分頁數據

導言

　　和默認翻頁方式相比，自定義分頁能提高幾個數量級的效率。當我們的需要對大量數據分頁的時候就需要考慮自定義分頁，然而實現自定義分頁相比默認分頁需要做更多工作。對于排序自定義分頁數據也是這樣，在本教程中我們就會擴展前面的例子來實現自定義分頁數據的排序。

　　注意：既然本教程是基于前一個的，因此我們需要把前面教程示例頁面EfficientPaging.aspx的<asp:Content>元素中的代碼復制到本教程SortParameter.aspx示例頁面中。關于如何進行這樣的復制操作請參看為刪除數據添加客戶端確認

Step 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輸入參數，使用如下任意一種方法：

　　為所有的排序表達式硬編碼查詢，使用IF/ELSE T-SQL語句來決定執行哪個查詢
使用CASE語句來根據輸入參數@sortExpression實現動態ORDER BY表達式，詳細請看The Power of SQL CASE Statements中的Used to Dynamically Sort Query Results部分。

　　使用字符串來保存查詢語句然后使用sp_executesql系統存儲過程來動態執行查詢

　　上述每一種實現方法都有各自的缺點。第一個方案和其余兩個相比可維護性比較差，因為它需要為每一個可能的查新表達式創建一句查詢。因此，如果你又在GridView中加入了一個允許排序的字段，還需要去修改存儲過程。對于第二個方案如果我們的數據庫列不是字符串類型的話，排序就會引發一定的效率問題，而且可維護性和第一種一個一樣也不是很好。至于最后一個動態組合SQL語句的方案，如果你允許用戶自己輸入參數并傳入存儲過程的話則可能帶來SQL注入攻擊的危害。

　　雖然沒有一種方案是完美的，但是我認識第三種是這三個方案中最佳的。因為它是使用動態SQL語句的，所以靈活性比前兩者都好。而且，只有當攻擊者能隨意把參數傳入存儲過程才能進行SQL注入攻擊。既然DAL使用參數化查詢，ADO.NET會防止這些惡意參數傳入數據庫，也就是說只有當攻擊者人直接執行存儲過程的時候才會有SQL注入的隱患。

　　要實現這個功能，讓我們在Northwind數據庫中新建稱作GetProductsPagedAndSorted的一個存儲過程。這個存儲過程接受三個參數：@sortExpression，nvarchar(100)類型的輸入參數，用來指定排序方式，它會直接拼接在ORDER BY子句后面。@startRowIndex 和 @maximumRows都是整數輸入參數，和前面教程中的一樣。你可以參考下面的腳本建立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表獲取所有行的查詢有些不同。在前面的例子中，我們使用子查詢獲取每一個產品關聯的分類和供應商名。在GetProductsPagedAndSorted中我們只能使用JOINS因為結果需要根據分類或者供應商名來排序。

　　我們通過連接靜態的查詢語句和@sortExpression, @startRowIndex, @maximumRows參數來組成動態查詢。因為@startRowIndex和@maximumRows是整數參數，所以必須在連接前把它們轉化為nvarchar類型。在動態SQL查詢連接完畢后就可以使用sp_executesql來執行。

　　先來花一些時間使用各種@sortExpression、@startRowIndex和@maximumRows參數的值來測試存儲過程。在服務器資源管理器中右鍵點擊存儲過程然后選擇執行。IDE會啟動運行存儲過程對話框，我們輸入各種輸入參數（見圖1）。比如，要讓結果按照分類名排序，就把@sortExpression參數的值設置為CategoryName；如果要按照公司名排序就用CompanyName。所有參數的值都正確設置后點擊OK。結果就會在輸出窗口中顯示。圖2顯示了按照UnitPrice倒序，從11到20的記錄。

圖1：試著設置存儲過程的三個輸入參數

圖2：存儲過程的結果顯示在了輸入窗口中

Step 2: 添加數據訪問和業務邏輯層

既然我們已經建立了GetProductsPagedAndSorted存儲過程，下一步就是要通過我們的應用程序構架來執行它。我們需要為DAL和BLL添加一個正確的方法。首先讓我們為DAL添加一個方法。打開Northwind.xsd強類型DataSet，右鍵點擊ProductsTableAdapter，從菜單中選擇添加查詢選項。和前面教程中做的一樣，我們需要配置一個新的DAL方法來使用建立的存儲過程－GetProductsPagedAndSorted。選擇使用已有存儲過程選項。

圖3：選擇一個已有的存儲過程

在下一步中，我們通過從下拉列表中選擇GetProductsPagedAndSorted存儲過程來使用它。

圖4：使用GetProductsPagedAndSorted存儲過程

在下一屏幕中，我們選擇它返回表格信息。

圖5：指示存儲過程返回表格信息

最后，我們創建DAL方法來填充DataTable和返回DataTable，分別命名為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);
}

Step 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開啟排序，請首先檢查Sorting多選框是否已經選中。把GridView的AllowSorting屬性設置為true以后就能讓每列的標題文字呈現為LinkButton。用戶點擊標題的LinkButton就會引發如下幾個步驟：

1.GridView把它的SortExpression 屬性的值修改為當前點擊的標題所在列的SortExpression的值。

2.ObjectDataSource調用BLL的GetProductsPagedAndSorted方法，把GridView的SortExpression屬性的值作為sortExpression參數傳入方法（還有正確的startRowIndex、maximumRows輸入參數的值）。

3.BLL調用DAL的GetProductsPagedAndSorted方法。

4.DAL執行GetProductsPagedAndSorted存儲過程并傳入@sortExpression參數（和@startRowIndex、@maximumRows輸入參數）。

5.存儲過程把正確的記錄子集數據返回BLL，BLL返回到ObjectDataSource；數據被綁定到GridView之后渲染成HTML顯示給用戶。

圖7顯示了按照UnitPrice正序排列地第一頁記錄集。

圖7：按照UnitPrice排列的果

雖然現在我們的程序能正確按照產品名、分類名、位數量和價格進行排序，但是如果我們選擇按照供應商名來排序會得到一個運行時異常，如圖8。

圖8：按照供應商名排序會得到一個運行時異常

之所以會引發這個異常時因為GridView的SupplierName BoundField綁定列的SortExpression設置為SupplierName。然而，這列在供應商表中實際叫做CompanyName，SupplierName是我們為這個列起的別名。因為ROW_NUMBER()功能只能使用真實列名，所以，我們需要把BoundField的SortExpression從“SupplierName”修改為“CompanyName”（如圖9），圖10顯示了修改后按照供應商排序的記錄。

圖9：把SupplierName BoundField的SortExpression修改為“CompanyName” （譯者注：圖片可能不對）

圖10：結果現在能按照供應商名排序了

總結

前面教程中我們實現了自定義分頁，只能在設計時固定一種排序方式。簡單來說要想又自定義分頁又提供自定義排序實現不了。在本教程中，我們通過引入@sortExpression來擴展存儲過程解決了這個限制。

在創建了存儲過程和DAL、BLL中的新方法后，我們就能通過配置ObjectDataSource把GridView當前SortExpression的值傳入BLL的SelectMethod中來實現排序和自定義分頁。

編程快樂！

關于作者

Scott Mitchell，著有六本ASP/ASP.NET方面的書，是4GuysFromRolla.com的創始人，自1998年以來一直應用微軟Web技術。Scott是個獨立的技 術咨詢顧問，培訓師，作家，最近完成了將由Sams出版社出版的新作，24小時內精通ASP.NET 2.0。他的聯系電郵為mitchell@4guysfromrolla.com，也可以通過他的博客http://ScottOnWriting.NET與他聯系。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com