AddThis Social Bookmark Button

Print
O'Reilly Book Excerpts: ASP.NET Cookbook

Cooking with ASP.NET

Editor's note: Michael Kittel and Geoffrey LeBlond have selected a few of their favorite recipes from O'Reilly's recently released ASP.NET Cookbook. This week, learn how to add a Totals row to a DataGrid, how to communicate between user controls, and how to display user-friendly error messages. Next week, the authors offer two more recipes--for creating a reusable image handler and saving and reusing HTML output.

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.


Figure 1-24. DataGrid with totals row 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.

Example 1-57. DataGrid with totals row (.aspx)

<%@ Page Language="vb" AutoEventWireup="false" 
         Codebehind="CH01DataGridWithTotalsRowVB.aspx.vb" 
         Inherits="ASPNetCookbook.VBExamples.CH01DataGridWithTotalsRowVB" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
  <head>
    <title>DataGrid With Totals Row</title>
    <link rel="stylesheet" href="css/ASPNetCookbook.css">
  </head>
  <body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0">
    <form id="frmDatagrid" method="post" runat="server">
      <table width="100%" cellpadding="0" cellspacing="0" border="0">
        <tr>
          <td align="center">
            <img src="images/ASPNETCookbookHeading_blue.gif">
          </td>
        </tr>
        <tr>
          <td class="dividerLine">
            <img src="images/spacer.gif" height="6" border="0"></td>
        </tr>
      </table>
      <table width="90%" align="center" border="0">
        <tr>
          <td><img src="images/spacer.gif" height="10" border="0"></td>
        </tr>
        <tr>
          <td align="center" class="PageHeading">
            DataGrid With Totals Row (VB)
          </td>
        </tr>
        <tr>
          <td><img src="images/spacer.gif" height="10" border="0"></td>
        </tr>
        <tr>
          <td align="center">
            <asp:DataGrid 
              id="dgBooks" 
              runat="server" 
              BorderColor="000080" 
              BorderWidth="2px"
              AutoGenerateColumns="False"
              width="100%"
              ShowFooter="True">
              <HeaderStyle 
                HorizontalAlign="Center" 
                ForeColor="#FFFFFF" 
                BackColor="#000080" 
                Font-Bold=true
                CssClass="TableHeader" /> 
              <ItemStyle
                BackColor="#FFFFE0" 
                cssClass="TableCellNormal" />
              <AlternatingItemStyle 
                BackColor="#FFFFFF" 
                cssClass="TableCellAlternating" />
              <FooterStyle cssClass="TableCellNormal" HorizontalAlign="Right" 
                           BackColor="#C0C0C0" />
              <Columns>
                <asp:TemplateColumn HeaderText="Title" FooterText="Total:"> 
                  <ItemTemplate>
                    <%# DataBinder.Eval(Container.DataItem, "Title") %>
                  </ItemTemplate>
                </asp:TemplateColumn>
                <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>
                <asp:TemplateColumn HeaderText="Discounted Price" 
                                    ItemStyle-HorizontalAlign="Right">
                  <ItemTemplate>
                    <asp:Literal id="lblDiscountedPrice" runat="server" 
                          text='<%# DataBinder.Eval(Container.DataItem, _
                                                    "DiscountedPrice") %>' />
                    </asp:Label>
                  </ItemTemplate>
                  <FooterTemplate>
                    <asp:Literal id="lblTotalDiscountedPrice" 
                                 runat="server" />
                  </FooterTemplate>
                </asp:TemplateColumn>
              </Columns>
            </asp:DataGrid>
          </td>
        </tr>
      </table>
    </form>
  </body>
</html>

Example 1-58. DataGrid with totals row code-behind (.vb)

Option Explicit On 
Option Strict On
'-----------------------------------------------------------------------------
'
'   Module Name: CH01DataGridWithTotalsRowVB.aspx.vb
'
'   Description: This class provides the code behind for
'                CH01DataGridWithTotalsRowVB
'
'*****************************************************************************
Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Imports System.Web.UI.WebControls

Namespace ASPNetCookbook.VBExamples
  Public Class CH01DataGridWithTotalsRowVB
    Inherits System.Web.UI.Page

    'controls on form
    Protected WithEvents dgBooks As System.Web.UI.WebControls.DataGrid

    'variables used to accumulate the sum of the prices
    Private mListPriceTotal As Decimal
    Private mDiscountedPriceTotal As Decimal

    '*************************************************************************
    '
    '   ROUTINE: Page_Load
    '
    '   DESCRIPTION: This routine provides the event handler for the page load
    '                event.  It is responsible for initializing the controls 
    '                on the page.
    '-------------------------------------------------------------------------
    Private Sub Page_Load(ByVal sender As System.Object, _
                          ByVal e As System.EventArgs) _
            Handles MyBase.Load
      Dim dbConn As OleDbConnection
      Dim da As OleDbDataAdapter
      Dim ds As DataSet
      Dim strConnection As String
      Dim strSQL As String

      If (Not Page.IsPostBack) Then
        Try
          'get the connection string from web.config and open a connection 
          'to the database
          strConnection = _
              ConfigurationSettings.AppSettings("dbConnectionString")
          dbConn = New OleDb.OleDbConnection(strConnection)
          dbConn.Open( )

          'build the query string and get the data from the database
          strSQL = "SELECT Title, ListPrice, DiscountedPrice " & _
                   "FROM Book " & _
                   "ORDER BY Title"
          da = New OleDbDataAdapter(strSQL, dbConn)
          ds = New DataSet
          da.Fill(ds)

          'set total values to 0 before data binding
          mListPriceTotal = 0
          mDiscountedPriceTotal = 0

          'set the source of the data for the datagrid control and bind it
          dgBooks.DataSource = ds
          dgBooks.DataBind( )

        Finally
          'cleanup
          If (Not IsNothing(dbConn)) Then
            dbConn.Close( )
          End If
        End Try
      End If
    End Sub  'Page_Load

    '*************************************************************************
    '
    '   ROUTINE: dgBooks_ItemDataBound
    '
    '   DESCRIPTION: This routine is the event handler that is called for each 
    '                item in the datagrid after a data bind occurs.  It is 
    '                responsible for accumlating the total prices and setting
    '                the values in the footer when all rows have been data 
    '                bound.
    '-------------------------------------------------------------------------
    Private Sub dgBooks_ItemDataBound(ByVal sender As Object, _
                ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) _
            Handles dgBooks.ItemDataBound
      Dim rowData As DataRowView
      Dim price As Decimal
      Dim listPriceLabel As System.Web.UI.WebControls.Literal
      Dim discountedPriceLabel As System.Web.UI.WebControls.Literal
      Dim totalLabel As System.Web.UI.WebControls.Literal
      'check the type of item that was databound and only take action if it 
      'was a row in the datagrid
      Select Case (e.Item.ItemType)
        Case ListItemType.AlternatingItem, ListItemType.EditItem, _
             ListItemType.Item, ListItemType.SelectedItem
          'get the data for the item being bound
          rowData = CType(e.Item.DataItem, _
                          DataRowView)
          'get the value for the list price and add it to the sum
          price = CDec(rowData.Item("ListPrice"))
          mListPriceTotal += price
          'get the control used to display the list price
          'NOTE: This can be done by using the FindControl method of the 
          '      passed item because ItemTemplates were used and the anchor
          '      controls in the templates where given IDs.  If a standard
          '      BoundColumn was used, the data would have to be accessed
          '      using the cellscollection (e.g. e.Item.Cells(1).controls(1)
          '      would access the label control in this example.
          listPriceLabel = CType(e.Item.FindControl("lblListPrice"), _
                                 System.Web.UI.WebControls.Literal)
          'now format the list price in currency format
          listPriceLabel.Text = price.ToString("C2")
          'get the value for the discounted price and add it to the sum
          price = CDec(rowData.Item("DiscountedPrice"))
          mDiscountedPriceTotal += price
          'get the control used to display the discounted price
       discountedPriceLabel = CType(e.Item.FindControl("lblDiscountedPrice"), _
                                    System.Web.UI.WebControls.Literal)
          'now format the discounted price in currency format
          discountedPriceLabel.Text = price.ToString("C2")
        Case ListItemType.Footer
          'get the control used to display the total of the list prices
          'and set its value to the total of the list prices
          totalLabel = CType(e.Item.FindControl("lblListPriceTotal"), _
                             System.Web.UI.WebControls.Literal)
          totalLabel.Text = mListPriceTotal.ToString("C2")
          'get the control used to display the total of the discounted prices
          'and set its value to the total of the discounted prices
          totalLabel = CType(e.Item.FindControl("lblTotalDiscountedPrice"), _
                             System.Web.UI.WebControls.Literal)
          totalLabel.Text = mDiscountedPriceTotal.ToString("C2")
        Case Else
          'ListItemType.Header, ListItemType.Pager, or ListItemType.Separator
          'no action required
      End Select
    End Sub  'dgBooks_ItemDataBound
  End Class  'CH01DataGridWithTotalsRowVB
End Namespace

Example 1-59. DataGrid with totals row code-behind (.cs)

//----------------------------------------------------------------------------
//
//   Module Name: CH01DataGridWithTotalsRowCS.aspx.cs
//
//   Description: This class provides the code behind for
//                CH01DataGridWithTotalsRowCS.aspx
//
//****************************************************************************
using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Web.UI.WebControls;

namespace ASPNetCookbook.CSExamples
{
  public class CH01DataGridWithTotalsRowCS : System.Web.UI.Page
  {
    // control on form
    protected System.Web.UI.WebControls.DataGrid dgBooks;

    // variables used to accumulate the sum of the prices
    private Decimal mListPriceTotal;
    private Decimal mDiscountedPriceTotal;

    //************************************************************************
    //
    //   ROUTINE: Page_Load
    //
    //   DESCRIPTION: This routine provides the event handler for the page
    //                load event.  It is responsible for initializing the
    //                controls on the page.
    //------------------------------------------------------------------------
    private void Page_Load(object sender, System.EventArgs e)
    {
      OleDbConnection dbConn = null;
      OleDbDataAdapter da = null;
      DataSet ds = null;
      String strConnection = null;
      String strSQL =null;

      // wire the item data bound event
      this.dgBooks.ItemDataBound +=
        new DataGridItemEventHandler(this.dgBooks_ItemDataBound);

      if (!Page.IsPostBack)
      {
        try
        {
          // get the connection string from web.config and open a connection 
          // to the database
          strConnection = 
            ConfigurationSettings.AppSettings["dbConnectionString"];
          dbConn = new OleDbConnection(strConnection);
          dbConn.Open( );

          // build the query string and get the data from the database
          strSQL = "SELECT Title, ListPrice, DiscountedPrice " +
                   "FROM Book " +
                   "ORDER BY Title";
          da = new OleDbDataAdapter(strSQL, dbConn);
          ds = new DataSet( );
          da.Fill(ds);

          // set total values to 0 before data binding
          mListPriceTotal = 0;
          mDiscountedPriceTotal = 0;

          // set the source of the data for the datagrid control and bind it
          dgBooks.DataSource = ds;
          dgBooks.DataBind( );
        }  // try

        finally
        {
          //clean up
          if (dbConn != null)
          {
            dbConn.Close( );
          }
        }  // finally
      }
    }  // Page_Load

    //************************************************************************
    //
    //   ROUTINE: dgBooks_ItemDataBound
    //
    //   DESCRIPTION: This routine is the event handler that is called for each
    //                item in the datagrid after a data bind occurs.  It is
    //                responsible for accumlating the total prices and setting
    //                the values in the footer when all rows have been data
    //                bound.
    //
    //------------------------------------------------------------------------
    private void dgBooks_ItemDataBound(Object sender,
                        System.Web.UI.WebControls.DataGridItemEventArgs e)
    {
      DataRowView rowData;
      Decimal price;
      System.Web.UI.WebControls.Literal listPriceLabel = null;
      System.Web.UI.WebControls.Literal discountedPriceLabel = null;
      System.Web.UI.WebControls.Literal totalLabel = null;
      // check the type of item that was databound and only take action if it
      // was a row in the datagrid
      switch (e.Item.ItemType)
      {
        case ListItemType.AlternatingItem:
        case ListItemType.EditItem:
        case ListItemType.Item:
        case ListItemType.SelectedItem:
          // get the data for the item being bound
          rowData = (DataRowView)(e.Item.DataItem);
          // get the value for the list price and add it to the sum
          price = (Decimal)(rowData["ListPrice"]);
          mListPriceTotal += price;
          // get the control used to display the list price
          // NOTE: This can be done by using the FindControl method of the 
          //      passed item because ItemTemplates were used and the anchor
          //      controls in the templates where given IDs.  If a standard
          //      BoundColumn was used, the data would have to be accessed
          //      using the cellscollection (e.g. e.Item.Cells(1).controls(1)
          //      would access the label control in this example.
          listPriceLabel = (System.Web.UI.WebControls.Literal)
                           (e.Item.FindControl("lblListPrice"));
          // now format the list price in currency format
          listPriceLabel.Text = price.ToString("C2");
          // get the value for the discounted price and add it to the sum
          price = (Decimal)(rowData["DiscountedPrice"]);
          mDiscountedPriceTotal += price;
          // get the control used to display the discounted price
          discountedPriceLabel = (System.Web.UI.WebControls.Literal)
                                 (e.Item.FindControl("lblDiscountedPrice"));
          // now format the discounted price in currency format
          discountedPriceLabel.Text = price.ToString("C2");
          break;
        case ListItemType.Footer:
          // get the control used to display the total of the list prices
          // and set its value to the total of the list prices
          totalLabel = (System.Web.UI.WebControls.Literal)
                       (e.Item.FindControl("lblListPriceTotal"));
          totalLabel.Text = mListPriceTotal.ToString("C2");
          // get the control used to display the total of the discounted 
          // prices and set its value to the total of the discounted prices
          totalLabel = (System.Web.UI.WebControls.Literal)
                       (e.Item.FindControl("lblTotalDiscountedPrice"));
          totalLabel.Text = mDiscountedPriceTotal.ToString("C2");
          break;
        default:
          // ListItemType.Header, ListItemType.Pager, or ListItemType.Separator
          // no action required
          break;
      }
    }  // dgBooks_ItemDataBound
  }  // CH01DataGridWithTotalsRowCS
}

Pages: 1, 2, 3

Next Pagearrow