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:
