Add total field to DataGrid in ASP.NET
Author：Eve Cole
Update Time：2009-06-30 15:40:15
One of the most frequently asked questions in the forum is: "How do I display column totals in a DataGrid?". I've personally provided sample code for this question many times, so I wanted to provide such a guide in the title DotNetJunkies. In this guide you will learn how to programmatically count the values of a column in the DataGrid and display the total value in the footer of the DataGrid. The downloadable examples in this guide include both C# and Visual Basic.NET code.
The end result of this guide will look like this:
As can be seen from the picture above:
The DataGrid in the screen picture used above is a very typical DataGrid. There are many properties that control the appearance of the DataGrid, which uses two BoundColumns to manipulate data, but that's not the most important. The really important thing to do this well is to use the DataGrid.OnItemDataBound event. This event will be triggered each time a record is bound to the DataGrid. You can create an event handler for this event to manipulate the data record. In this case, you will get the total value of the Price column at runtime.
The footer refers to the last row of the data range. When this row is qualified, you can get the runtime statistics of the Price column during event processing.
Implementation :
First let's find a way to manipulate Web Forms output. In this tutorial, you will use a Web form (calcTotals.aspx) and a class code file (calcTotals.aspx.cs). The intent of this guide is that class code will be compiled using a Just-In-Time compiler. Here is the code for calcTotals.aspx:
<%@ Page Inherits="myApp.calcTotals" Src="20010731T0101.aspx.cs" %>
|
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">
|
ItemStyle-HorizontalAlign="Right"
HeaderStyle-HorizontalAlign="Center" />
|
In Web Forms you use @Page to directly declare the class code that the page inherits. The SRC attribute indicates that the class code will be compiled using the JIT compiler. Most of the code style declarations in Web Forms are used to make the DataGrid look better.
One of the last properties specified is the OnItemDataBound property. This event will be triggered when the OnItemDataBound event occurs.
The DataGrid (MyGrid) in the Web Form contains two BoundColumns, one is Title and the other is Price. The title and price columns of the Titles table in the Pubs database (SQL Server) will be displayed here.
Ignore code definitions
Class code will be used everywhere. In the class code, you can operate two events: Page_Load event and MyGrid_OnItemDataBound event. There is also a private method CalcTotal, which can be used to simply perform mathematical operations on runtime statistics.
The beginning of the basic structural block of class code:
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 class code, you must use the relevant statements to import the namespace (namespace). In the class declaration, you declare two variables, one is the variable mapping the DataGrid (MyGrid) control of the Web form in the class code; the other is the double precision value used to operate the runtime statistics in the Price column of the DataGrid.
Page_Load event
In the Page_Load event, all you have to do is connect to SQL Server and execute a simple SqlCommand. You have obtained all title and price data with Price value > 0. You use the SqlCommand.ExecuteReader method to return a SqlDataReader and bind it directly to the DataGrid (MyGrid).
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection myConnection = new SqlConnection("server=Localhost;database=pubs;uid=sa;pwd=;");//Create SQL connection SqlCommand myCommand = new SqlCommand("SELECT title, price FROM Titles WHERE price > 0", myConnection );//Create SQL command
try
{
myConnection.Open();//Open the database connection MyGrid.DataSource = myCommand.ExecuteReader();//Specify the data source of DataGrid MyGrid.DataBind();//Bind data to DataGrid
myConnection.Close();//Close the data connection}
catch(Exception ex)
{
//Catch the error HttpContext.Current.Response.Write(ex.ToString());
}
} |
CalcTotals method
The CalcTotals method is used to handle the runningTotal variable. This value will be passed as a string. You need to parse it as a double, and then the runningTotal variable becomes a double.
private void CalcTotal(string _price)
{
try
{
runningTotal += Double.Parse(_price);
}
catch
{
//catch error}
} |
MyGrid_ItemDataBound event
The MyGrid_ItemDataBound event is called when each row in the data source is bound to the DataGrid. In this event handler, you can process each row of data. For your purposes here, you will need to call the CalcTotals method and pass the text from the Price column and format the Price column of each row with an amount and display the runningTotal value 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 have to use ListItemType to determine whether the current DataGridItem is a data item or an AlternatingItem row. For data items, you call CalcTotals and pass it the value of the Price column as a parameter; then you format and color the Price column in amount format.
If the DataGridItem is a footer, runningTotal can be displayed in amount format.
Summarize
In this guide, you learned how to use the DataGrid.OnItemDataBound event to perform run-time statistics on a DataGrid column. Using this event, you can create a column total and color the footer of a DataGrid row.
