Developer

Include totals with numeric values via DataGrid

DataGrid is a valuable part of any developer's toolbox. Learn how you can use DataGrid to include total values, which is often a requirement when working with numeric values.

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!

About Tony Patton

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

Editor's Picks

Free Newsletters, In your Inbox