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:
