在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 ProductsWithRowNumbersWHERE 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 ProductsWithRowNumbersWHERE 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 specifiedIF LEN(@sortExpression) = 0 SET @sortExpression = 'ProductID'-- Issue queryDECLARE @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 queryEXEC 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與他聯絡。