During my training career, I have been asked one common question several times that how we can calculate summary totals inside GridView control and how we can show it at the footer of GridView control. So I have decided to write a tutorial on this topic. In this tutorial I will show you how you can calculate and display totals not only in GridView footer but also on any control outside GridView control.
For the purpose of this tutorial I have setup a simple ASP.NET page with data bound GridView control and a Label control on it. Following is the HTML source code of the GridView and Label control:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#336699" BorderStyle="Solid" BorderWidth="1px"
CellPadding="2" Font-Names="Verdana" ShowFooter="true"
Font-Size="10pt" Width="50%" DataKeyNames="ProductID"
GridLines="Horizontal" onrowdatabound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" />
<asp:TemplateField HeaderText="UnitPrice">
<ItemTemplate>
<asp:Label ID="lblPrice" runat="server" Text='<%# Eval("UnitPrice")%>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalPrice" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="UnitsInStock">
<ItemTemplate>
<asp:Label ID="lblUnitsInStock" runat="server" Text='<%# Eval("UnitsInStock") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalUnitsInStock" runat="server" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#336699" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
<HeaderStyle BackColor="#336699" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
</asp:GridView>
<br />
<b>Average Price: </b>
<asp:Label ID="lblAveragePrice" runat="server" Text="Label"></asp:Label>
In the above code, I am using TemplateField column inside GridView control which has two templates ItemTemplate and FooterTemplate containing Label controls to show data. The Labels inside ItemTemplate are bound with UnitPrice and UnitsInStock fields using ASP.NET binding expression <%# %> syntax. The Labels inside FooterTemplate are left blank for showing totals later from the code.
I am also attaching an event handler for the RowDataBound event with the GridView. This event is where I will calculate the total for GridView data later. The RowDataBound event is raised whenever a row in the GridView is bound to data. It is raised for the header, the footer, and data rows. This event provides an opportunity to access each row before the page is finally sent to the client for display.
The Page_Load event of the page binds data with the GridView control as follows:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}
private void BindData()
{
string constr = "Server=TestServer;Database=TestDatabase;uid=test;pwd=test;";
string query = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock, OnSale FROM Products";
SqlDataAdapter da = new SqlDataAdapter(query, constr);
DataTable table = new DataTable();
da.Fill(table);
GridView1.DataSource = table;
GridView1.DataBind();
}
To calculate totals first you need to declare some variables outside the RowDataBound event. Two variables will keep track of the total of all the values bound inside GridView control and the third variable totalItems will be used to calculate the average price.
decimal totalPrice = 0M;
decimal totalStock = 0M;
int totalItems = 0;
As I mentioned above, RowDataBound event raised for all header, data or footer rows. I put one if condition to make sure currently it is binding DataRow. Then I am getting the reference of lblPrice and lblUnitsInStock labels using FindControl method. These labels have price and stock values of the current row in their text property. I am converting the text into decimal and then adding the value in totalPrice and totalStock variables. Once all the Data rows are finished I have similar check for Footer Row in which I am getting the reference of lblTotalPrice and lblTotalUnitsInStock Labels available in the FooterTemplate inside GridView and showing the totals on those labels.
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lblPrice = (Label)e.Row.FindControl("lblPrice");
Label lblUnitsInStock = (Label)e.Row.FindControl("lblUnitsInStock");
decimal price = Decimal.Parse(lblPrice.Text);
decimal stock = Decimal.Parse(lblUnitsInStock.Text);
totalPrice += price;
totalStock += stock;
totalItems += 1;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lblTotalPrice = (Label)e.Row.FindControl("lblTotalPrice");
Label lblTotalUnitsInStock = (Label)e.Row.FindControl("lblTotalUnitsInStock");
lblTotalPrice.Text = totalPrice.ToString();
lblTotalUnitsInStock.Text = totalStock.ToString();
lblAveragePrice.Text = (totalPrice / totalItems).ToString("F");
}
}
how to calculate average of grid in columnwise and display it in footer
how to get average of each column in gridview footer
we are using same coding in our project.. we are not getting how we can use this total in another c# page??
Is it possible to format the price label and the footer in currency format e.g with DataFormatString=”{0:n2}”?
Thanks very helpful….!
nice one..
Brother congratulation,i have solved my problem by following your this tutorials after trying 1 day. so thank you.
vry nice
great man
thanks a ton for the tutorial, it was really of great help to me for completing my project 🙂
Thank You very much your tutorial is very useful for me……
Its good article
Very useful. thanks
Thanks a lot…It helped me..very useful article!!
Gud…
This content was very useful. I really appreciate the way of explanation. Thank u very much.
Sir, I have implemented this. its seems that it should work but it is not displaying total. please tell if any solution possible..
hai good morning
Nice & simple…
Very Simple and Very Informative
Thanks
It is not working….
Input string was not in correct format error showing………
please help me………….
Hi,
A very great article!
When I tried it, it worked perfectly; But when I changed the gridview pagesize to 3, it gave only the total per page of the gridview.
Pls how do i modify the code to give me the grandtotal at the last page of the gridview.
Thank u.
Emmao
very good example .
every one can easily understand.
A simple example illustrating usability of GridView. I need to know how to fix value of a cell when it is clicked/accessed.
very good tutorial
Thanks. great
sir,
Actually im having one column name for Cost in grid view.if i select the checkbox one cost will be selected so i want to total cost in total textbox. how can i get it
Good one buddy!! life saver!!!
Good one buddy!! life saver!!!
Grt thanks for this beautiful explanation.i m looking for this for the last one month
Your Article on Displaying Total in Footer of GridView really worked like a charm for me.
Thanks keep it up
Thanks.. It Really Worked
Nice Tutorial ..
I need basics of Ajax . plz Eazy tutorial on Ajax plzzzzz …
i am your student thank you for sending this articales
if you have a upgraded c# foundation training kit please send my
your evs_dotnet65 student