默認的分頁功能非常吸引人,因為你只需要選中一個checkbox就可以完成了.但是它每次都讀取所有的數據,這種方式在大數據量或者並發用戶多的情況下就不合適.在這樣的情況下,我們必須通過自定義分頁來使系統達到更好的性能.
自定義分頁的一個重點是要寫一個返回僅僅需要的數據的查詢語句.幸運的,Microsoft SQL Server 2005 提供了一個新的keyword,通過它我們可以寫出讀取需要的數據的查詢.在本教程裡,我們將學習在GridView裡如何使用Microsoft SQL Server 2005 的這個新的keyword來實現自定義分頁.自定義分頁和默認分頁的界面看起來一樣,但是當你從一頁轉到另一頁時,在效率上差了幾個數量級.
注意:自定義分頁帶來的性能提升程序取決於數據的總量和數據庫的負載.在本教程的最後我們會用數據來說明自定義分頁帶來的性能方面的好處.
給數據分頁的時候,頁面顯示的數據取決於請求的是哪一頁和每頁顯示多少條.比如,想象以下我們給81個product分頁,每頁顯示10條.當我們浏覽第一頁時,我們需要的是product 1 到 product 10.當浏覽第二頁時,我們需要的是product 11 到 product 20,以次類推.
對於需要讀取什麼數據和分頁的頁面怎麼顯示,有三個相關的變量:
對默認分頁來說,Start Row Index是由頁索引和每頁的記錄數加1得到,Maximum Rows 就是每頁的記錄數.使用默認分頁時,不管是呈現哪頁的數據,都是要讀取全部的數據,所有每行的索引都是已知的,這樣獲取Start Row Index變的沒有價值.而且,記錄的總條數是可以通過DataTable的總條數來獲取的.
自定義分頁只返回從Start Row Index 開始的Maximum Rows條記錄.在這裡有兩個要注意的地方:
在後面的兩步裡我們將寫出和上面兩點相關的SQL.除此之外，我們還將在DAL和BLL裡完成相應的方法.
在我們學習如何返回顯示頁面需要的數據之前,我們先來看看怎麼獲取數據的總條數.因為在配置界面的時候需要用到這個信息.我們使用SQL的COUNT aggregate function來實現這個.比如,返回Products表的總記錄條數,我們可以用如下的語句:
1
2
SELECT COUNT(*)
FROM Products
我們在DAL裡添加一個方法來返回這個信息.這個方法名為TotalNumberOfProducts() ,它會執行上面的SQL語句.
打開App_Code/DAL 文件夾裡的 Northwind.xsd .然後在設計器裡右鍵點ProductsTableAdapter ,選擇Add Query.和我們在以前的教程裡學習的那樣,這樣會允許我們添加一個新的DAL方法,這個方法被調用時會執行指定的SQL或存儲過程.和前面的 TableAdapter 方法一樣,為這個添加一個SQL statement.
圖 1: 使用 SQL Statement
在下一個窗體我們可以指定創建哪種SQL .由於查詢只返回一個值–Products表的總記錄條數–我們選擇“SELECT which returns a singe value”.
圖 2: 使用 SELECT Statement that Returns a Single Value來配置SQL
下一步是寫SQL語句.
圖 3: 使用SELECT COUNT(*) FROM Products 語句
最後給這個方法命名為TotalNumberOfProducts.
圖 4: 將方法命名為 TotalNumberOfProducts
點擊結束後,DAL裡添加了一個TotalNumberOfProducts方法.這個方法返回的值可為空,而Count語句總是返回一個非空的值.
我們還需要在BLL中加一個方法.打開ProductsBLL類文件,添加一個TotalNumberOfProducts方法,這個方法要做的只是調用DAL的TotalNumberOfProducts方法.
1
2
3
4
public int TotalNumberOfProducts()
{
return Adapter.TotalNumberOfProducts().GetValueOrDefault();
}
DAL的TotalNumberOfProducts方法返回一個可空的整型,而需要ProductsBLL類的TotalNumberOfProducts方法返回一個標准的整型.調用GetValueOrDefault方法,如果可為空的整型為空,則返回默認值,0.
下一步我們要在DAL和BLL裡創建接受Start Row Index 和Maximum Rows 的方法,然後返回合適的記錄.我們首先看看需要的SQL語句.我們面臨的挑戰是需要為整個分頁的記錄分配索引,用來返回從Start Row Index 開始的Maximum Records number of records條記錄.
如果在數據庫表裡已經有一個列作為索引,那麼一切會變的很簡單.我們首先會想到Products表的ProductID字段可以滿足這個條件,第一個 Product的ProductID為1,第二個為2,以此類推.然而當一個product被刪除後,這個序列會留下間隔來,所以這個方法不行.
有兩種可以把整個要分頁的數據和一個row index關聯起來的方法.
本教程用ROW_NUMBER()來實現自定義分頁.如果需要知道更多的關於table變量和SET ROWCOUNT的技術,請看 A More Efficient Method for Paging Through Large Result Sets.
以下語句用來使用ROW_NUMBER()將一個等級和返回的每條記錄關聯:
1
2
3
SELECT columnList,
ROW_NUMBER() OVER(orderByClause)
FROM TableName
ROW_NUMBER()返回一個根據指定排序的表示每條記錄的等級的值.比如,我們可以用以下居於查看根據價格來排序(降序)的每個product的等級:
1
2
3
SELECT ProductName, UnitPrice,
ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank
FROM Products
圖5 是在Visual Studio裡運行以上代碼的結果. 注意product根據價格排序,每行有一個等級.
圖 5: 返回的記錄裡每行有一個Price Rank
注意: ROW_NUMBER() 只是 SQL Server 2005裡很多排級的功能中的一種. 想了解更多的ROW_NUMBER()的討論,包括其它的排級功能,請看 Returning Ranked Results with Microsoft SQL Server 2005.
當使用OVER從句裡的ORDER BY 列名(UnitPrice)來排級時,SQL Server會對結果排序.為了提升大數據量查詢時的性能,可以為用來排序的列加上非聚集索引.更多的性能考慮參考Ranking Functions and Performance in SQL Server 2005.
ROW_NUMBER()返回的等級信息無法直接在WHERE從句中使用.而在From後面的Select裡可以返回ROW_NUMBER(),並在 WHERE從句裡使用.比如,下面的語句使用一個From後的Select返回ProductName,UnitPrice,和ROW_NUMBER() 的結果,然後使用一個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
更進一步,我們可以根據這個方法返回給定Start Row Index 和Maximum Rows 的頁的數據.
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>)
注意:我們在本教程的後面會看到, ObjectDataSource 提供的StartRowIndex是從0開始的,而ROW_NUMBER()的值從1開始.因此,WHERE從句返回會嚴格返回PriceRank大於 StartRowIndex而小於StartRowIndex+MaximumRows的那些記錄.
我們已經知道如何根據給定的Start Row Index 和Maximum Rows 用ROW_NUMBER()返回特定頁的數據.現在我們需要在DAL和BLL裡實現它.
我們首先要決定根據什麼排序來分級.我們這裡用product名字的字母順序.這意味著我們還不能同時實現排序的功能.在後面的教程裡,我們將學習如何實現這樣的功能.
在前面我們使用SQL statement創建DAL方法.但是TableAdapter wizard 使用的Visual Stuido裡的T-SQL 解析器不能識別帶OVER語法的ROW_NUMBER()方法.因此我們要以存儲過程來創建這個DAL方法.從view menu裡選擇server explorer(Ctrl+Alt+S),展開NORTHWND.MDF 的節點.右鍵點擊存儲過程,選擇增加一個新的存儲過程(見圖6).
圖 6: 為Products分頁增加一個存儲過程
這個存儲過程帶兩個整型的輸入參數- @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)
創建完存儲過程後,花點時間測試一下.右鍵在Server Explorer 點名為GetProductsPaged的存儲過程,選擇執行.Visual Studio 會讓你輸入參數, @startRowIndex和@maximumRows(見圖7).輸入不同的值查看一下結果是什麼.
圖 7: 為 @startRowIndex 和@maximumRows Parameters輸入值
輸入參數的值後,你會看到結果.圖8的結果為兩個參數的值都為10的結果.
圖 8: 將在第二頁裡顯示的數據
完成存儲過程後,我們可以創建ProductsTableAdapter 方法了.打開Northwind.xsd ,右鍵點ProductsTableAdapter,選擇Add Query.選擇使用已經存在的存儲過程.
圖 9: 使用已經存在的存儲過程創建DAL Method
下一步會要我們選擇要調用的存儲過程.從下拉列表裡選擇GetProductsPaged .
圖10: 選擇GetProductsPaged
下一步要選擇存儲過程返回的數據類型:表值,單一值,無值.由於GetProductsPaged 返回多條記錄,所以選擇表值.
圖 11: 為存儲過程指定返回表值
最後給方法命名.象前面的方法一樣,選擇Fill a DataTable 和Return a DataTable,為第一個命名為FillPaged ,第二個為GetProductsPaged.
圖 12: 命名方法為FillPaged 和GetProductsPaged
除了創建一個DAL方法返回特定頁的products外,我們需要在BLL裡也這樣做.和DAL方法一樣,BLL的GetProductsPaged 方法帶兩個整型的輸入參數,分別為Start Row Index 和Maximum Rows,並返回在指定范圍內的記錄.在ProductsBLL 創建這個方法,僅僅調用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方法的參數取任何名字.但是我們馬上會看到,選擇用startRowIndex 和maximumRows 會讓我們在配置ObjectDataSource 時方便很多.
