ROW_NUMBER()返回的等级信息无法直接在WHERE从句中使用.而在From后面的Select里可以返回ROW_NUMBER(),并在WHERE从句里使用.比如,下面的语句使用一个From后的Select返回ProductName,UnitPrice,和ROW_NUMBER()的结果,然后使用一个WHERE从句来返回price rank在11到20之间的product.
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 的页的数据.
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的记录.将以下代码加到存储过程里,然后保存.
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