使用 ROW_NUMBER() 函数结合 SQL Server 2005 和 ASP.NET 实现数据分页

• 下载源文件 - 667 Kb

查看演示

引言

SQL Server 2000 的一个主要弱点在于它无法很好地处理数据分页。以前解决这个问题的方法总是先选择所有结果，然后通过编程方式隐藏用户不想看到的结果。当用户点击“下一页”或“上一页”时，你又要重新选择所有行，只显示用户请求的数据。虽然这种方法可以实现期望的界面，但效率非常低下。为什么我们不能只选择需要的数据呢？如果你曾经编写过针对数百万条记录使用 “like” 进行搜索的功能，你就知道 SQL Server 2000 在这种情况下性能有多么缓慢。

在我使用 SQL Server 2000 的日子里，我曾不懈地寻找解决这个问题的方法。我尝试过使用 “select top” 的实现，其中最后一行大于一个参数。这在某些情况下有效，比如按主键或日期排序。否则，由于重复数据的存在，这种方法会失败。我还尝试过构建使用疯狂的 “for” 循环来尝试完成此任务的存储过程。在所有这些尝试中，我总是会遇到瓶颈。客户会要求一个我无法通过我的方法支持的功能，而我总是会回到低效地选择所有（或大部分）行并通过编程方式处理分页方案的老路。

在这个过程中，我经常设想一个 SQL Server 函数，它可以为我的结果集添加一个顺序行号，并允许我使用一个 “where” 子句来仅选择我需要的行。经过一番研究，我发现这个函数确实存在。唯一的问题是，它只存在于 Oracle 中！我非常愤怒，为什么如此有用的功能会被从 SQL Server 2000 中遗漏？

几年过去了，微软发布了 .NET，它为这个问题提供了一个部分解决方案。ASP.NET 允许你对 Web 控件的结果进行输出缓存。基本上，你可以一次性选择所有行，然后在分页浏览结果时，从缓存的结果中提取后续的每一组数据。这似乎部分解决了性能问题，尽管你仍然面临着初始选择的问题。但是，如果你想查看实时变化的数据怎么办？当你减小缓存时间时，性能会变差；当你增加缓存时间时，数据会过时。最终，你又会回到那个疲惫的老方法。

随着 SQL Server 2005 的发布，微软引入了备受期待的 ROW_NUMBER() 函数来解决这个问题。在本文中，我们将通过 C# 实现，演示如何使用 ROW_NUMBER() 方法进行数据分页。

代码

第一步是编写你的存储过程。使用 ROW_NUMBER() 的 SQL 代码不像你想象的那么直观。当我最初尝试这样做时，我试图像使用 newid() 一样简单地使用 ROW_NUMBER() 函数。我很快发现，那行不通。经过一些研究，我得到了下面的存储过程。虽然我宁愿看到比下面的语法更直观的写法，但仔细想想，它是合理的。我猜他们不想隐藏逻辑，让程序员仅仅接受神奇的事情发生了。在接下来的项目中，我将使用一个包含美国所有邮政编码的数据库。

CREATE PROCEDURE [dbo].[sp_getzipcodes] 
     -- Add the parameters for the stored procedure here
     @start int = 0

AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     Set NOCOUNT ON
     SELECT TOP 20 * FROM 
     (
          SELECT zip,city,state,latitude,longitude,timezone,dst,
          ROW_NUMBER() OVER (ORDER BY zip) AS num
          FROM dbo.zipcode
     ) AS a
     WHERE num > @start
END

现在你有了存储过程，你需要将其结果显示在网站上。在我们的示例中，我们使用了一个 GridView 控件，但本质上这可以与任何控件配合使用，因为我们像这样在 SQL 数据源中设置了参数：

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
     ConnectionString="<%$ ConnectionStrings:personalConnectionString %>"
     SelectCommand="sp_getzipcodes" SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:QueryStringParameter Name="start" 
           QueryStringField="start" DefaultValue="0" />
  </SelectParameters>
</asp:SqlDataSource>

最后，你需要构建你的分页控件。在这个项目中，我们通过在代码隐藏页面中设置一个 `Literal` 控件来实现这一点。

if ((Request.QueryString["start"] == null) | 
    (Request.QueryString["start"] == "0"))
{ 
     paging.Text = "<< prev | <a href = \"?start=20\">next >></a>";
}
else
{
     int start = Convert.ToInt32(Request.QueryString["start"]) + 1;
     int next = Convert.ToInt32(Request.QueryString["start"]) + results;
     int prev = Convert.ToInt32(Request.QueryString["start"]) - results;
     if (next > max)
     { 
          paging.Text = @"<a href = ""?start=" + prev + 
                        @"""><< prev</a> | next >></a>";
     }
     else
     { 
          paging.Text = @"<a href = ""?start=" + prev + 
                        @"""><< prev</a> | <a href = ""?start=" + 
                        next + @""">next >></a>";
     }
}

就这样。下载源代码以获取完整的项目。下载内容包括 Visual Studio 2005 项目、SQL Server 存储过程以及 CSV 格式的邮政编码数据库。

• 在线演示.