C# 在 Asp.Net Gridview 中对数据进行分组

Grouping Data in Asp.Net Gridview

提问by Nag

I have two Stored Procedures which are returning two sets of related data. The Data is like this. First Procedure returns data like this

我有两个存储过程，它们返回两组相关数据。数据是这样的。第一个过程返回这样的数据

ISSUE_ID          ISSUETYPE          
-------------------------------------
1            ISSUE 1 TYPE
2            ISSUE 2 TYPE
3            ISSUE 3 TYPE
4            ISSUE 4 TYPE

Second Procedure returns data like this based on ISSUE_ID

第二个程序根据 ISSUE_ID 返回这样的数据

HEADER ID          HEADER NAME            ISSUE_ID       
-----------------------------------------------------
 1                 HEADER 1 NAME               1   
 2                 HEADER 2 NAME               1
 3                 HEADER 3 NAME               2   
 4                 HEADER 4 NAME               2   
 5                 HEADER 5 NAME               3

Thing is How can i group this based on ISSUE_IDand display it in groups in gridview using both stored procedures. I have googled in lot forums and i found the options was nested gridview. Can i achive this without using this nested gridview.

问题是我如何ISSUE_ID使用这两个存储过程将其分组并在 gridview 中分组显示。我在很多论坛上搜索过，我发现选项是嵌套的 gridview。我可以在不使用此嵌套 gridview 的情况下实现此目的吗？

Finally I want to display in gridview like this.

最后我想像这样在gridview中显示。

ISSUE 1 TYPE
-----------------------------
            HEADER 1 NAME                 
            HEADER 2 NAME 
ISSUE 2 TYPE
-----------------------------
            HEADER 3 NAME                 
            HEADER 4 NAME                  
ISSUE 3 TYPE
-----------------------------
            HEADER 5 NAME                 

Thank a Million in advance.. Need some suggestions to achive this.

提前感谢一百万.. 需要一些建议来实现这一目标。

回答by mijaved

An example of grouping in ASP.Net GridView

ASP.Net GridView中分组的一个例子

<asp:GridView ID="grdViewOrders" CssClass="serh-grid" runat="server" AutoGenerateColumns="False" 
              TabIndex="1" Width="100%" CellPadding="4" ForeColor="Black" GridLines="Vertical" 
              BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px"
              OnRowDataBound="grdViewOrders_RowDataBound" OnRowCommand="grdViewOrders_RowCommand" 
              OnRowCreated="grdViewOrders_RowCreated">
    <Columns>       
        <asp:BoundField DataField="OrderID" HeaderText="OrderID" SortExpression="OrderID" />            
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" SortExpression="ProductName" />            
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" SortExpression="UnitPrice" />          
        <asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity" />         
        <asp:BoundField DataField="Discount" HeaderText="Discount" SortExpression="Discount" />         
        <asp:BoundField DataField="Amount" HeaderText="Amount" SortExpression="Amount" />                       
    </Columns>      
    <FooterStyle BackColor="#CCCC99" />     
    <SelectedRowStyle CssClass="grid-sltrow" />     
    <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" BorderStyle="Solid" BorderWidth="1px" BorderColor="Black" />        
</asp:GridView>

Notes:

笔记：

• The main logic is in the RowCreated and RowDataBound events of the GridView.

• While iterating through all the rows I am

  • peeking at the CustomerId (Primary index) and checking the other rows.
  • Keeping track of a running GrandTotal(s)
  • Keeping track of a running SubTotal(s)

• At every point the Primary index changes while iterating through the result-set:

  • Add SubTotal(s) row
  • Reset SubTotal(s) ready for next group

• Heading displayed as a new row in the GridView.

• 主要逻辑在GridView的RowCreated和RowDataBound事件中。

• 在遍历所有行时，我

  • 查看 CustomerId（主索引）并检查其他行。
  • 跟踪正在运行的 GrandTotal(s)
  • 跟踪正在运行的小计

• 在迭代结果集时，主索引的每一点都会发生变化：

  • 添加小计行
  • 重置小计为下一组做好准备

• 标题在 GridView 中显示为新行。

GridView helper

GridView 助手

Using the GridViewHelper

使用 GridViewHelper

Below we will see some GridViewHelper samples. First we show the grid to which the groups and summaries will be created. The sample data comes from Northwind database, with a few modifications:

下面我们将看到一些 GridViewHelper 示例。首先，我们显示将创建组和摘要的网格。样本数据来自 Northwind 数据库，做了一些修改：

To create a summary for the ItemTotal column we need only the promised 2 lines of code:

要为 ItemTotal 列创建摘要，我们只需要承诺的 2 行代码：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.RegisterSummary("ItemTotal", SummaryOperation.Sum);
}

First we create the GridViewHelper setting the grid in which it will act in the constructor. Then we register the summary specifying the column name and the summary operation to be performed. The result is below:

首先，我们创建 GridViewHelper，设置它将在构造函数中起作用的网格。然后我们注册指定列名和要执行的汇总操作的汇总。结果如下：

In this sample a new line was added to display the summary. Another option is to use the footer row to display the summary instead of creating a new one. When a new row is added to the grid, only the required cells to display the summarized columns are created. Using the footer, all the cells are created. In case of group summaries, generation of all cells or only the needed cells is a group attribute.

在此示例中，添加了一个新行以显示摘要。另一种选择是使用页脚行来显示摘要而不是创建一个新摘要。将新行添加到网格时，只会创建显示汇总列所需的单元格。使用页脚创建所有单元格。在组摘要的情况下，生成所有单元格或仅生成所需单元格是组属性。

Now we will create a group. The code is shown below:

现在我们将创建一个组。代码如下所示：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.RegisterGroup("ShipRegion", true, true);
    helper.ApplyGroupSort();
}

The first parameter of RegisterGroup method defines the columns to which the group must be created. It's also possible to create a composite group, consisting of an array of columns. The second parameter specifies if the group is automatic. In this case a new row will be created automatically for the group header. The third parameter specifies if the group columns must be hidden. The ApplyGroupSort method sets the sort expression of the grid as being the group columns, in this case, ShipRegion. This is required to grouping works properly, except if the data comes ordered from database.

RegisterGroup 方法的第一个参数定义必须为其创建组的列。也可以创建一个复合组，由一组列组成。第二个参数指定组是否是自动的。在这种情况下，将自动为组标题创建一个新行。第三个参数指定是否必须隐藏组列。ApplyGroupSort 方法将网格的排序表达式设置为组列，在本例中为 ShipRegion。这是分组正常工作所必需的，除非数据来自数据库。

In the above sample the column ShipRegion have been hidden:

在上面的示例中，列 ShipRegion 已隐藏：

Let's make something more interesting, let's add a summary to the created group. We need just one more line to register the summary to the group:

让我们做一些更有趣的事情，让我们为创建的组添加一个摘要。我们只需要多一行就可以将摘要注册到组中：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.RegisterGroup("ShipRegion", true, true);
    helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipRegion");
    helper.ApplyGroupSort();
}

This time, the RegisterSummary method takes another parameter. The parameter specifies the name of the group to which the summary must be created. Group name is automatically generated from the group column names. If the group has only one column, group name will be the name of that column. If the group has more than one column, the group name will be the ordered concatenation of the columns that composes the group, joined with a plus sign ("+"): "ShipRegion+ShipName".

这一次，RegisterSummary 方法采用另一个参数。该参数指定必须为其创建摘要的组的名称。组名是从组列名自动生成的。如果组只有一列，则组名将是该列的名称。如果组有多个列，组名将是组成组的列的有序串联，并用加号（“+”）连接：“ShipRegion+ShipName”。

We can see below the grid with grouping and a summary for the group:

我们可以在带有分组的网格下方看到该组的摘要：

It's possible to create more than one group in the grid, simulating a hierarchical grouping, as seen below:

可以在网格中创建多个组，模拟分层分组，如下所示：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.RegisterGroup("ShipRegion", true, true);
    helper.RegisterGroup("ShipName", true, true);
    helper.ApplyGroupSort();
}

Result:

结果：

Visualization is compromised when there is more than one group. GridViewHelper has events to allow easy implementation of visual or functional adjusts. The list of events follows below:

当有多个组时，可视化会受到影响。GridViewHelper 具有允许轻松实现视觉或功能调整的事件。事件列表如下：

• GroupStart: Occurs when a new group starts, it means, when new values are found in the group column.
• GroupEnd: Occurs in the last row of the group
• GroupHeader: Occurs when an automatic header row is added for the group. The event is not triggered if the group is not automatic.
• GroupSummary: Occurs when the summary row is generated for the group. The event is not triggered if the group is not automatic, but will be triggered if the group is a suppression group (will be seen later on).
• GeneralSummary: Occurs after the general summaries be calculated. If the summary is automatic the event occurs after the summary row be added and after the summary values be placed in the row.
• FooterDataBound: Occurs in the footer databinding.

• GroupStart：在新组开始时发生，这意味着在组列中找到新值时。
• GroupEnd：发生在组的最后一行
• GroupHeader：在为组添加自动标题行时发生。如果组不是自动的，则不会触发该事件。
• GroupSummary：在为组生成汇总行时发生。如果该组不是自动的，则不会触发该事件，但如果该组是抑制组（稍后会看到），则会触发该事件。
• GeneralSummary：在计算一般摘要之后发生。如果汇总是自动的，则在添加汇总行并将汇总值放置在行中之后发生事件。
• FooterDataBound：发生在页脚数据绑定中。

With a few more lines of code we can improve the visual aspect of the grid:

再多写几行代码，我们就可以改善网格的视觉效果：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.RegisterGroup("ShipRegion", true, true);
    helper.RegisterGroup("ShipName", true, true);
    helper.GroupHeader += new GroupEvent(helper_GroupHeader);
    helper.ApplyGroupSort();
}

private void helper_GroupHeader(string groupName, object[] values, GridViewRow row)
{
    if ( groupName == "ShipRegion" )
    {
        row.BackColor = Color.LightGray;
        row.Cells[0].Text = "  " + row.Cells[0].Text;
    }
    else if (groupName == "ShipName")
    {
        row.BackColor = Color.FromArgb(236, 236, 236);
        row.Cells[0].Text = "     " + row.Cells[0].Text;
    }
}

The grid after the cosmetics:

化妆品后的格子：

More grouping options

更多分组选项

There are two more interesting samples. The first presents a composite group. The second defines a suppress group, that has the same behavior of the sql GROUP BY clause. The repeating values are suppressed, and a summary operation is performed on the other columns.

还有两个更有趣的例子。第一个提出了一个复合组。第二个定义了一个抑制组，它具有与 sql GROUP BY 子句相同的行为。重复值被抑制，并对其他列执行汇总操作。

Below we can see the code and the grid appearance for the composite group:

下面我们可以看到复合组的代码和网格外观：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    string[] cols = new string[2];
    cols[0] = "ShipRegion";
    cols[1] = "ShipName";
    helper.RegisterGroup(cols, true, true);
    helper.ApplyGroupSort();
}

We can add a summary to the group. This time we will define an average operation and add a label to indicate the operation:

我们可以向组中添加摘要。这次我们将定义一个平均操作并添加一个标签来指示该操作：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    string[] cols = new string[2];
    cols[0] = "ShipRegion";
    cols[1] = "ShipName";
    helper.RegisterGroup(cols, true, true);
    helper.RegisterSummary("ItemTotal", SummaryOperation.Avg, "ShipRegion+ShipName");
    helper.GroupSummary += new GroupEvent(helper_GroupSummary);
    helper.ApplyGroupSort();
}

private void helper_GroupSummary(string groupName, object[] values, GridViewRow row)
{
    row.Cells[0].HorizontalAlign = HorizontalAlign.Right;
    row.Cells[0].Text = "Average";
}

The last sample will create a suppress group. It's important to mention that if a suppress group is defined, no other group may be created. In the same way, if there is already a group defined, we can't create a suppress group and an exception will be raised if we try it.

最后一个示例将创建一个抑制组。值得一提的是，如果定义了抑制组，则不能创建其他组。同理，如果已经定义了一个组，我们就不能创建一个抑制组，如果我们尝试它会引发异常。

Below we can see the code and the grid appearance for the suppress group:

下面我们可以看到抑制组的代码和网格外观：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.SetSuppressGroup("ShipName");
    helper.RegisterSummary("Quantity", SummaryOperation.Sum, "ShipName");
    helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipName");
    helper.ApplyGroupSort();
}

No value is displayed for the columns that don't have a summary operation defined. This makes sense because GridViewHelper doesn't know how to proceed to summarize the values found in the group rows to a unique value. This reminds the certain known message:

对于未定义汇总操作的列，不显示任何值。这是有道理的，因为 GridViewHelper 不知道如何继续将组行中找到的值汇总为唯一值。这提醒了某些已知消息：

"Column 'column_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

“选择列表中的列 'column_name' 无效，因为它不包含在聚合函数或 GROUP BY 子句中。”

It doesn't make sense to display the columns that don't have a summary operation, and to hide them we need to call a method:

显示没有汇总操作的列是没有意义的，为了隐藏它们，我们需要调用一个方法：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.SetSuppressGroup(rdBtnLstGroup.SelectedValue);
    helper.RegisterSummary("Quantity", SummaryOperation.Sum, "ShipName");
    helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipName");
    helper.SetInvisibleColumnsWithoutGroupSummary();
    helper.ApplyGroupSort();
}

I know, it's a big big name! The resulting grid can be seen below:

我知道，这是一个很大的名字！生成的网格如下所示：

Summary operations

汇总操作

GridViewHelper has three built-in summary operations: sum, average and row count. A very useful feature is the possibility of define custom summary operations. To achieve this we need to provide two methods to the GridViewHelper. A method will be called for each row found in the grid (or group) and the other will be called to retrieve the result of the summary operation. Below we have a sample of a custom summary operation. The semi-dummy operation will return the minimum value found:

GridViewHelper 内置了三个汇总操作：sum、average 和 row count。一个非常有用的功能是可以定义自定义汇总操作。为了实现这一点，我们需要为 GridViewHelper 提供两个方法。将为网格（或组）中找到的每一行调用一个方法，并调用另一个方法来检索汇总操作的结果。下面我们有一个自定义汇总操作的示例。半虚拟操作将返回找到的最小值：

private List<int> mQuantities = new List<int>();

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.RegisterSummary("Quantity", SaveQuantity, GetMinQuantity);
}

private void SaveQuantity(string column, string group, object value)
{
    mQuantities.Add(Convert.ToInt32(value));
}

private object GetMinQuantity(string column, string group)
{
    int[] qArray = new int[mQuantities.Count];
    mQuantities.CopyTo(qArray);
    Array.Sort(qArray);
    return qArray[0];
}

In the code above we can see the required methods signatures. Both receive the summarized group and column names. If the summary is not relative to a group, the group parameter will be null. The method that is called for each row found in the grid, receives also the value of the column in the current row.

在上面的代码中，我们可以看到所需的方法签名。两者都接收汇总的组名和列名。如果摘要与组无关，则组参数将为空。为网格中找到的每一行调用的方法也接收当前行中列的值。

The resulting grid can be seen below :

生成的网格如下所示：

Limitations

限制

In one sample we said that we can simulate a hierarchical grouping. Although the grid appears to present a hierarchical grouping, the actual implementation isn't hierarchical. There's no group or subgroup. There are only sequentially registered groups. This becomes a problem if we need to create a summary for an inner group. Below we can see what happens in this situation:

在一个示例中，我们说我们可以模拟分层分组。尽管网格似乎呈现分层分组，但实际实现并不是分层的。没有组或子组。只有按顺序注册的组。如果我们需要为内部组创建摘要，这就会成为一个问题。下面我们可以看到在这种情况下会发生什么：

protected void Page_Load(object sender, EventArgs e)
{
    GridViewHelper helper = new GridViewHelper(this.GridView1);
    helper.RegisterGroup("ShipRegion", true, true);
    helper.RegisterGroup("ShipName", true, true);
    helper.RegisterSummary("ItemTotal", SummaryOperation.Sum, "ShipName");
    helper.RegisterSummary("ItemTotal", SummaryOperation.Sum);
    helper.GroupSummary += new GroupEvent(helper_Bug);
    helper.ApplyGroupSort();
}

private void helper_Bug(string groupName, object[] values, GridViewRow row)
{
    if (groupName == null) return;

    row.BackColor = Color.Bisque;
    row.Cells[0].HorizontalAlign = HorizontalAlign.Center;
    row.Cells[0].Text = "[ Summary for " + groupName + " " + values[0] + " ]";
}

As we can see, the summary is created after the header of the outer group. This occurs because the event sequence is:

如我们所见，摘要是在外部组的标题之后创建的。发生这种情况是因为事件序列是：

Group1_Start
Group1_End
Group2_Start
Group2_End

To a hierarchical grouping, the event sequence should be:

对于分层分组，事件序列应该是：

Group1_Start
Group2_Start
Group2_End
Group1_End

Implementation

执行

GridViewHelper was implemented as a standalone class instead of an inherited class. This makes possible to use the GridViewHelper with any GridView, and doesn't force the developer to inherit a specific GridView, what could affect the classes design. There's another four classes in the solution: GridViewSummary, GridViewGroup, GridViewSummaryList and GridViewGroupList. The "list" classes were created to allow access by a string indexer: helper.GeneralSummaries["ItemTotal"].Value.

GridViewHelper 是作为独立类而不是继承类实现的。这使得可以将 GridViewHelper 与任何 GridView 一起使用，并且不会强制开发人员继承特定的 GridView，这会影响类设计。解决方案中还有另外四个类：GridViewSummary、GridViewGroup、GridViewSummaryList 和 GridViewGroupList。创建“列表”类以允许字符串索引器访问：helper.GeneralSummaries["ItemTotal"].Value。

When the GridViewHelper is created, a reference to the target GridView is saved, and the RowDataBound event is bound to the method that does the hard work:

创建 GridViewHelper 时，会保存对目标 GridView 的引用，并将 RowDataBound 事件绑定到执行繁重工作的方法：

public GridViewHelper(GridView grd, bool useFooterForGeneralSummaries, SortDirection groupSortDirection)
{
    this.mGrid = grd;
    this.useFooter = useFooterForGeneralSummaries;
    this.groupSortDir = groupSortDirection;
    this.mGeneralSummaries = new GridViewSummaryList();
    this.mGroups = new GridViewGroupList();
    this.mGrid.RowDataBound += new GridViewRowEventHandler(RowDataBoundHandler);
}

Some methods used internally by the GridViewHelper were defined public because they provide some useful features that may be needed for some customizations. There are a few other options that wasn't shown in the samples but that can be easily verified with Visual Studio intellisense.

GridViewHelper 内部使用的一些方法被定义为公共方法，因为它们提供了一些自定义可能需要的有用功能。还有一些其他选项未在示例中显示，但可以使用 Visual Studio 智能感知轻松验证。

Known issues

已知的问题

Performance might be compromised with the excessive boxing and unboxing of value types. To solve this we could implement the built-in summary operations with generics, but this is not easy as we would like, as can be seen at Using Generics for Calculations. Another possibility: Operator Overloading with Generics. In real life this will not affect the application except if there are a million rows, or if there are thousand of users grouping and summarizing data concurrently.

性能可能会因值类型的过多装箱和拆箱而受到影响。为了解决这个问题，我们可以使用泛型实现内置的汇总操作，但这并不像我们希望的那样容易，正如在使用泛型进行计算中可以看到的那样。另一种可能性：运算符重载泛型。在现实生活中，这不会影响应用程序，除非有一百万行，或者有数千个用户同时分组和汇总数据。

The online sample keeps the GridView EnableViewState false. This is required because when EnableViewState is true, if the page is in a PostBack the GridView will be rebuilt from the ViewState, and won't trigger the RowDataBound event. We can securely disable the ViewState in ASP.Net 2.0 because the ControlState will still be saved.

在线示例将 GridView EnableViewState 保持为 false。这是必需的，因为当 EnableViewState 为 true 时，如果页面处于 PostBack 状态，则 GridView 将从 ViewState 重建，并且不会触发 RowDataBound 事件。我们可以安全地禁用 ASP.Net 2.0 中的 ViewState，因为 ControlState 仍将被保存。

回答by Guillermo Ruffino

This does not answer the question exactly, here I'm using a denormalized result set instead of the one in the question, however adapting data should not be the main issue here.

这并不能准确回答问题，这里我使用的是非规范化结果集而不是问题中的结果集，但是调整数据不应该是这里的主要问题。

Here is how I get mine grouping, first I attach to row data bound to find groups, and also I hiHyman the render delegate:

这是我如何获得我的分组，首先我附加到绑定到查找组的行数据，然后我劫持了渲染委托：

readonly Dictionary<Control, string> _groupNames = new Dictionary<Control, string>();
private Group _lastGroup;
protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        var obj = (Obj)e.Row.DataItem;
        if (obj.Group != _lastGroup)
        {
            e.Row.SetRenderMethodDelegate(RenderGridViewRowWithHeader);
            _lastGroup = obj.Group;

            // Cache group description for this row, note that you might
            // like to implement this differently if you have your data normalized.
            _groupNames[e.Row] = obj.Group.Description;                 }
        }
    }
}

Then in the render method:

然后在渲染方法中：

private void RenderGridViewRowWithHeader(HtmlTextWriter output, Control container)
{
    // Render group header
    var row = new TableRow { CssClass = "groupingCssClass" };
    row.Cells.Add(new TableCell());
    row.Cells.Add(new TableCell
    {
        ColumnSpan = ((GridViewRow)container).Cells.Count - 1,
        Text = _groupNames[container]
    });
    row.RenderControl(output);

    // Render row
    container.SetRenderMethodDelegate(null); // avoid recursive call
    container.RenderControl(output);
}

I know this is a hack, but ASP.NET is all about this when you get into details. This solution is quite friendly with the control state, adding rows to the grid at runtime might cause estrange behavior.

我知道这是一个 hack，但是当您进入详细信息时，ASP.NET 就是关于此的全部内容。此解决方案对控制状态非常友好，在运行时向网格添加行可能会导致疏远行为。