65.9K
CodeProject 正在变化。 阅读更多。
Home

如何在 ASP.NET 中在 SQL 查询级别实现分页

Aqeeel
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.15/5 (12投票s)

2016 年 2 月 16 日

CPOL

3分钟阅读

viewsIcon

40933

虽然 GridView 提供了一种实现分页的方法，但是当记录数量非常大时，我们需要在查询级别对其进行优化。

引言

在 SQL 查询级别而不是 GridView 中实现 ASP.NET 分页

背景

虽然 GirdView 是一个在 ASP.NET WebForm 上显示数据的非常强大的工具。 它还帮助我们在结果集数量很大时实现分页。 然而，在后端； 将获取完整的数据，然后提取相关数据并显示在 WebForm 的 GridView 中。 在这种情况下，相关数据是完全提取的数据的一小部分。 额外的处理是对处理能力、内存和时间的完全浪费。 在本文中，我们将了解如何仅从数据库中提取所需的数据，以避免浪费额外的处理能力、内存和时间。

下图详细说明了如何从数据库中获取完整的数据。 在呈现过程中，提取相关数据并填充到 GridView 中。

下图解释了如何从数据库中提取过滤或相关数据，从而生成数据库产生较小的数据集。 并且在 Web 应用程序中，相同的数据集被填充到 GridView 中，没有任何提取。

实现

工具

这使用 SQL Server 2014 和 Visual Studio 2015 实现。 早期版本的 SQL Server，尤其是 2012 之前的版本，SQL 不支持 FETCH，因此可以使用 ROW NUMBER 实现相同的功能。

让我们先设置后端

  1. 创建一个名为 TestPagingInASPNET 的数据库，
  2. 创建两个名为“AdministrativeUnits”和“Cities”的表。
  3. 创建将从数据库中获取数据的存储过程。 请注意，我创建了两个 SP。 第一个名为“SelectCitiesWithPaging”，第二个名为“SelectCitiesWithPagingOldSQLVersions”。 由于我在 SQL Server 2014 中实现了解决方案，所以在第一个 SP 中我们使用 OFFSET FETCH 语句。 对于较早的版本，如 SQL Server 2005 和 SQL Server2008，我们将使用 ROW_NUMBER() 函数代替 OFFSET FETCH。 因此创建适合您需求的 SP。 与传统的 SP 不同，此 SP 将有三个参数。 详细信息如下
    • @PageNumber 标识将返回的页面编号
    • @RowsPerPage 标识每页的行数，以及
    • @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 表单上绘制一个带有两行表格的表格
  • 在第一行表格中拖放一个 GridView。 无需启用分页，因为这已在我们的存储过程中处理
  • 在第二行表格中放置两个按钮，用于导航到上一页和下一页。 还要创建两个按钮的单击事件
  • 在第三行表格中，我们将填充页面导航链接
  • 下面； 找到在 .aspx 文件中创建的代码
    <table style="width:100%;">
        <tr>
            <td>
                <asp:GridView ID="GridView1" runat="server"></asp:GridView>
            </td>
        </tr>
        <tr>
            <td style="text-align:center;">
                <asp:Button ID="btnGridViewPrevious" runat="server" OnClick="btnGridViewPrevious_Click" Text="&lt;" />
                <asp:TextBox ID="txtGridViewPageNumber" runat="server"></asp:TextBox>
                <asp:Button ID="btnGridViewGoToPageNumber" runat="server" Text="Go to Page" OnClick="btnGridViewGoToPageNumber_Click" />
                <asp:Button ID="btnGridViewNext" runat="server" OnClick="btnGridViewNext_Click" Text="&gt;" />
            </td>
        </tr>
        <tr>
            <td style="text-align:center;" runat="server" id="tdPage">

            </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 中显示的数据所需的处理。 因此，可以获得更好和优化的性能。

© . All rights reserved.