Display Sum Total in the Footer of the GridView Control

Introduction

In this article, we will see how to display the sum total of a column in the footer of the GridView control using Visual Studio 2005.

For the purpose of this article, we will use the Products Table from the Northwind Database and the GridView control for data binding. The GridView control will display the data and SqlDataSource is supplied in the web.config file as shown in Listing 1.

Listing 1

<connectionStrings> <add name="DummyDB"   connectionString="Server=localhost;Integrated  Security=True;Database=NorthWind;Persist,   Security Info=True" providerName="System.Data.SqlClient"/> </connectionStrings>

 
By default, the GridView's Showfooter property is set to false. We'll change it to true. As we are calculating the field UnitPrice, we'll use TemplateField's ItemTemplate to display UnitPrice and FooterTemplate to display the total.

<asp:GridView ID="GridView1"   ShowFooter="true" DataKeyNames="ProductId"   AutoGenerateColumns="false" runat="server"   DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="Productid" HeaderText="Product Id" /> <asp:BoundField DataField="ProductName" FooterText="Total" HeaderText="Product Name" /> <asp:TemplateField HeaderText="Unit Price" FooterStyle-Font-Bold="True"> <ItemTemplate>   <%# GetUnitPrice(decimal.Parse(Eval("UnitPrice").ToString())).ToString("N2") %> </ItemTemplate> <FooterTemplate>   <%# GetTotal().ToString("N2") %> </FooterTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server"  ConnectionString="<%$ ConnectionStrings:DummyDB %>" SelectCommand="Select * from Products"> </asp:SqlDataSource>

Finally, we'll use Helper functions named GetUnitPrice and GetTotal to display the UnitPrice in the ItemTemplate and Total in the FooterTemplate. For instance, for each row of the GridView, a price value is passed to the GetUnitPrice function returning variable Price.

decimal TotalUnitPrice; 

decimal GetUnitPrice(decimal Price)  

{     

TotalUnitPrice += Price;    

return Price; 

} 

decimal GetTotal() 

{    

return TotalUnitPrice; 

}