Displaying Summary Information (sum or total) in the GridView's Footer

Challenge

"I have a GridView and I need to show the aggregate sum of a column in the footer row"

Introduction

In this article I will go through steps to enable aggregation sum (total) of a column in the asp.net GridView Footer.

Step1: Prepare Database Connection

In this article I used the AdventureWorks Database and I will query table "PurchaseOrderDetail".

In the web.config set the connection string as following:

<connectionStrings>

    <add name="dbconnection" connectionString="Data Source=(local);Integrated Security=true;Initial Catalog=AdventureWorks"/>

</connectionStrings>

Step2: Prepare the aspx Page


<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>Displaying Summary Information in the GridView's Footer</title>

<style type="text/css">

.Gridview

{

font-family:Verdana;

font-size:10pt;

font-weight:normal;

color:black;

}

</style>

</head>

<body>

<form id="form1"runat="server">

<div>

<asp:GridView ID="gvPurchaseOrderDetail" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8"

HeaderStyle-ForeColor="White" HeaderStyle-Font-Bold="true" DataKeyNames="ProductID"

runat="server" ShowFooter="true" AllowPaging="true" PageSize="5" AutoGenerateColumns="false"

DataSourceID="sqldsPurchaseOrderDetail" OnRowDataBound="gvPurchaseOrderDetail_RowDataBound">

<FooterStyle Font-Bold="true" BackColor="#61A6F8" ForeColor="black" />

<Columns>

  <asp:BoundField DataField="ProductID" HeaderText="Product ID" />

  <asp:BoundField DataField="PurchaseOrderDetailID" HeaderText="Purchase Order ID" />

  <asp:TemplateField HeaderText="Order Quantity">

  <ItemTemplate>

   <asp:Label ID="lblOrderQty" runat="server" Text='<%#Eval("OrderQty") %>' />

  </ItemTemplate>

  <FooterTemplate>

   <asp:Label ID="lbltxtTotal" runat="server" Text="Total Price" />

  </FooterTemplate>

  </asp:TemplateField>

  <asp:TemplateField HeaderText="Price">

  <ItemTemplate>

   <asp:Label ID="lblPrice" runat="server" />

  </ItemTemplate>

  <FooterTemplate>

   <asp:Label ID="lblTotal" runat="server" />

  </FooterTemplate>

  </asp:TemplateField>

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="sqldsPurchaseOrderDetail" runat="server" SelectCommand="select * from [AdventureWorks].[Purchasing].[PurchaseOrderDetail]" ConnectionString="<%$ ConnectionStrings:dbconnection %>"></asp:SqlDataSource>

</div>

</form>

</body>

</html>

Step3: Prepare Code Behind

decimal total = 0;

protected void gvPurchaseOrderDetail_RowDataBound(object sender, GridViewRowEventArgs e)

{

 if (e.Row.RowType == DataControlRowType.DataRow)

 {

Label lblPrice = (Label)e.Row.FindControl("lblPrice");

decimal price = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "UnitPrice")) * Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "OrderQty"));

lblPrice.Text = price.ToString();

total += price;

 }

 if (e.Row.RowType == DataControlRowType.Footer)

 {

Label lblTotal = (Label)e.Row.FindControl("lblTotal");

lblTotal.Text = total.ToString();

 }

}

Result:

Total in GridView


7 Comments

Comments have been disabled for this content.