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; 

}

Master\Detail with CollapsiblePanelExtender and edit detail with HoverMenuExtender using GridView

Performance Notes:
You'll notice performance issue with sample. The returns actually for many reasons such as the dependency javascript files that will be downloaded for both CollapsiblePanelExtender and HoverMenuExtender. Beside the usage of Nested UpdatePanels.

Nested UpdatePanels with Nested GridViews:
I had to work with Nested UpdatePanels this time. It is just on Parent root UpdatePanel, and many child UpdatePanel underneath it. Below is a demonstration code for the structure of the Nested UpdatePanels with Nested GridViews

<asp:SqlDataSource ID="sqlDsCustomers" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"

        SelectCommand="SELECT [Customers].[CustomerID], [Customers].[CompanyName], [Customers].[Address] FROM [Customers] INNER JOIN [Orders] ON [Customers].[CustomerID]=[Orders].[CustomerID]&#13;&#10;Group By [Customers].[CustomerID], [Customers].[CompanyName], [Customers].[Address]"

        ProviderName="<%$ ConnectionStrings:Northwind.ProviderName %>"></asp:SqlDataSource>

<asp:UpdatePanel ID="pnlUpdate"                           UpdateMode="Conditional" ChildrenAsTriggers="false" runat="server">

<Triggers>

<asp:AsyncPostBackTrigger ControlID="gvCustomers" ventName="PageIndexChanged" />

</Triggers>

<ContentTemplate>

<asp:GridView Width="100%" AllowPaging="True" ID="gvCustomers" AutoGenerateColumns="False" DataSourceID="sqlDsCustomers" runat="server" ShowHeader="False" OnRowCreated="gvCustomers_RowCreated">

<Columns>

<asp:TemplateField>

<ItemTemplate>

<asp:Panel CssClass="group" ID="pnlCustomer" runat="server">

<asp:Image ID="imgCollapsible" CssClass="first" ImageUrl="~/Assets/img/plus.png"

Style="margin-right: 5px;" runat="server" />

<span class="header"><%#Eval("CompanyName")%><%#Eval ("Address") %></span>

</asp:Panel>

<asp:SqlDataSource ID="sqlDsOrders" runat="server" ConnectionString="<%$ ConnectionStrings:Northwind %>"                                                        SelectCommand="SELECT [OrderID], [OrderDate], [RequiredDate], [Freight], [ShippedDate], [CustomerID] FROM [Orders] WHERE ([CustomerID] = @CustomerID)"

UpdateCommand="UPDATE [Orders] SET [Freight] = @Freight WHERE [OrderID] = @OrderID"

DeleteCommand="DELETE FROM [Orders] WHERE [OrderID] = @OrderID">

<SelectParameters>

<asp:Parameter Name="CustomerID" Type="String" DefaultValue="" />

</SelectParameters>

<UpdateParameters>

<asp:ControlParameter Name="OrderID" Type="Int32" ControlID="gvOrders" PropertyName="SelectedValue" />

</UpdateParameters>

</asp:SqlDataSource>

<asp:Panel Style="margin-left: 20px; margin-right: 20px;" ID="pnlOrders" runat="server">

<asp:UpdatePanel ID="pnlUpdateOrders" UpdateMode="Conditional" runat="server">

<ContentTemplate>

<asp:GridView ID="gvOrders" AutoGenerateColumns="False" DataKeyNames="OrderID" CssClass="grid" DataSourceID="sqlDsOrders"

runat="server" OnRowCreated="gvOrders_RowCreated"

OnRowEditing="gvOrders_RowEditing" OnRowCommand="gvOrders_RowCommand">

<RowStyle CssClass="row" />                                                                    <AlternatingRowStyle CssClass="altrow" />

<Columns>

<asp:TemplateField ItemStyle-CssClass="rownum">

<ItemTemplate>

<%# Container.DataItemIndex + 1 %>

</ItemTemplate>

<ItemStyle CssClass="rownum" />

</asp:TemplateField>

<asp:BoundField HeaderText="Order ID" DataField="OrderID"

ItemStyle-Width="80px" ReadOnly="true" >

<ItemStyle Width="80px" />

</asp:BoundField>

<asp:BoundField HeaderText="Date Ordered" DataField="OrderDate" DataFormatString="{0:MM/dd/yyyy}" ItemStyle-Width="100px" ReadOnly="true" >

<ItemStyle Width="100px" />

</asp:BoundField>

<asp:BoundField HeaderText="Date Required" DataField="RequiredDate" DataFormatString="{0:MM/dd/yyyy}"

ItemStyle-Width="110px" ReadOnly="true" >

<ItemStyle Width="110px" />

</asp:BoundField>

<asp:TemplateField HeaderText="Freight">

<ItemTemplate>

<asp:Label ID="lblFreight" runat="server" Text='<%# Bind("Freight", "{0:N2}") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:TextBox ID="txtFreight" runat="server" Text='<%# Bind("Freight", "{0:N2}") %>' Width="50px"></asp:TextBox></EditItemTemplate>

<ItemStyle HorizontalAlign="Right" Width="50px" />

</asp:TemplateField>

<asp:BoundField DataField="ShippedDate" DataFormatString="{0:MM/dd/yyyy}"

HeaderText="Date Shipped" ItemStyle-Width="100px" ReadOnly="true">

<ItemStyle Width="100px" />

</asp:BoundField>

<asp:TemplateField HeaderStyle-Width="0px" ItemStyle-Width="0px"

ShowHeader="false">

<ItemTemplate>

<asp:Panel ID="popupMenu" runat="server" Style="display: none">

<div style="border: 1px outset white; padding: 2px;">

<div>

<asp:LinkButton ID="lnkButtonEdit" runat="server"

CommandArgument='<%#Eval("CustomerID")%>' CommandName="Edit" Text="Edit" />

</div>

<div>

<asp:LinkButton ID="lnkButtonDelete" runat="server" Enabled="false"

CommandArgument='<%#Eval("CustomerID")%>' CommandName="Delete" Text="Delete" />

</div>

</div>

</asp:Panel>

<ajaxToolkit:HoverMenuExtender ID="hoverMenu" runat="server"

HoverCssClass="popupHover" PopDelay="300" PopupControlID="PopupMenu"

PopupPosition="Right" TargetControlID="PopupMenu" />

</ItemTemplate>

<EditItemTemplate>

<asp:Panel ID="popupMenu" runat="server">

<div style="border: 1px outset white; padding: 2px;">

<div>

<asp:LinkButton ID="lnkButtonUpdate" runat="server"

CommandArgument='<%#Eval("CustomerID")%>' CommandName="Update" Text="Update" />

</div>

<div>

<asp:LinkButton ID="lnkButtonCancel" runat="server"

CommandArgument='<%#Eval("CustomerID")%>' CommandName="Cancel" Text="Cancel" />

</div>

</div>

</asp:Panel>

<ajaxToolkit:HoverMenuExtender ID="hoverMenu" runat="server"

HoverCssClass="popupHover" PopDelay="300" PopupControlID="PopupMenu"

PopupPosition="Right" TargetControlID="PopupMenu" />

</EditItemTemplate>

<HeaderStyle Width="0px" />

<ItemStyle Width="0px" />

</asp:TemplateField>

</Columns>

</asp:GridView>

</ContentTemplate>

</asp:UpdatePanel>

</asp:Panel>

<ajaxToolkit:CollapsiblePanelExtender ID="cpe" runat="Server" TargetControlID="pnlOrders" CollapsedSize="0" Collapsed="True" ExpandControlID="pnlCustomer" CollapseControlID="pnlCustomer"

AutoCollapse="False" AutoExpand="False" ScrollContents="false" ImageControlID="imgCollapsible" ExpandedImage="~/Assets/img/minus.png" CollapsedImage="~/Assets/img/plus.png" ExpandDirection="Vertical" />

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</ContentTemplate>

</asp:UpdatePanel>

Few things to note here. The Parent Panel "pnlUpdateParent" UpdateMode is set to Conditional, and ChildrenAsTriggers is set to false. This is very important, because the Children GridViews will raise postback triggers, and that will cause the whole UpdatePanel to be updated and I don't want that. I just want the child GridView that caused the postback to be updated, while the parent GridView remain silent.
Next you'll notice Triggers tag. Yes, I wanted the Parent UpdatePanel to be only updated when the Parent GridView raises PageIndexChanged Event. that means only upon paging.

Of course the "sqlDsOrders" SqlDataSource has update and delete statement defined. All columns in this GridView are read only except one column. I didn't show them here to save the size of this post. As you can see I declared 3 Event Handlers here:

• OnRowCreated: used to initialize HoverMenuExtender.
• OnRowEditing: used to specifying current editing row.
• OnRowComman: used to specify select parameter value of the SqlDataSource.

It worth to mention that OnRowComman event will be fired and executed every time any command is triggered. And will be fired first and before any other command event fired.


CodeBehind:

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.OleDb;

using System.Text;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using AjaxControlToolkit;

public partial class _Default : System.Web.UI.Page

{

    protected void gvCustomers_RowCreated(object sender, GridViewRowEventArgs e)

    {

        if (e.Row.RowType == DataControlRowType.DataRow)

        {

            SqlDataSource ctrl = e.Row.FindControl("sqlDsOrders") as SqlDataSource;

            if (ctrl != null && e.Row.DataItem != null)

            {

                ctrl.SelectParameters["CustomerID"].DefaultValue = ((DataRowView)e.Row.DataItem)["CustomerID"].ToString();

            }

        }

    }

    protected void gvOrders_RowCreated(object sender, GridViewRowEventArgs e)

    {

        if (e.Row.RowType == DataControlRowType.DataRow)

        {

            HoverMenuExtender hoveMenu = (HoverMenuExtender)e.Row.FindControl("hoverMenu");

            e.Row.ID = e.Row.RowIndex.ToString();

            hoveMenu.TargetControlID = e.Row.ID;

        }

    }

    protected void gvOrders_RowCommand(object s, GridViewCommandEventArgs e)

    {

        //CustomerID is stored as event Commend Argument

        string customerId = (string)e.CommandArgument;

        GridView orders = (GridView)s;

        Control parent = orders.Parent.Parent;

        //Find SqlDataSource

        SqlDataSource src = (SqlDataSource)parent.FindControl("sqlDsOrders");

        //Set select parameter value.

        src.SelectParameters[0].DefaultValue = customerId;

    }

    protected void gvOrders_RowEditing(object s, GridViewEditEventArgs e)

    {

        GridView orders = (GridView)s;

        orders.EditIndex = e.NewEditIndex;

        orders.DataBind();

    }

}