You have a DataGrid containing numeric
information and you need to display a
total of the data in the last row of the grid.
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:
Initialize the totals to 0, and then bind the data to the
DataGridin the normal fashion.In the
ItemDataBoundevent handler, add the values for each data row to the accumulated totals.In the
ItemDataBoundevent 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.
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 NamespaceExample 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
}Get ASP.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
