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
|
<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>
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:
