导言
如我们在之前的教程里讨论的那样,分页可以通过两种方法来实现:
１.默认分页– 你仅仅只用选中data Web control的 智能标签的Enable Paging ; 然而,当你浏览页面的时候,虽然你看到的只是一小部分数据,ObjectDataSource 还是会每次都读取所有数据
２.自定义分页– 通过只从数据库读取用户需要浏览的那部分数据,提高了性能. 显然这种方法需要你做更多的工作.
默认的分页功能非常吸引人,因为你只需要选中一个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得到. 例如, 如果一页显示10条数据, 那么对第一页来说(第一页的索引为0), 第一行的索引为0 * 10 + 1, or 1; 对第二页来说(索引为1), 第一行的索引为1 * 10 + 1, 即 11.
２.Maximum Rows – 每页显示的最多记录的条数. 之所以称为“maximum” rows 是由于最后一页显示的数据可能会比page size要小. 比如, 当以每页10条记录来显示81条时, 最后一页也就是第九页只包含一条记录. 没有页面显示的记录条数会大于Maximum Rows 的值.
３.Total Record Count – 显示数据的总条数. 不需要知道页面显示什么数据,但是记录总数会影响到分页. 比如, 如果对81条product记录分页,每页10条,那么总页数为9.
对默认分页来说,Start Row Index是由页索引和每页的记录数加1得到,Maximum Rows 就是每页的记录数.使用默认分页时,不管是呈现哪页的数据,都是要读取全部的数据,所有每行的索引都是已知的,这样获取Start Row Index变的没有价值.而且,记录的总条数是可以通过DataTable的总条数来获取的.
自定义分页只返回从Start Row Index 开始的Maximum Rows条记录.在这里有两个要注意的地方:
１.我们必须把整个要分页的数据和一个row index关联起来,这样才能从指定的Start Row Index 开始返回需要的数据.
２.我们需要提供用来分页的数据的总条数.
在后面的两步里我们将写出和上面两点相关的SQL.除此之外，我们还将在DAL和BLL里完成相应的方法.
第二步: 返回需要分页的记录的总条数
在我们学习如何返回显示页面需要的数据之前,我们先来看看怎么获取数据的总条数.因为在配置界面的时候需要用到这个信息.我们使用SQL的COUNT aggregate function来实现这个.比如,返回Products表的总记录条数,我们可以用如下的语句:
SELECT COUNT(*) FROM Products</div>
我们在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方法.
public int TotalNumberOfProducts()
{
return Adapter.TotalNumberOfProducts().GetValueOrDefault();
}
</div>
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关联起来的方法.
１.使用SQL Server 2005的ROW_NUMBER() Keyword – SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index.
２.使用SET ROWCOUNT – SQL Server的 SET ROWCOUNT statement 可以用来指定有多少记录需要处理; table variables 是可以存放表格式的T-SQL 变量, 和temporary tables类似. 这个方法在Microsoft SQL Server 2005 和SQL Server 2000都可以用 (ROW_NUMBER() 方法只能在SQL Server 2005里用).
这个思路是,为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里就和一个row index(通过IDENTITY列)关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SET ROWCOUNT用来限制放到table变量里的记录的数量.
当SET ROWCOUNT的值指定为Start Row Index 加上Maximum Rows时,这个方法的效率取决于被请求的页数.对于比较前面的页来说– 比如开始几页的数据– 这种方法非常有效. 但是对接近尾部的页来说,这种方法的效率和默认分页时差不多.
本教程用ROW_NUMBER()来实现自定义分页.如果需要知道更多的关于table变量和SET ROWCOUNT的技术,请看 A More Efficient Method for Paging Through Large Result Sets.
以下语句用来使用ROW_NUMBER()将一个等级和返回的每条记录关联:
SELECT columnList, ROW_NUMBER() OVER(orderByClause) FROM TableName</div>
ROW_NUMBER()返回一个根据指定排序的表示每条记录的等级的值.比如,我们可以用以下居于查看根据价格来排序(降序)的每个product的等级:
SELECT ProductName, UnitPrice, ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank FROM Products</div>
图5 是在Visual Studio里运行以上代码的结果. 注意product根据价格排序,每行有一个等级.