I talk to a lot of people who sneer when
discussing the DataGrid
control; they often discard it in favor of third-party
alternatives. DataGrid, a core piece of the .NET Framework, is
actually a valuable part of my development toolbox.

Presenting data in a table format provides many
opportunities. I’ll explain how you can use
DataGrid to include total values, which is often a requirement when
working with numeric values.

What is the total?

Utilizing the DataGrid control in your
applications allows you to present data in a format that’s familiar
to most users. (The grid format is often compared to a spreadsheet
application such as Microsoft Excel.) With this type of
application, users are accustomed to viewing custom functionality
like column totals, averages, and so forth. While these functions
aren’t a standard part of the DataGrid, you can add them easily
through custom coding.

In our example, I’ll use the sample Northwind
database that comes with every SQL Server installation, and I’ll
pull data from the orders table. A total value will be calculated
for the freight column; this total should be displayed within the
DataGrid for presentation consistency. Here’s the C# code for this
application:

<%@ Import Namespace=”System.Data.SqlClient”
%>
<%@ Import Namespace=”System.Data” %>
<%@ Page language=”c#” %>
<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN”
>
<HTML><HEAD><title>Builder.com DataGrid Totals
Example</title>
</HEAD>
<body MS_POSITIONING=”GridLayout”>
<script runat=”server”>
double totalFreight = 0;
private void Page_Load(object sender, System.EventArgs e) {
if (!Page.IsPostBack) {
BindData();
} }
private void BindData() {
const string sConn;
sConn = “server=(local);Initial
Catalog=Northwind;UID=ctester;PWD=password”;
try {
SqlConnection conn = new SqlConnection(sConn);
conn.Open();
string sSQL = “SELECT TOP 10 OrderID, Freight, ShipName,
ShipCountry FROM
 Orders”;
SqlCommand comm = new SqlCommand(sSQL, conn);
SqlDataReader dr = comm.ExecuteReader();
dgNorthwind.DataSource = dr;
dgNorthwind.DataBind();
} catch (Exception e) {
Console.WriteLine(e.ToString());
} }
private void doTotal(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item | e.Item.ItemType
==
 ListItemType.AlternatingItem) {
double currentFreight =
Convert.ToDouble(DataBinder.Eval(e.Item.DataItem,
 “Freight”));
totalFreight += currentFreight;
} else if (e.Item.ItemType == ListItemType.Footer) {
e.Item.Cells[2].Text = “Total:”;
e.Item.Cells[3].Text = Convert.ToString(totalFreight);
} }
</script>
<form id=”frmDataGridTotals” method=”post”
runat=”server”>
<asp:DataGrid id=”dgNorthwind”
style=”Z-INDEX: 101; LEFT: 24px; POSITION: absolute; TOP:
32px”
runat=”server” Height=”320px” Width=”496px”
AutoGenerateColumns=”False”
OnItemDataBound=”doTotal”
ShowFooter=”True” 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=”Gray”
FooterStyle-Font-Name=”Verdana”
FooterStyle-Font-Size=”10pt”
FooterStyle-Font-Bold=”True”
FooterStyle-ForeColor=”Red”
FooterStyle-BackColor=”Gray”>
<Columns>
<asp:BoundColumn DataField=”OrderID” HeaderText=”#”
ItemStyle-Width=”10%”
 HeaderStyle-HorizontalAlign=”Center” />
<asp:BoundColumn DataField=”ShipName” HeaderText=”Customer”
ItemStyle
-Width=”50%” />
<asp:BoundColumn DataField=”ShipCountry” HeaderText=”Country”
ItemStyle
-Width=”20%” />
<asp:BoundColumn DataField=”Freight” HeaderText=”Freight”
ItemStyle-Width=”20%”
 />
</Columns></asp:DataGrid>
</form></body></HTML>

The first thing you may notice is that the page
doesn’t utilize the code-behind feature; all code is included
within the aspx file. The necessary import directives begin the
page to make the necessary code available for the database
interaction. The page’s Page_Load event calls the BindData method,
which is where the database interaction occurs. It connects to the
database and creates a SqlDataReader object that contains the
records returned by the SQL statement. The SqlDataReader object is
used to fill the DataGrid object on the page via its DataSource
property. The DataGrid’s DataBind method fills it with the data.
The DataGrid’s HTML specifies the columns and their formatting.
This includes the colors, font, alignment, and so on.

The DataBind method also maintains a running
total of the freight column from the data source. The following
line retrieves a specific amount from a row:

double currentFreight =
Convert.ToDouble(DataBinder.Eval(e.Item.DataItem,
 “Freight”));

This line retrieves the value via the Eval
statement and converts it to the necessary format for maintaining
the total. Once it’s retrieved, it’s added to the total variable.
This is performed for each row in the DataGrid. A row is determined
with the following line:

if(e.Item.ItemType==ListItemType.Item |
e.Item.ItemType==ListItemType.AlternatingItem)

This statement returns true for every row
within the DataGrid. The else portion of the statement determines
when to display the total amount. It’s triggered when all rows (the
first part of the if statement is false) are processed in addition
to the start with the header:

else if (e.Item.ItemType ==
ListItemType.Footer)

It returns true when the footer is reached.
Since we don’t have an interest in the header, we must determine if
it’s the footer. At this point, the total value displays in the
appropriate column in the DataGrid. You must remember that the
column numbering begins at zero. For this reason, we populate
columns two and three, and not columns three and four. You can
accomplish this by populating the Text property of the Cells
property with index value of the Item:

e.Item.Cells[2].Text = “Total:”;
e.Item.Cells[3].Text = Convert.ToString(totalFreight);


Notice the total is converted to a string value before
displaying.

An alternate approach

While totaling the data on the fly provides the
desired results, you can use another approach. This entails
utilizing SQL to compute the total of column values by way of the
SQL SUM statement. The drawback to this approach is that it
requires a separate database call, and the results must be stored
in a DataSet or comparable object.

Paging

You may wonder how DataGrid paging affects the
totals. The example code in this article will display a total for
the amounts (freight) displayed on the screen, so the total is
different for every page. (You must alter the code to maintain a
total for every value, but that’s beyond the scope of this
article.)

The code in this article will not work for
paging since you can’t use a SqlDataReader for paging. However, you
can change the code to utilize a DataSet object to offer the paging
option. The following code changes would make this possible:

SqlCommand comm = new SqlCommand(sSQL, conn);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
da.Fill(ds);
dgNorthwind.DataSource = ds;

TechRepublic’s free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!