• 默認(rèn)分頁– 你僅僅只用選中data Web control的 智能標(biāo)簽的Enable Paging ; 然而,當(dāng)你瀏覽頁面的時(shí)候,雖然你看到的只是一小部分?jǐn)?shù)據(jù),ObjectDataSource 還是會(huì)每次都讀取所有數(shù)據(jù)
• 自定義分頁– 通過只從數(shù)據(jù)庫讀取用戶需要瀏覽的那部分?jǐn)?shù)據(jù),提高了性能. 顯然這種方法需要你做更多的工作.

默認(rèn)的分頁功能非常吸引人,因?yàn)槟阒恍枰x中一個(gè)checkbox就可以完成了.但是它每次都讀取所有的數(shù)據(jù),這種方式在大數(shù)據(jù)量或者并發(fā)用戶多的情況下就不合適.在這樣的情況下,我們必須通過自定義分頁來使系統(tǒng)達(dá)到更好的性能.

自定義分頁的一個(gè)重點(diǎn)是要寫一個(gè)返回僅僅需要的數(shù)據(jù)的查詢語句.幸運(yùn)的,Microsoft SQL Server 2005 提供了一個(gè)新的keyword,通過它我們可以寫出讀取需要的數(shù)據(jù)的查詢.在本教程里,我們將學(xué)習(xí)在GridView里如何使用Microsoft SQL Server 2005 的這個(gè)新的keyword來實(shí)現(xiàn)自定義分頁.自定義分頁和默認(rèn)分頁的界面看起來一樣,但是當(dāng)你從一頁轉(zhuǎn)到另一頁時(shí),在效率上差了幾個(gè)數(shù)量級.

注意:自定義分頁帶來的性能提升程序取決于數(shù)據(jù)的總量和數(shù)據(jù)庫的負(fù)載.在本教程的最后我們會(huì)用數(shù)據(jù)來說明自定義分頁帶來的性能方面的好處.

第一步: 理解自定義分頁的過程

給數(shù)據(jù)分頁的時(shí)候,頁面顯示的數(shù)據(jù)取決于請求的是哪一頁和每頁顯示多少條.比如,想象以下我們給81個(gè)product分頁,每頁顯示10條.當(dāng)我們?yōu)g覽第一頁時(shí),我們需要的是product 1 到 product 10.當(dāng)瀏覽第二頁時(shí),我們需要的是product 11 到 product 20,以次類推.

對于需要讀取什么數(shù)據(jù)和分頁的頁面怎么顯示,有三個(gè)相關(guān)的變量:

• Start Row Index – 頁面里顯示數(shù)據(jù)的第一行的索引; 這個(gè)值可以通過頁的索引乘每頁顯示的記錄的條數(shù)加1得到. 例如, 如果一頁顯示10條數(shù)據(jù), 那么對第一頁來說(第一頁的索引為0), 第一行的索引為0 * 10 + 1, or 1; 對第二頁來說(索引為1), 第一行的索引為1 * 10 + 1, 即 11.
• Maximum Rows – 每頁顯示的最多記錄的條數(shù). 之所以稱為“maximum” rows 是由于最后一頁顯示的數(shù)據(jù)可能會(huì)比page size要小. 比如, 當(dāng)以每頁10條記錄來顯示81條時(shí), 最后一頁也就是第九頁只包含一條記錄. 沒有頁面顯示的記錄條數(shù)會(huì)大于Maximum Rows 的值.
• Total Record Count – 顯示數(shù)據(jù)的總條數(shù). 不需要知道頁面顯示什么數(shù)據(jù),但是記錄總數(shù)會(huì)影響到分頁. 比如, 如果對81條product記錄分頁,每頁10條,那么總頁數(shù)為9.

對默認(rèn)分頁來說,Start Row Index是由頁索引和每頁的記錄數(shù)加1得到,Maximum Rows 就是每頁的記錄數(shù).使用默認(rèn)分頁時(shí),不管是呈現(xiàn)哪頁的數(shù)據(jù),都是要讀取全部的數(shù)據(jù),所有每行的索引都是已知的,這樣獲取Start Row Index變的沒有價(jià)值.而且,記錄的總條數(shù)是可以通過DataTable的總條數(shù)來獲取的.

自定義分頁只返回從Start Row Index 開始的Maximum Rows條記錄.在這里有兩個(gè)要注意的地方:

• 我們必須把整個(gè)要分頁的數(shù)據(jù)和一個(gè)row index關(guān)聯(lián)起來,這樣才能從指定的Start Row Index 開始返回需要的數(shù)據(jù).
• 我們需要提供用來分頁的數(shù)據(jù)的總條數(shù).

在后面的兩步里我們將寫出和上面兩點(diǎn)相關(guān)的SQL.除此之外，我們還將在DAL和BLL里完成相應(yīng)的方法.

第二步: 返回需要分頁的記錄的總條數(shù)

在我們學(xué)習(xí)如何返回顯示頁面需要的數(shù)據(jù)之前,我們先來看看怎么獲取數(shù)據(jù)的總條數(shù).因?yàn)樵谂渲媒缑娴臅r(shí)候需要用到這個(gè)信息.我們使用SQL的COUNT aggregate function來實(shí)現(xiàn)這個(gè).比如,返回Products表的總記錄條數(shù),我們可以用如下的語句:

1
      2

SELECT COUNT(*)
      FROM Products

我們在DAL里添加一個(gè)方法來返回這個(gè)信息.這個(gè)方法名為TotalNumberOfProducts() ,它會(huì)執(zhí)行上面的SQL語句.

打開App_Code/DAL 文件夾里的 Northwind.xsd .然后在設(shè)計(jì)器里右鍵點(diǎn)ProductsTableAdapter ,選擇Add Query.和我們在以前的教程里學(xué)習(xí)的那樣,這樣會(huì)允許我們添加一個(gè)新的DAL方法,這個(gè)方法被調(diào)用時(shí)會(huì)執(zhí)行指定的SQL或存儲過程.和前面的 TableAdapter 方法一樣,為這個(gè)添加一個(gè)SQL statement.

圖 1: 使用 SQL Statement

在下一個(gè)窗體我們可以指定創(chuàng)建哪種SQL .由于查詢只返回一個(gè)值–Products表的總記錄條數(shù)–我們選擇“SELECT which returns a singe value”.

圖 2: 使用 SELECT Statement that Returns a Single Value來配置SQL

下一步是寫SQL語句.

圖 3: 使用SELECT COUNT(*) FROM Products 語句

最后給這個(gè)方法命名為TotalNumberOfProducts.

圖 4: 將方法命名為 TotalNumberOfProducts

點(diǎn)擊結(jié)束后,DAL里添加了一個(gè)TotalNumberOfProducts方法.這個(gè)方法返回的值可為空,而Count語句總是返回一個(gè)非空的值.

我們還需要在BLL中加一個(gè)方法.打開ProductsBLL類文件,添加一個(gè)TotalNumberOfProducts方法,這個(gè)方法要做的只是調(diào)用DAL的TotalNumberOfProducts方法.

1
      2
      3
      4

public int TotalNumberOfProducts()
      {
      return Adapter.TotalNumberOfProducts().GetValueOrDefault();
      }

DAL的TotalNumberOfProducts方法返回一個(gè)可空的整型,而需要ProductsBLL類的TotalNumberOfProducts方法返回一個(gè)標(biāo)準(zhǔn)的整型.調(diào)用GetValueOrDefault方法,如果可為空的整型為空,則返回默認(rèn)值,0.

第三步: 返回需要的數(shù)據(jù)記錄

下一步我們要在DAL和BLL里創(chuàng)建接受Start Row Index 和Maximum Rows 的方法,然后返回合適的記錄.我們首先看看需要的SQL語句.我們面臨的挑戰(zhàn)是需要為整個(gè)分頁的記錄分配索引,用來返回從Start Row Index 開始的Maximum Records number of records條記錄.

如果在數(shù)據(jù)庫表里已經(jīng)有一個(gè)列作為索引,那么一切會(huì)變的很簡單.我們首先會(huì)想到Products表的ProductID字段可以滿足這個(gè)條件,第一個(gè) Product的ProductID為1,第二個(gè)為2,以此類推.然而當(dāng)一個(gè)product被刪除后,這個(gè)序列會(huì)留下間隔來,所以這個(gè)方法不行.

有兩種可以把整個(gè)要分頁的數(shù)據(jù)和一個(gè)row index關(guān)聯(lián)起來的方法.

• 使用SQL Server 2005的ROW_NUMBER() Keyword – SQL Server 2005的新特性,它可以將記錄根據(jù)一定的順序排列,每條記錄和一個(gè)等級相關(guān) 這個(gè)等級可以用來作為每條記錄的row index.
• 使用SET ROWCOUNT – SQL Server的 SET ROWCOUNT statement 可以用來指定有多少記錄需要處理; table variables 是可以存放表格式的T-SQL 變量, 和temporary tables類似. 這個(gè)方法在Microsoft SQL Server 2005 和SQL Server 2000都可以用 (ROW_NUMBER() 方法只能在SQL Server 2005里用).
  
  
  這個(gè)思路是,為要分頁的數(shù)據(jù)創(chuàng)建一個(gè)table變量,這個(gè)table變量里有一個(gè)作為主健的IDENTITY列.這樣需要分頁的每條記錄在table變量里就和一個(gè)row index(通過IDENTITY列)關(guān)聯(lián)起來了.一旦table變量產(chǎn)生,連接數(shù)據(jù)庫表的SELECT語句就被執(zhí)行,獲取需要的記錄.SET ROWCOUNT用來限制放到table變量里的記錄的數(shù)量.
  當(dāng)SET ROWCOUNT的值指定為Start Row Index 加上Maximum Rows時(shí),這個(gè)方法的效率取決于被請求的頁數(shù).對于比較前面的頁來說– 比如開始幾頁的數(shù)據(jù)– 這種方法非常有效. 但是對接近尾部的頁來說,這種方法的效率和默認(rèn)分頁時(shí)差不多.

本教程用ROW_NUMBER()來實(shí)現(xiàn)自定義分頁.如果需要知道更多的關(guān)于table變量和SET ROWCOUNT的技術(shù),請看 A More Efficient Method for Paging Through Large Result Sets.

以下語句用來使用ROW_NUMBER()將一個(gè)等級和返回的每條記錄關(guān)聯(lián):

1
      2
      3

SELECT columnList,
      ROW_NUMBER() OVER(orderByClause)
      FROM TableName

ROW_NUMBER()返回一個(gè)根據(jù)指定排序的表示每條記錄的等級的值.比如,我們可以用以下居于查看根據(jù)價(jià)格來排序(降序)的每個(gè)product的等級:

1
      2
      3

SELECT ProductName, UnitPrice,
      ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
      FROM Products

圖5 是在Visual Studio里運(yùn)行以上代碼的結(jié)果. 注意product根據(jù)價(jià)格排序,每行有一個(gè)等級.

圖 5: 返回的記錄里每行有一個(gè)Price Rank

注意: ROW_NUMBER() 只是 SQL Server 2005里很多排級的功能中的一種. 想了解更多的ROW_NUMBER()的討論,包括其它的排級功能,請看 Returning Ranked Results with Microsoft SQL Server 2005.

當(dāng)使用OVER從句里的ORDER BY 列名(UnitPrice)來排級時(shí),SQL Server會(huì)對結(jié)果排序.為了提升大數(shù)據(jù)量查詢時(shí)的性能,可以為用來排序的列加上非聚集索引.更多的性能考慮參考Ranking Functions and Performance in SQL Server 2005.

ROW_NUMBER()返回的等級信息無法直接在WHERE從句中使用.而在From后面的Select里可以返回ROW_NUMBER(),并在 WHERE從句里使用.比如,下面的語句使用一個(gè)From后的Select返回ProductName,UnitPrice,和ROW_NUMBER() 的結(jié)果,然后使用一個(gè)WHERE從句來返回price rank在11到20之間的product.

1
      2
      3
      4
      5
      6
      7

SELECT PriceRank, ProductName, UnitPrice
      FROM
      (SELECT ProductName, UnitPrice,
      ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
      FROM Products
      ) AS ProductsWithRowNumber
      WHERE PriceRank BETWEEN 11 AND 20

更進(jìn)一步,我們可以根據(jù)這個(gè)方法返回給定Start Row Index 和Maximum Rows 的頁的數(shù)據(jù).

1
      2
      3
      4
      5
      6
      7

SELECT PriceRank, ProductName, UnitPrice
      FROM
      (SELECT ProductName, UnitPrice,
      ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
      FROM Products
      ) AS ProductsWithRowNumber
      WHERE PriceRank > <i>StartRowIndex</i> AND PriceRank <= (<i>StartRowIndex</i> + <i>MaximumRows</i>)

注意:我們在本教程的后面會(huì)看到, ObjectDataSource 提供的StartRowIndex是從0開始的,而ROW_NUMBER()的值從1開始.因此,WHERE從句返回會(huì)嚴(yán)格返回PriceRank大于 StartRowIndex而小于StartRowIndex+MaximumRows的那些記錄.

我們已經(jīng)知道如何根據(jù)給定的Start Row Index 和Maximum Rows 用ROW_NUMBER()返回特定頁的數(shù)據(jù).現(xiàn)在我們需要在DAL和BLL里實(shí)現(xiàn)它.

我們首先要決定根據(jù)什么排序來分級.我們這里用product名字的字母順序.這意味著我們還不能同時(shí)實(shí)現(xiàn)排序的功能.在后面的教程里,我們將學(xué)習(xí)如何實(shí)現(xiàn)這樣的功能.

在前面我們使用SQL statement創(chuàng)建DAL方法.但是TableAdapter wizard 使用的Visual Stuido里的T-SQL 解析器不能識別帶OVER語法的ROW_NUMBER()方法.因此我們要以存儲過程來創(chuàng)建這個(gè)DAL方法.從view menu里選擇server explorer(Ctrl+Alt+S),展開NORTHWND.MDF 的節(jié)點(diǎn).右鍵點(diǎn)擊存儲過程,選擇增加一個(gè)新的存儲過程(見圖6).

圖 6: 為Products分頁增加一個(gè)存儲過程

這個(gè)存儲過程帶兩個(gè)整型的輸入?yún)?shù)- @startRowIndex和@maximumRows- 并用ROW_NUMBER()以ProductName字段排序,返回那些大于@startRowIndex并小于等于 @startRowIndex+@maximumRows的記錄.將以下代碼加到存儲過程里,然后保存.

1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20

CREATE PROCEDURE dbo.GetProductsPaged
      (
      @startRowIndex int,
      @maximumRows int
      )
      AS
      SELECT   ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued, CategoryName, SupplierName
      FROM
      (
      SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued,
      (SELECT CategoryName
      FROM Categories
      WHERE Categories.CategoryID = Products.CategoryID) AS CategoryName,
      (SELECT CompanyName
      FROM Suppliers
      WHERE Suppliers.SupplierID = Products.SupplierID) AS SupplierName,
      ROW_NUMBER() OVER (ORDER BY ProductName) AS RowRank
      FROM Products
      ) AS ProductsWithRowNumbers
      WHERE RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)

創(chuàng)建完存儲過程后,花點(diǎn)時(shí)間測試一下.右鍵在Server Explorer 點(diǎn)名為GetProductsPaged的存儲過程,選擇執(zhí)行.Visual Studio 會(huì)讓你輸入?yún)?shù), @startRowIndex和@maximumRows(見圖7).輸入不同的值查看一下結(jié)果是什么.

圖 7: 為 @startRowIndex 和@maximumRows Parameters輸入值

輸入?yún)?shù)的值后,你會(huì)看到結(jié)果.圖8的結(jié)果為兩個(gè)參數(shù)的值都為10的結(jié)果.

圖 8: 將在第二頁里顯示的數(shù)據(jù)

完成存儲過程后,我們可以創(chuàng)建ProductsTableAdapter 方法了.打開Northwind.xsd ,右鍵點(diǎn)ProductsTableAdapter,選擇Add Query.選擇使用已經(jīng)存在的存儲過程.

圖 9: 使用已經(jīng)存在的存儲過程創(chuàng)建DAL Method

下一步會(huì)要我們選擇要調(diào)用的存儲過程.從下拉列表里選擇GetProductsPaged .

圖10: 選擇GetProductsPaged

下一步要選擇存儲過程返回的數(shù)據(jù)類型:表值,單一值,無值.由于GetProductsPaged 返回多條記錄,所以選擇表值.

圖 11: 為存儲過程指定返回表值

最后給方法命名.象前面的方法一樣,選擇Fill a DataTable 和Return a DataTable,為第一個(gè)命名為FillPaged ,第二個(gè)為GetProductsPaged.

圖 12: 命名方法為FillPaged 和GetProductsPaged

除了創(chuàng)建一個(gè)DAL方法返回特定頁的products外,我們需要在BLL里也這樣做.和DAL方法一樣,BLL的GetProductsPaged 方法帶兩個(gè)整型的輸入?yún)?shù),分別為Start Row Index 和Maximum Rows,并返回在指定范圍內(nèi)的記錄.在ProductsBLL 創(chuàng)建這個(gè)方法,僅僅調(diào)用DAL的GetProductsPaged 就可以了.

1
      2
      3
      4
      5

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, false)]
      public Northwind.ProductsDataTable GetProductsPaged(int startRowIndex, int maximumRows)
      {
      return Adapter.GetProductsPaged(startRowIndex, maximumRows);
      }

你可以為BLL方法的參數(shù)取任何名字.但是我們馬上會(huì)看到,選擇用startRowIndex 和maximumRows 會(huì)讓我們在配置ObjectDataSource 時(shí)方便很多.