Do you spend much time reading posts on the ASPNG lists? If not, I highly recommend
it. You can sign-up at http://www.asp.net
or http://www.asplists.com.
Lately one of the questions that has been popping up a lot is, "How do
I show column totals in a DataGrid?" Personally I have provided sample
code for this question more than a few times, so I thought it would only be
appropriate that we here at DotNetJunkies HQ get a tutorial up on the topic.
In this tutorial you will learn how to programmatically sum the values of a
column in a DataGrid, and display that total in the footer of the DataGrid.
The downloadable sample code for this tutorial is in both C# and Visual Basic.NET.
The end result of this tutorial will look like this:
[Run Sample]
Lets Get It On
The DataGrid used in the screenshot above is a pretty typical DataGrid. There
are a few properties that control the look and feel of the DataGrid, and it
uses two BoundColumns to render the data, but that is it. The real important
piece that makes this work is the use of the DataGrid.OnItemDataBound
event. This event fires each time a record is bound to the DataGrid. You can
create an event handler for this event, and manipulate the data in the record.
In this case, you will keep a running total of the sum of the Price column.
The Footer row is the last row that is data bound. When this row is bound,
in the event handler you will render the running total to the Price column.
Sounds simple, huh?
Break It Down
First lets get that pesky Web form out of the way. For this tutorial you will
use a Web Form (calcTotals.aspx) and a code behind class file (calcTotals.aspx.cs).
For the purposes of this tutorial, the code behind class will be compiled using
a Just-In-Time compiler. This is identified by the addition of the SRC
attribute in the @ Page directive. Here is the
code for calcTotals.aspx:
<%@ Page Inherits="myApp.calcTotals"
Src="20010731T0101.aspx.cs" %>
<html>
<body bgcolor="white">
<asp:DataGrid id="MyGrid"
runat="server"
AutoGenerateColumns="False"
CellPadding="4"
CellSpacing="0"
BorderStyle="Solid"
BorderWidth="1"
Gridlines="None"
BorderColor="Black"
ItemStyle-Font-Name="Verdana"
ItemStyle-Font-Size="9pt"
HeaderStyle-Font-Name="Verdana"
HeaderStyle-Font-Size="10pt"
HeaderStyle-Font-Bold="True"
HeaderStyle-ForeColor="White"
HeaderStyle-BackColor="Blue"
FooterStyle-Font-Name="Verdana"
FooterStyle-Font-Size="10pt"
FooterStyle-Font-Bold="True"
FooterStyle-ForeColor="White"
FooterStyle-BackColor="Blue"
OnItemDataBound="MyDataGrid_ItemDataBound"
ShowFooter="True">
<Columns>
<asp:BoundColumn HeaderText="Title"
DataField="title" />
<asp:BoundColumn HeaderText="Price"
DataField="price"
ItemStyle-HorizontalAlign="Right"
HeaderStyle-HorizontalAlign="Center"
/>
</Columns>
</asp:DataGrid>
</body>
</html> |
In the Web Form you use the @ Page directive
to declare the code behind class that this page inherits from. The SRC
attribute indicates that the code behind class will be compiled on the fly using
the JIT compiler. The bulk of the rest of the code in the Web Form is style
declarations to make the DataGrid look good, after all, image is everything.
One of the last properties specified is the OnItemDataBound
property. This specifies what event handler should fire when the OnItemDataBound
event occurs.
The DataGrid (MyGrid) in the Web Form has two
BoundColumns, one for Title and one for Price. This is where you
will display the title and price
columns from the Titles table in the Pubs
database (SQL Server).
Ignore the Code Behind the Curtain
The code behind class is where all the work will be done. In the code behind
class you will handle two events, the Page_Load
event, and the MyGrid_OnItemDataBound event,
and have one private method, CalcTotal, which
will simply do the math to maintain the running total.
Start by building the basic structure for the code behind class.
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data;
using System.Data.SqlClient;
namespace myApp
{
public class
calcTotals : Page
{
protected DataGrid
MyGrid;
private double runningTotal
= 0;
}
} |
In the basic structure of the code behind class you import the necessary namespaces
with the using statement. Inside the class declaration
you declare two variables, one for the DataGrid (MyGrid)
which maps the variable in the code behind class to the control in the Web Form;
and a double that will be used to maintain the running total of the Price column
of the DataGrid.
The Page_Load Event Handler
In the Page_Load event handler all you do is
make a connection to your SQL Server database and execute a simply SqlCommand.
You are retrieving the title and price data for all titles that have a price
greater than zero. You use the SqlCommand.ExecuteReader
method to return a SqlDataReader and bind that
directly to the DataGrid (MyGrid).
|
protected void Page_Load(object
sender, EventArgs e)
{
SqlConnection myConnection =
new SqlConnection("server=Localhost;database=pubs;uid=sa;pwd=;");
SqlCommand myCommand = new
SqlCommand("SELECT title, price FROM Titles WHERE price >
0", myConnection);
try
{
myConnection.Open();
MyGrid.DataSource = myCommand.ExecuteReader();
MyGrid.DataBind();
myConnection.Close();
}
catch(Exception
ex)
{
//An error occured
HttpContext.Current.Response.Write(ex.ToString());
}
}
|
The CalcTotals Method
The CalcTotals method adds the passed in value
to the runningTotal variable. The value will
be passed in as a string value. You will need to parse it as a double, since
the runningTotal variable is a double type.
|
private void CalcTotal(string
_price)
{
try
{
runningTotal += Double.Parse(_price);
}
catch
{
//A value was null
}
}
|
The MyGrid_ItemDataBound Event
The MyGrid_ItemDataBound event is called as
each row in the data source is bound to the DataGrid. In this event handler
you can work with the data in each row, in the form of a DataGridItem.
For you purpose here, you will need to call CalcTotals
and pass in the text from the Price column, and then format the Price column
as currency for each row (Item or AlternatingItem),
and display the value of runningTotal in the
Footer row.
|
public void MyDataGrid_ItemDataBound(object
sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType ==
ListItemType.Item || e.Item.ItemType ==
ListItemType.AlternatingItem)
{
CalcTotal( e.Item.Cells[1].Text );
e.Item.Cells[1].Text = string.Format("{0:c}",
Convert.ToDouble(e.Item.Cells[1].Text));
}
else if(e.Item.ItemType == ListItemType.Footer )
{
e.Item.Cells[0].Text="Total";
e.Item.Cells[1].Text = string.Format("{0:c}",
runningTotal);
}
}
|
In the MyGrid_ItemDataBound event handler,
first you evaluate to see if the current DataGridItem
is an Item or AlternatingItem
row, using the ListItemType enumerator. If it
is, you call CalcTotals and pass in the value
of the Price column as the argument. Next you apply formatting to the Price
column to render it as currency.
If the DataGridItem is a Footer,
you display the runningTotal, formatted as currency.
[Run Sample]
Summary
In this tutorial you learned how to use the DataGrid.OnItemDataBound
event to maintain a running total of a DataGrid column. Using this event you
were able to create a total sum of the column and render it to the footer row
of the DataGrid.