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>
Our goal is to display the total of the column UnitPrice in the footer as shown in Figure 1.
Figure 1
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.
Listing 2: Display Sum Total in Footer of GridView
Control
<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.
Listing 3: Using Helper Functions
[Visual Basic .NET]
Dim TotalUnitPrice As Decimal = 0.0
Function GetUnitPrice(ByVal Price As Decimal) As Decimal
TotalUnitPrice += Price
Return Price
End Function
Function GetTotal() As Decimal
Return TotalUnitPrice
End Function
C#
decimal TotalUnitPrice;
decimal GetUnitPrice(decimal Price)
{
TotalUnitPrice += Price;
return Price;
}
decimal GetTotal()
{
return TotalUnitPrice;
}
Downloads
[Download
Sample]
Conclusion
In this article, you have learned how to display the sum
total of a column in the footer of the GridView control with the help of an
example.