使用对象数据源优化分页和排序

Title:       Optimized Paging and Sorting using Object Data Source
Author:      Muhammad Akhtar Shiekh 
Email:       akhhttar@gmail.com
Member ID:   2243665
Language:    C# 2.0
Platform:    Windows, .NET
Technology:  ASP.NET
Level:       Intermediate
Description: An article on optimized paging and sorting in GridView.
Section      ASP.NET
SubSection   GridView
License:     CPL

• 下载源代码 - 340 Kb

引言

分页和排序是 ASP.NET GridView 最常用的功能。使用少量代码在 GridView 中实现这些功能非常容易。在本文中，我将演示使用传统方式对 GridView 进行分页和排序的性能缺点，然后我将演示“一种优化分页和排序的方法”。

分页和排序的传统步骤是什么？

通常，我们执行以下步骤来在 GridView 中启用分页和排序：

1. 将 GridView 的 AllowPaging 和 AllowSorting 属性分别设置为 True 以启用分页和排序，例如：

       <asp:GridView ID="GridView1" runat="server" AllowPaging="true" AllowSorting="true"  >
        </asp:GridView>
   

2. 设置 PageSize 属性以指定每页显示多少条记录。
3. 设置每个列的 SortExpression 属性。默认情况下，每个数据绑定列都将绑定的列名作为 SortExpression 属性的默认值。
4. 处理 GridView 的 PageIndexChanging 和 Sorting 事件以分别响应分页和排序操作，例如：

       <asp:GridView ID="GridView1" runat="server" AllowPaging="true" 
                   AllowSorting="true" onpageindexchanging="GridView1_PageIndexChanging" 
                   onsorting="GridView1_Sorting"  >
               </asp:GridView>
   

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
       {
           
   
       }
       protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
       {
   
       }
   

5. 在事件处理程序中放置一些逻辑来完成它们的工作
   
   a. 在 PageIndexChanging 事件处理方法中，我们通常从数据库或缓存中获取数据，并使用这些数据重新绑定我们的 Grid。重新绑定后，我们将 GridView 的 PageIndex 属性更改为新的页索引，以显示用户选择的页面。

        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
       {
           GridView1.DataSource = GetData(); // GetData() is a method that will get Data from Database/Cache/Session to display in Grid.
           GridView1.DataBind();
   
           GridView1.PageIndex = e.NewPageIndex;
       }
   
   

   b. 在 Sorting 事件处理方法中，我们根据排序表达式从数据源（数据源可以是数据库/缓存/会话等）获取排序数据，然后重新绑定 Grid 以显示排序后的记录。

        protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
       {
           GridView1.DataSource = GetData(e.SortExpression); // GetData(string sortBy) is a method that will get Data from Database/Cache/Session to display in Grid.
   
           GridView1.DataBind();
       }
    
    

就是这样。

缺点

在传统的分页和排序方式中，我们获取完整的数据集，而不是获取显示在当前/请求页面上所需的部分数据。正如您所看到的，在每次 pageIndexChanging 调用中，我们都从数据源获取所有数据，然后将其绑定到 GridView。**理想情况下，我们应该只获取显示在请求页面上所需的数据。**

嗯……听起来不错，但是怎么做呢？

您脑海中可能会出现的问题是：“理论上，我们应该只获取所需的数据，这听起来不错，但实际上，如果我们将仅一页数据绑定到 GridView，它会认为这是它需要显示的唯一数据，那么 GridView 如何显示页码和总记录数呢？这是一个真实的问题，所以让我们试着回答这个问题！”

一种优化的分页和排序方式

如本文开头所述，我们讨论了在 ASP.NET GridView 中实现分页和排序的传统 5 个步骤。在此提出的解决方案中，我们将**原封不动地**使用前 3 个步骤，而不是自己执行第 4 和第 5 个步骤，我们将使用 ObjectDataSource，它将以优化的方式为我们执行这些步骤。

高级概述

我们将在数据库和表示层优化代码

在数据库层面，我们将以一种只返回一页记录的方式编写存储过程。该存储过程将页面大小、页索引和排序表达式作为输入参数，并返回特定页索引的排序记录。

在表示层，我们将使用 ObjectDataSource 的虚拟分页功能来优化分页。虚拟分页不是 Microsoft 定义的术语，我之所以使用它，是因为 ObjectDataSource 暴露了一些属性和方法，允许我们只将一页数据绑定到 GridView，并定义数据库中的总记录数（而不是一页中的），以便 GridView 可以提取出需要在 GridView 页面区域中显示的页面总数。在接下来的部分中，我们将看到这些属性和方法是什么？以及如何使用它们？

如果您不熟悉 ObjectDataSource，我建议您先阅读一些相关文章。以下是一些文章：

• http://www.eggheadcafe.com/tutorials/aspnet/884994b3-0ec7-4e60-9a8f-cbf9127ef50b/basics-objectdatasource.aspx
• http://www.c-sharpcorner.com/UploadFile/mahesh/ObjectDataSource08192005140915PM/ObjectDataSource.aspx

实现细节

数据库层

我们有一个包含以下架构的员工表：

我们编写了以下存储过程，它有两个 SELECT 语句。第一个 SELECT 语句将返回 Employee 表中员工的总数，第二个动态 SELECT 语句将根据提供的起始索引、页面大小和排序参数返回一页排序记录。

Create PROCEDURE spGetAllEmployee
	(
	@startIndex		int,
	@pageSize		int,
	@sortBy		nvarchar(30),
	@totalEmployees	int OUTPUT		
	)
AS
	SET NOCOUNT ON 

 DECLARE
    @sqlStatement nvarchar(max),    
    @upperBound int

  IF @startIndex  < 1 SET @startIndex = 1
  IF @pageSize < 1 SET @pageSize = 1
  
  SET @upperBound = @startIndex + @pageSize
 

 Select @totalEmployees=Count(*) From Employee
  
  SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
                FROM (
                      SELECT  ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
                      FROM    Employee
                     ) AS E
                WHERE  rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
                       rowNumber <  ' + CONVERT(varchar(9), @upperBound)
  exec (@sqlStatement)

在上面的存储过程中，我想解释一下 ROW_NUMBER() 函数，它使我们能够只选择一页数据。ROW_NUMBER() 方法包含在 TSQL 的 2005 版本中，它实际上在选定的记录集中添加了一个整数列，其中包含每条记录的记录号。这看起来很简单，但实际上在执行嵌套查询时非常有用。正如我们在存储过程中所做的那样，在嵌套查询中，我们选择所有员工记录并按提供的排序表达式排序，并使用 ROW_NUMBER() 方法为每条记录添加一个行号，在外层查询中，我们使用下限和上限索引过滤结果行，以便我们只返回位于下限和上限之间的行。

数据访问层

在数据访问层，我们将编写一个类，负责调用 spGetAllEmployee 存储过程以获取员工记录，并将员工列表返回给业务逻辑层。为了避免文章的复杂性和长度，我只发布了用于从数据库获取记录的代码，我没有发布任何帮助代码/类，但是完整代码可供下载。

public List<EmployeeInfo> GetAllEmployee(int startIndex, int pageSize, string sortBy,ref int totalEmployees)        {
            IDbConnection connection=null ;
            IDbCommand selectCommand=null ;
            List<EmployeeInfo> employeeInfoList;
            DataSet employeeDS = new DataSet();
            IDbDataAdapter dataAdapter = DataObjectFactory.CreateDataAdapter();
            try
            {
                using (connection = DataObjectFactory.CreateConnectionObject())
                {
                    using (selectCommand = DataObjectFactory.CreateStoredProcedureCommand(connection, "spGetAllEmployee"))
                    {

                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@startIndex", SqlDbType.Int, startIndex));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@pageSize", SqlDbType.Int, pageSize));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@sortBy", SqlDbType.NVarChar, 30, sortBy));
                        selectCommand.Parameters.Add(DataObjectFactory.CreateCommandParameter("@totalEmployees", SqlDbType.Int, 0));
                        ((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Direction = ParameterDirection.Output;

                        if (connection.State != ConnectionState.Open)
                            connection.Open();

                        dataAdapter.SelectCommand = selectCommand;

                        dataAdapter.Fill(employeeDS);

                        totalEmployees = Convert.ToInt32(((SqlParameter)selectCommand.Parameters["@totalEmployees"]).Value);
                    }
                }
            }
            catch (SqlException ex)
            {
                if (connection.State != ConnectionState.Closed)
                    connection.Close();
                DALException mineException = new DALException();
                mineException.ConnectingString = connection.ConnectionString;
                mineException.StoredProcedureName = selectCommand.CommandText;
                mineException.Source = "GetAllEmployee";
                throw mineException;
                
            }

            employeeInfoList =  ConvertorUtility.ConvertToEmployeeInfoCollection(employeeDS);

            return employeeInfoList;
           
        }

您可能会注意到，GetAllEmployee 的最后一个参数 totalEmployee 是按引用传递的，这是出于优化考虑，我们不希望执行两次单独的数据库调用来获取员工记录和总计数。出于同样的原因，我们从一个存储过程中返回这两项（员工数据和总计数）。在表示层中，您将更清楚地了解这种方法的必要性以及它将如何帮助我们。

业务逻辑层

业务逻辑层将只调用数据访问层代码来获取记录。源代码可在下载中获取。

表示层

正如我们之前在文章中讨论过的，我们将使用 ObjectDataSource 的虚拟分页功能。Object Data Source 暴露出一些有趣的属性，用于虚拟分页。为了从这些属性中受益，您应该将 ObjectDataSource 的 EnablePaging 属性设置为 True。这些属性是：

• StartRowIndexParameterName：指定 ObjectDataSource 绑定类型的 Select 方法的参数名称。当用户更改页索引时，ObjectDataSource 会将值传递给此参数。例如，如果 Grid View 页面大小为 10，用户单击第 3 页以查看第 3 页，则 ObjectDataSource 将使用（页面大小 * 页索引）公式计算 StartRowIndexParameter 的值，在这种情况下将是 10 * 3 = 30。此属性的默认值为 startRowIndex，这意味着如果我们不为此属性指定任何值，则 Select 方法应该具有 startRowIndex 参数。
• MaximumRowsParameterName：指定 ObjectDataSurce 绑定类型的 Select 方法的参数名称。当用户更改页索引时，ObjectDataSource 会将值传递给此参数。ObjectDataSource 从 GridView 的 PageSize 属性获取其值。此属性的默认值为 maximumRow。这意味着如果我们不为此属性指定任何值，则 Select 方法应该具有 maximumRow 参数。
• SelectCountMethod：指定 ObjectDataSource 类型的方法名称。此方法将由 ObjectDataSource 调用，以获取数据库中的总记录数。此计数将帮助 ObjectDataSource 在 Grid 中创建虚拟分页。例如，如果 GridView 页面大小为 10，并且 SelectCountMethod 返回 100 作为数据库中员工的总数，则 ObjectDataSource 将在 GridView 页面区域中显示 10 个页面索引（尽管我们没有从数据库中获取所有 100 条记录）
• SortParameterName：指定 ObjectDataSource 绑定类型的 Select 方法的参数名称。当用户单击任何列标题以根据该列排序数据时，ObjectDataSource 会将值传递给此参数。ObjectDataSource 从特定 GirdView 列的 SortExpression 属性获取值。

现在，如果您将这三个属性放在一起看，您就会明白 SelectCountMethod 将用于在 GridView 中显示虚拟页面索引，并且在每次页面索引更改时，我们将使用 StartRowIndexParameterName 和 MaximumRowsParameterName 参数的值来查询数据库以仅获取所需的页面数据。

正如您所知，我们已经编写了接受这些参数并只返回一页排序数据的存储过程、数据访问和业务逻辑代码。所以现在是时候通过将这些参数值传递给它们来使用它们了。

理论够多了，现在我们来谈谈 C# 和 ASPX 的实际操作：

ASPX：GridView 和 ObjectDataSource 的标记

<asp:GridView ID="GridView1" DataKeyNames="EmployeeID" runat="server" AllowPaging="True" AutoGenerateColumns="False"
        CellPadding="4" DataSourceID="ObjectDataSource1" ForeColor="#333333" GridLines="None" AllowSorting="True" PageSize="5" >
        <RowStyle BackColor="#EFF3FB" />
        <Columns>
            <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" ReadOnly="true" SortExpression="EmployeeID" />
            <asp:BoundField DataField="EmployeeCode" HeaderText="EmployeeCode" SortExpression="EmployeeCode" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
            <asp:BoundField DataField="Salary" HeaderText="Salary" SortExpression="Salary" />
        </Columns>
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#2461BF" />
        <AlternatingRowStyle BackColor="White" />
    </asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" SelectMethod="GetAllEmployees" EnablePaging="true"
        TypeName="EmployeeData" StartRowIndexParameterName="startIndex" MaximumRowsParameterName="pageSize" SortParameterName="sortBy" SelectCountMethod="GetTotalEmployeesCount" >
    </asp:ObjectDataSource>

EmployeeData.cs：一个绑定到 ObjectDataSource 的类

public class EmployeeData
{

    private static int employeesCount;
    public EmployeeData()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    [DataObjectMethod(DataObjectMethodType.Select)]
    public static List<EmployeeInfo> GetAllEmployees(int startIndex, int pageSize, string sortBy)
    {
        EmployeeBLL objEmployeeBLL = new EmployeeBLL();
        List<EmployeeInfo> result;
        int totalEmployees=0;

        if (string.IsNullOrEmpty(sortBy))
            sortBy = "EmployeeID";

        result = objEmployeeBLL.GetAllEmployee(startIndex, pageSize, sortBy, ref totalEmployees);
        employeesCount = totalEmployees;
        return result;

    }

    public static int GetTotalEmployeesCount()
    {
        return employeesCount;
    }


}

这里没什么特别需要解释的，因为我们正在调用已经解释过的代码，通过传递 ObjectDataSource 提供的值来获取一页排序数据。但有一点我想从优化角度解释，我们在 GetAllEmployees() 方法中获取 employeesCount，而在 GetTotalEmployeesCount() 中我们只返回该值，而不是从数据库中获取计数。人们通常做的是，他们发送单独的数据库调用来获取计数，这是一个不必要的步骤，因为我们可以以一种方式编写存储过程、数据访问和业务逻辑层，在这种方式中我们可以在一次数据库调用中获取实际数据和总计数。

比较

我比较了分页和排序的传统实现和优化实现，它们之间有巨大的差异，以下是结果：

从这个性能表中可以看出，随着数据量的增加，未优化的实现需要更多时间从数据库中获取记录，而优化的方式没有明显的差异。

记录数	优化分页（秒）	未优化分页（秒）
500000	0.21	07.41
1000000	0.28	17.27
2000000	0.32	36.28
3000000	0.33	54.03

记录数	优化排序（秒）	未优化排序（秒）
500000	01.61	10.14
1000000	03.13	23.22
2000000	09:38	47.39
3000000	13.12	70:25

* 分页时间在 GridView 页面索引更改事件中计算。
* 排序时间在 GridView 排序事件中计算。

摘要

在本文中，我们学习了一个端到端的解决方案，用于优化 ASP.NET GridView 中的分页和排序。在数据库层，我们使用了 ROWNUMBER() 方法，它帮助我们只选择一页数据。在表示层，我们通过使用 ObjectDataSource 的属性 StartRowIndexParameterName、MaximumRowsParameterName、SelectCountMethod、SortParameterName 来使用其虚拟分页功能。希望它能有所帮助。

反馈

您的反馈将真正帮助我做得更好，所以请留下您的评论、建议。您也可以通过 akhhttar at gmail dot com 向我发送任何查询。

编程愉快！！！