ASP .NET 如何在 SQL 查询层面实现分页

小雪崩

　　【编者按】本文作者为来自巴基斯坦的软件开发工程师 Aqeeel，主要介绍了在 SQL 查询层面实现 ASP.NET 应用的分页方法。
　　本文系 iyunv 工程师编译呈现，以下为正文。
　　GridView 提供了一种实现分页的方法。但是，随着记录的不断扩大，我们需要在查询层面进行优化。
简介
　　在 SQL 查询层面实现 ASP.NET 程序分页，而不借助 GridView。
背景
　　无可否认，GridView 是在 ASP.NET Web 表单展示数据的强大工具，它能在结果集较大时实现分页。然而，后端会获取完整的数据，抽取出相关数据，然后通过 GridView 展示在 Web 表单中。在这种情况下，相关数据只是完全抽取数据的一小部分。这些多余的数据造成了处理能力、内存空间以及时间的极大浪费。在本文中，我们将展示如何仅从数据库抽取所需数据，从而避免这些浪费。
　　下图展示了从数据库中获取完整数据的方式。在渲染阶段，相关数据会被抽取出来，填充到 GridView 中。

http://news.iyunv.com/content/images/2016/04/01.png

　　下图展示了从数据库中抽取过滤或相关数据的方式，进而得到更小的数据集。在 Web 应用中，同样的数据集无需经过进一步抽取，就可以填充到 GridView 中。

http://news.iyunv.com/content/images/2016/04/02.png

具体实现
工具
　　本例借助 SQL Server 2014 与 Visual Studio 2015 实现。2012 之前的 SQL Server 版本不支持 FETCH，但是使用 ROW NUMBER 可以达到同样的效果。
首先进行后端设置：

• 　　创建名为 TestPagingInASPNET 的数据库，
  
• 　　创建名为 AdministrativeUnits 与 Cities 的两张表。
  
• 　　创建存储过程（Stored Procedures，简称 SP），用于从数据库获取数据。请注意，笔者创建了两个存储过程，名字分别为 SelectCitiesWithPaging 与 SelectCitiesWithPagingOldSQLVersions。由于笔者是在 SQL Server 2014 中实现该解决方案的，在第一个 SP 中，笔者使用了 OFFSET FETCH 声明。对于更早的版本，比如 SQL Server 2005 与 SQL Server 2008，则应该使用 ROW_NUMBER() 函数而非 OFFSET FETCH。因此，请创建与开发环境相适合的 SP。与传统的 SP 不同，此处创建的 SP 将包含三个参数，细节如下：
  　　@PageNumber 为将会返回的页码数（Page Number）
  　　@RowsPerPage 为每页的行数（Number of Rows）
  　　@TotalResords（输出参数）为总的记录
  

-- CREATE DATABASECREATE DATABASE TestPagingInASPNET;GO-- CREATE FIRST TABLECREATE TABLE AdministrativeUnits (    AdministrativeUnitID INT PRIMARY KEY IDENTITY(1, 1),    Name VARCHAR(50));GO-- CREATE SECOND TABLECREATE TABLE Cities (    CityID INT PRIMARY KEY IDENTITY(1, 1),    AdministrativeUnitID INT,    Name VARCHAR(50));GO-- CREATE THE STORED PROCEDURECREATE PROCEDURE SelectCitiesWithPaging    @PageNumber INT,    @RowsPerPage INT,    @TotalRows INT OUTPUTASBEGIN    SET NOCOUNT ON;    SELECT        @TotalRows = COUNT(*)    FROM        [AdministrativeUnits] [AU]    INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]    SELECT        [AU].[Name] [Administrative Unit],                [C].[Name] [City]    FROM        [AdministrativeUnits] [AU]    INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]    ORDER BY    [AU].[Name], [C].[Name]    OFFSET        ((@PageNumber - 1) * @RowsPerPage) ROWS FETCH NEXT @RowsPerPage ROWS ONLYENDGO-- CREATE THE STORED PROCEDURECREATE PROCEDURE SelectCitiesWithPagingOldSQLVersions    @PageNumber INT,    @RowsPerPage INT,    @TotalRows INT OUTPUTASBEGIN    SET NOCOUNT ON;    SELECT        @TotalRows = COUNT(*)    FROM         [AdministrativeUnits] [AU]    INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]    SELECT    *    FROM    (    SELECT        ROW_NUMBER() OVER (ORDER BY [AU].[Name], [C].[Name]) NUMBER,                            [AU].[Name] [Administrative Unit],                            [C].[Name] [City]                 FROM        [AdministrativeUnits] [AU]                 INNER JOIN    [Cities] [C] ON [AU].[AdministrativeUnitID] = [C].[AdministrativeUnitID]                 ) tbl    WHERE    Number BETWEEN ((@PageNumber - 1) * @RowsPerPage + 1) AND (@PageNumber * @RowsPerPage)ENDGO现在，讨论应用的前端部分
　　ASPX

• 　　在 Web 表单中绘制一个表格，其包含两个表格行（Table Rows）
  
• 　　在第一个表格行中推拽下放一个 GridView。此处无需启用分页，因为存储过程实现该功能。
  
• 　　在第二个表格行中，放置两个按钮来实现前页与后页之间的跳转。此外，为两个按钮创建点击事件。
  
• 　　在第三个表格行中，放置页面导航链接。
  
• 　　在下面；提供了 .aspx 文件中的代码。
  

                                                                                                                                                                                                                　　ASPX.cs
　　在 .aspx.cs 文件中，我们会创建两个函数。
　　GetAndBindData()

• 　　第一个函数将得到来自数据库的请求数据。请注意，我们以页码数与每页的行数为参数。
  
• 　　接收到的数据将填充在网格中。
  
• 　　在页面加载（Page Load）事件触发，且参数页面数（PageNumber）为1时，即会调用该函数。
  

　　CreatePager()

• 　　第二个函数将创建用于导航的链接。
  

private void GetAndBindData(Int32 PageNumber, Int32 RowsPerPage){    SqlConnection con = new SqlConnection(ConnectionString);    SqlCommand cmd = new SqlCommand();    cmd.CommandType = System.Data.CommandType.StoredProcedure;    cmd.CommandText = "SelectProjects";    cmd.Connection = con;    SqlParameter par1 = new SqlParameter();    par1.ParameterName = "PageNumber";    par1.DbType = System.Data.DbType.Int32;    par1.Direction = System.Data.ParameterDirection.Input;    par1.Value = PageNumber;    cmd.Parameters.Add(par1);    SqlParameter par2 = new SqlParameter();    par2.ParameterName = "RowsPerPage";    par2.DbType = System.Data.DbType.Int32;    par2.Direction = System.Data.ParameterDirection.Input;    par2.Value = RowsPerPage;    cmd.Parameters.Add(par2);    SqlParameter par3 = new SqlParameter();    par3.ParameterName = "TotalRows";    par3.DbType = System.Data.DbType.Int32;    par3.Direction = System.Data.ParameterDirection.Output;    cmd.Parameters.Add(par3);    SqlDataAdapter adp = new SqlDataAdapter();    adp.SelectCommand = cmd;    DataSet ds = new DataSet();    con.Open();    adp.Fill(ds);    Session["TotalRows"] = par3.Value.ToString();    GridView1.DataSource = ds.Tables[0];    GridView1.DataBind();}private void CreatePager(Int32 TotalRecords, Int32 PageNumber, Int32 RowsPerPage){    Int32 intIndex;    Int32 intPageNumber;    tdPage.InnerHtml = "";    intPageNumber = 1;    for (intIndex = 1; intIndex  intIndex) {        tdPage.InnerHtml += " " + intIndex.ToString() + " ";    }}protected void btnGridViewNext_Click(object sender, EventArgs e){    Int32 NewPageNumber = Convert.ToInt32(Session["PageNumber"]);    NewPageNumber++;    Session["PageNumber"] = NewPageNumber;    txtGridViewPageNumber.Text = Session["PageNumber"].ToString();    GetAndBindData(Convert.ToInt32(Session["PageNumber"]), 10);    btnGridViewPrevious.Enabled = true;}protected void btnGridViewGoToPageNumber_Click(object sender, EventArgs e){    Int32 NewPageNumber = Convert.ToInt32(txtGridViewPageNumber.Text);    Session["PageNumber"] = NewPageNumber;    txtGridViewPageNumber.Text = Session["PageNumber"].ToString();    GetAndBindData(Convert.ToInt32(Session["PageNumber"]), 10);    btnGridViewPrevious.Enabled = true;}要点总结
　　通过此方法，在用户改变页面索引时，开发者可以只获取相关数据进行展示，而非完整的数据集。这样，不仅可以从数据库中选出相关数据，在 GridView 中过滤数据所需的步骤也可以避免。从而切实提高并优化应用性能。
　　原文地址：http://www.codeproject.com/Articles/1078662/How-to-implement-Paging-in-ASP-NET-at-SQL-Query-Le
　　iyunv 助您轻松锁定 .NET 应用性能瓶颈，通过强大的 Trace 记录逐层分析，直至锁定行级问题代码。以用户角度展示系统响应速度，以地域和浏览器维度统计用户使用情况。想阅读更多技术文章，请访问 iyunv 官方博客。
　　本文转自 iyunv 官方博客
　　+