【编者按】本文作者为来自巴基斯坦的软件开发工程师 Aqeeel,主要介绍了在 SQL 查询层面实现 ASP.NET 应用的分页方法。

本文系 OneAPM 工程师编译呈现,以下为正文。

GridView 提供了一种实现分页的方法。但是,随着记录的不断扩大,我们需要在查询层面进行优化。

简介

在 SQL 查询层面实现 ASP.NET 程序分页,而不借助 GridView。

背景

无可否认,GridView 是在 ASP.NET Web 表单展示数据的强大工具,它能在结果集较大时实现分页。然而,后端会获取完整的数据,抽取出相关数据,然后通过 GridView 展示在 Web 表单中。在这种情况下,相关数据只是完全抽取数据的一小部分。这些多余的数据造成了处理能力、内存空间以及时间的极大浪费。在本文中,我们将展示如何仅从数据库抽取所需数据,从而避免这些浪费。

下图展示了从数据库中获取完整数据的方式。在渲染阶段,相关数据会被抽取出来,填充到 GridView 中。

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

具体实现

工具

本例借助 SQL Server 2014 与 Visual Studio 2015 实现。2012 之前的 SQL Server 版本不支持 FETCH,但是使用 ROW NUMBER 可以达到同样的效果。

首先进行后端设置:

  1. 创建名为 TestPagingInASPNET 的数据库,
  2. 创建名为 AdministrativeUnits 与 Cities 的两张表。
  3. 创建存储过程(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 DATABASE CREATE DATABASE TestPagingInASPNET; GO

-- CREATE FIRST TABLE CREATE TABLE AdministrativeUnits ( AdministrativeUnitID INT PRIMARY KEY IDENTITY(1, 1), Name VARCHAR(50) ); GO

-- CREATE SECOND TABLE CREATE TABLE Cities ( CityID INT PRIMARY KEY IDENTITY(1, 1), AdministrativeUnitID INT, Name VARCHAR(50) ); GO

-- CREATE THE STORED PROCEDURE CREATE PROCEDURE SelectCitiesWithPaging @PageNumber INT, @RowsPerPage INT, @TotalRows INT OUTPUT AS BEGIN

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 ONLY

END GO

-- CREATE THE STORED PROCEDURE CREATE PROCEDURE SelectCitiesWithPagingOldSQLVersions @PageNumber INT, @RowsPerPage INT, @TotalRows INT OUTPUT AS BEGIN

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)

END GO ```

现在,讨论应用的前端部分

ASPX

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

```

</asp:GridView>
</asp:TextBox>

        </td>
    </tr>
</table>

```

ASPX.cs

在 .aspx.cs 文件中,我们会创建两个函数。

GetAndBindData()

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

CreatePager()

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

``` 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 <= TotalRecords; intIndex+=10)
{
    tdPage.InnerHtml += " <a href=''>" + intPageNumber.ToString() + "</a> ";
    intPageNumber++;
}

if (TotalRecords > intIndex) {
    tdPage.InnerHtml += " <a href=''>" + intIndex.ToString() + "</a> ";
}

}

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.wking-china.com/xpjylc/Articles/1078662/How-to-implement-Paging-in-ASP-NET-at-SQL-Query-Le

OneAPM 助您轻松锁定 .NET 应用性能瓶颈,通过强大的 Trace 记录逐层分析,直至锁定行级问题代码。以用户角度展示系统响应速度,以地域和浏览器维度统计用户使用情况。想阅读更多技术文章,请访问 OneAPM 官方博客本文转自