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

導(dǎo)言

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

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

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

　　要實(shí)現(xiàn)自定義分頁(yè)，我們需要使用一些方法根據(jù)開始行索引和最大行參數(shù)返回一個(gè)記錄的子集。在前面的教程中，我們看了如何使用微軟SQL SERVER 2005的ROW_NUMBER()來實(shí)現(xiàn)。簡(jiǎn)而言之，ROW_NUMBER()為每一個(gè)查詢返回的行分配一個(gè)行號(hào)。下面這個(gè)查詢演示了如何使用這個(gè)技術(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

　　對(duì)于按照一種固定的排序規(guī)則進(jìn)行分頁(yè)，上述技術(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)建存儲(chǔ)過程來接受@sortExpression輸入?yún)?shù)，使用如下任意一種方法：

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

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

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

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

　　要實(shí)現(xiàn)這個(gè)功能，讓我們?cè)贜orthwind數(shù)據(jù)庫(kù)中新建稱作GetProductsPagedAndSorted的一個(gè)存儲(chǔ)過程。這個(gè)存儲(chǔ)過程接受三個(gè)參數(shù)：@sortExpression，nvarchar(100)類型的輸入?yún)?shù)，用來指定排序方式，它會(huì)直接拼接在ORDER BY子句后面。@startRowIndex 和 @maximumRows都是整數(shù)輸入?yún)?shù)，和前面教程中的一樣。你可以參考下面的腳本建立GetProductsPagedAndSorted存儲(chǔ)過程：

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

　　存儲(chǔ)過程一開始先確保@sortExpression參數(shù)的值已經(jīng)被指定。如果未被指定則按照ProductID排序。接下來，開始構(gòu)建動(dòng)態(tài)的SQL查詢。注意到，在這里的動(dòng)態(tài)SQL查詢和前面的用來從Products表獲取所有行的查詢有些不同。在前面的例子中，我們使用子查詢獲取每一個(gè)產(chǎn)品關(guān)聯(lián)的分類和供應(yīng)商名。在GetProductsPagedAndSorted中我們只能使用JOINS因?yàn)榻Y(jié)果需要根據(jù)分類或者供應(yīng)商名來排序。

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

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

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

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

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

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

圖3：選擇一個(gè)已有的存儲(chǔ)過程

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

圖4：使用GetProductsPagedAndSorted存儲(chǔ)過程

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

圖5：指示存儲(chǔ)過程返回表格信息

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

圖6：選擇方法名

現(xiàn)在，我們已經(jīng)擴(kuò)展了DAL，讓我們來看看BLL吧。打開ProductsBLL類文件并且新增一個(gè)方法GetProductsPagedAndSorted。這個(gè)方法接受三個(gè)參數(shù)－sortExpression，startRowIndex和maximumRows。僅僅是簡(jiǎn)單地調(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存儲(chǔ)過程。剩下的工作就是配置SortParameter.aspx頁(yè)面的ObjectDataSource來根據(jù)用戶請(qǐng)求的排序?yàn)樾碌腂LL方法傳入SortExpression參數(shù)。

　　首先，我們把ObjectDataSource的SelectMethod從GetProductsPaged修改為GetProductsPagedAndSorted?？梢酝ㄟ^配置數(shù)據(jù)源向?qū)У膶傩源翱趤硇薷幕蛘咧苯釉诼暶鞔a中修改。下一步，我們需要提供ObjectDataSource的SortParameterName 屬性。屬性設(shè)置后，ObjectDataSource才會(huì)把GridView的SortExpression屬性傳入SelectMethod。特別地，ObjectDataSource會(huì)根據(jù)SortParameterName的值來尋找輸入倉(cāng)儲(chǔ)，既然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>

注意：和前面教程說的一樣，請(qǐng)確保ObjectDataSource的SelectParameters集合中sortExpression、startRowIndex和maximumRows輸入?yún)?shù)。

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

1.GridView把它的SortExpression 屬性的值修改為當(dāng)前點(diǎn)擊的標(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存儲(chǔ)過程并傳入@sortExpression參數(shù)（和@startRowIndex、@maximumRows輸入?yún)?shù)）。

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

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

圖7：按照UnitPrice排列的果

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

圖8：按照供應(yīng)商名排序會(huì)得到一個(gè)運(yùn)行時(shí)異常

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

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

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

總結(jié)

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

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

編程快樂！

關(guān)于作者

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