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

導(dǎo)言

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

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

Step 1: 回顧自定義分頁技術(shù)

　　要實現(xiàn)自定義分頁，我們需要使用一些方法根據(jù)開始行索引和最大行參數(shù)返回一個記錄的子集。在前面的教程中，我們看了如何使用微軟SQL SERVER 2005的ROW_NUMBER()來實現(xiàn)。簡而言之，ROW_NUMBER()為每一個查詢返回的行分配一個行號。下面這個查詢演示了如何使用這個技術(shù)按照ProductName排序獲取的11至20的產(chǎn)品數(shù)據(jù)。

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

　　對于按照一種固定的排序規(guī)則進(jìn)行分頁，上述技術(shù)就能滿足了（比如按照ProductName排序），但是如果我們希望獲取按照不同的排序表達(dá)式排序后的記錄，理想地，我們應(yīng)該在OVER子句中使用參數(shù)重寫上述查詢，代碼如下：

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子句中不能使用參數(shù)。而我們只能創(chuàng)建存儲過程來接受@sortExpression輸入?yún)?shù)，使用如下任意一種方法：

　　為所有的排序表達(dá)式硬編碼查詢，使用IF/ELSE T-SQL語句來決定執(zhí)行哪個查詢
使用CASE語句來根據(jù)輸入?yún)?shù)@sortExpression實現(xiàn)動態(tài)ORDER BY表達(dá)式，詳細(xì)請看The Power of SQL CASE Statements中的Used to Dynamically Sort Query Results部分。

　　使用字符串來保存查詢語句然后使用sp_executesql系統(tǒng)存儲過程來動態(tài)執(zhí)行查詢

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

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

　　要實現(xiàn)這個功能，讓我們在Northwind數(shù)據(jù)庫中新建稱作GetProductsPagedAndSorted的一個存儲過程。這個存儲過程接受三個參數(shù)：@sortExpression，nvarchar(100)類型的輸入?yún)?shù)，用來指定排序方式，它會直接拼接在ORDER BY子句后面。@startRowIndex 和 @maximumRows都是整數(shù)輸入?yún)?shù)，和前面教程中的一樣。你可以參考下面的腳本建立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參數(shù)的值已經(jīng)被指定。如果未被指定則按照ProductID排序。接下來，開始構(gòu)建動態(tài)的SQL查詢。注意到，在這里的動態(tài)SQL查詢和前面的用來從Products表獲取所有行的查詢有些不同。在前面的例子中，我們使用子查詢獲取每一個產(chǎn)品關(guān)聯(lián)的分類和供應(yīng)商名。在GetProductsPagedAndSorted中我們只能使用JOINS因為結(jié)果需要根據(jù)分類或者供應(yīng)商名來排序。

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

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

圖1：試著設(shè)置存儲過程的三個輸入?yún)?shù)

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

Step 2: 添加數(shù)據(jù)訪問和業(yè)務(wù)邏輯層

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

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

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

圖4：使用GetProductsPagedAndSorted存儲過程

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

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

最后，我們創(chuàng)建DAL方法來填充DataTable和返回DataTable，分別命名為FillPagedAndSorted和GetProductsPagedAndSorted。

圖6：選擇方法名

現(xiàn)在，我們已經(jīng)擴(kuò)展了DAL，讓我們來看看BLL吧。打開ProductsBLL類文件并且新增一個方法GetProductsPagedAndSorted。這個方法接受三個參數(shù)－sortExpression，startRowIndex和maximumRows。僅僅是簡單地調(diào)用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參數(shù)

　　好了，我們已經(jīng)為DAL和BLL添加了方法來調(diào)用GetProductsPagedAndSorted存儲過程。剩下的工作就是配置SortParameter.aspx頁面的ObjectDataSource來根據(jù)用戶請求的排序為新的BLL方法傳入SortExpression參數(shù)。

　　首先，我們把ObjectDataSource的SelectMethod從GetProductsPaged修改為GetProductsPagedAndSorted?？梢酝ㄟ^配置數(shù)據(jù)源向?qū)У膶傩源翱趤硇薷幕蛘咧苯釉诼暶鞔a中修改。下一步，我們需要提供ObjectDataSource的SortParameterName 屬性。屬性設(shè)置后，ObjectDataSource才會把GridView的SortExpression屬性傳入SelectMethod。特別地，ObjectDataSource會根據(jù)SortParameterName的值來尋找輸入倉儲，既然BLL中GetProductsPagedAndSorted方法的輸入?yún)?shù)叫做sortExpression，我們這里的ObjectDataSource的SortExpression屬性也應(yīng)該設(shè)置為“sortExpression”。

在這兩步修改后，ObjectDataSource的聲明應(yīng)該如下：

<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輸入?yún)?shù)。

要讓GridView開啟排序，請首先檢查Sorting多選框是否已經(jīng)選中。把GridView的AllowSorting屬性設(shè)置為true以后就能讓每列的標(biāo)題文字呈現(xiàn)為LinkButton。用戶點擊標(biāo)題的LinkButton就會引發(fā)如下幾個步驟：

1.GridView把它的SortExpression 屬性的值修改為當(dāng)前點擊的標(biāo)題所在列的SortExpression的值。

2.ObjectDataSource調(diào)用BLL的GetProductsPagedAndSorted方法，把GridView的SortExpression屬性的值作為sortExpression參數(shù)傳入方法（還有正確的startRowIndex、maximumRows輸入?yún)?shù)的值）。

3.BLL調(diào)用DAL的GetProductsPagedAndSorted方法。

4.DAL執(zhí)行GetProductsPagedAndSorted存儲過程并傳入@sortExpression參數(shù)（和@startRowIndex、@maximumRows輸入?yún)?shù)）。

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

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

圖7：按照UnitPrice排列的果

雖然現(xiàn)在我們的程序能正確按照產(chǎn)品名、分類名、位數(shù)量和價格進(jìn)行排序，但是如果我們選擇按照供應(yīng)商名來排序會得到一個運行時異常，如圖8。

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

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

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

圖10：結(jié)果現(xiàn)在能按照供應(yīng)商名排序了

總結(jié)

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

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

編程快樂！

關(guān)于作者

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

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