Recipe 1.19: Adding a Totals Row to a DataGrid

Problem

Related Reading ASP.NET Cookbook The Ultimate ASP.NET Code Sourcebook By Michael A. Kittel, Geoffrey T. LeBlond

You have a DataGrid containing numeric information and you need to display a total of the data in the last row of the grid.

Solution

Enable the output of the footer in the DataGrid, accumulate the total for the data in the ItemDataBound event handler, and then output the total in the DataGrid footer.

In the .aspx file, set the ShowFooter attribute of the asp:DataGrid element to True.

In the code-behind class for the page, use the .NET language of your choice to:

1. Initialize the totals to 0, and then bind the data to the DataGrid in the normal fashion.

2. In the ItemDataBound event handler, add the values for each data row to the accumulated totals.

3. In the ItemDataBound event handler, set the total values in the footer when the footer is data bound.

Figure 1-24 shows some typical output. Examples Example 1-57 through Example 1-59 show the .aspx file and code-behind files for an application that produces this output.

Discussion

The best way to describe the addition of a totals row to a DataGrid is by example. In this recipe, you'll want to create the DataGrid a little differently than normal. In the asp:DataGrid element, set the ShowFooter attribute to True to cause a footer to be output when the control is rendered. You then place the totals data in the footer.

<asp:DataGrid 
  id="dgBooks" 
  runat="server" 
  BorderColor="000080" 
  BorderWidth="2px"
  AutoGenerateColumns="False"
  width="100%"
  
ShowFooter="True">

Next, add a FooterStyle element to format all of the columns in the footer with a stylesheet class, background color, and horizontal alignment:

<FooterStyle cssClass="TableCellNormal" HorizontalAlign="Right" 
             BackColor="#C0C0C0" />

All columns are defined in the Columns element as asp:TemplateColumn columns. This provides a lot of flexibility in the display of the columns. The first column contains only an ItemTemplate that is bound to the Title field in the DataSet. The FooterText property of this column is set to "Total:" to simply display the label for the other values in the footer.

<asp:TemplateColumn HeaderText="Title" 
FooterText="Total:"> 
  <ItemTemplate>
    <%# DataBinder.Eval(Container.DataItem, _
                        "Title") %>
  </ItemTemplate>
</asp:TemplateColumn>

The second and third columns contain an ItemTemplate element to define the format of the data placed in the rows of the grid and a FooterTemplate element to define the format of the data placed in the footer of the respective columns:

<asp:TemplateColumn HeaderText="List Price" 
                    ItemStyle-HorizontalAlign="Right">
  <ItemTemplate>
    <asp:Literal id="lblListPrice" runat="server" 
                 text='<%# DataBinder.Eval(Container.DataItem, _
                                           "ListPrice") %>' />
  </ItemTemplate>
  <FooterTemplate>
    <asp:Literal id="lblListPriceTotal" runat="server" />
  </FooterTemplate>
</asp:TemplateColumn>

In the code-behind, two private variables (mListPriceTotal and mDiscountedPriceTotal) are declared at the class level to store the accumulated sum for each of the price columns. The bindData method is identical to previous recipes, except for the addition of the code to set mListPriceTotal and mDiscountedPriceTotal to zero before the data binding is performed.

The ItemDataBound event is used to accumulate the sum of the prices as the rows in the DataGrid are bound. You can do this because the data binding always starts at the top of the grid and ends at the bottom. Because the ItemDataBound event method is called for every row in the grid, you must first determine what row this event applies to by checking the ItemType of the passed event arguments. Several groups of item types are needed here, so a Select Case statement (switch in C#) is used.

When the item type is a data row, you need to get the values in the list price and discounted price columns, and then add them to the appropriate total variables. Getting the price values requires getting the price values from the data passed to the method (e.Item.ItemData), adding the price data to the totals, getting a reference to the controls used to display the data, and then setting the price value in the controls for the row. Getting a reference to the control is the trickiest part. The easiest and most flexible approach is to use Literal controls in the ItemTemplates of the DataGrid defined in the .aspx file. By setting the IDs of the literal controls, the FindControl method of the row being data bound can be used to get a reference to the desired control.

TIP: If the IDs of the controls in the ItemTemplates are not defined, the only way to get a reference to a control is to index into the cells and controls collections of the row. In this example, the list price control is in the second column of the grid. Cells in a DataGrid are created with a literal control before and after the controls you define in a column; therefore, the list price control is the second control in the controls collection of the cell. Getting a reference to the list price control using this method would be done with listPriceControl = e.Item.Cells(1).controls(1). This approach is very dependent on column layout—rearranging columns would break code that uses this approach. The FindControl method is much easier to maintain and less likely to be broken by changing the user interface.

TIP: Literal controls are used in this example because they are rendered without the addition of other controls and because accessing the price value is as simple as getting the value of the text property of the control. An asp:Label control would seem like a good option here; however, it is created as three literal controls in the DataGrid, making it necessary to index into the controls collection of the control returned by the FindControl method to get the needed price value.

When the item is the footer, all data rows have been processed and you have the totals for the price columns in the mListPriceTotal and mDiscountedPriceTotal variables. Now you need to output these totals in the controls placed in the footer. This is done by again using the FindControl method of the passed item to get a reference to the controls in the footer. After a reference to the control is obtained, the text property is set to the total for the column. In our example, the totals are also being formatted to be displayed in currency format with two decimal places.