Display ASP.Net GridView Column total in Footer when AutoGenerateColumns is true using C# and VB.Net

Hi ashfaq,

Refer below sample.

Database

For this sample I have used of NorthWind database that you can download using the link given below.

Download Northwind Database

SQL

CREATE PROCEDURE DisplayTotalByOrderDate
	@OrderDate1 DATETIME = NULL,
	@OrderDate2 DateTime = NULL
AS
BEGIN
	SELECT DISTINCT  TOP 10 c.CustomerID, CONVERT(VARCHAR,o.OrderDate,101) OrderDate, c.ContactName, o.Freight FROM Customers c
	INNER JOIN Orders o ON o.CustomerId = c.CustomerID
	WHERE OrderDate BETWEEN @OrderDate1 AND @OrderDate2  OR @OrderDate2 IS NULL 
END
GO
EXEC DisplayTotalByOrderDate

HTML

StartDate :
<asp:TextBox runat="server" ID="txtStartDate" />
<br />
EndDate :
<asp:TextBox runat="server" ID="txtEndDate" />
<br />
<asp:Button Text="Search" runat="server" OnClick="Search" />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" ShowFooter="true">
</asp:GridView>

Namespaces

C#

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB.Net

Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        BindGrid("", "");
    }
}

private void BindGrid(string startDate, string endDate)
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("DisplayTotalByOrderDate", con))
        {
            if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate))
            {
                cmd.Parameters.AddWithValue("@OrderDate1", startDate);
                cmd.Parameters.AddWithValue("@OrderDate2", endDate);
            }
            cmd.CommandType = CommandType.StoredProcedure;
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                DataTable dt = new DataTable();
                da.Fill(dt);
                this.GridView1.DataSource = dt;
                this.GridView1.DataBind();
                decimal total = dt.AsEnumerable().Sum(row => row.Field<decimal>("Freight"));
                GridView1.FooterRow.Cells[1].Text = "Total";
                GridView1.FooterRow.Cells[1].HorizontalAlign = HorizontalAlign.Right;
                GridView1.FooterRow.Cells[3].Text = total.ToString();
            }
        }
    }
}

protected void Search(object sender, EventArgs e)
{
    BindGrid(txtStartDate.Text, txtEndDate.Text);
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        BindGrid("", "")
    End If
End Sub

Private Sub BindGrid(ByVal startDate As String, ByVal endDate As String)
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("DisplayTotalByOrderDate", con)
            If Not String.IsNullOrEmpty(startDate) AndAlso Not String.IsNullOrEmpty(endDate) Then
                cmd.Parameters.AddWithValue("@OrderDate1", startDate)
                cmd.Parameters.AddWithValue("@OrderDate2", endDate)
            End If

            cmd.CommandType = CommandType.StoredProcedure
            Using da As SqlDataAdapter = New SqlDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
                Me.GridView1.DataSource = dt
                Me.GridView1.DataBind()
                Dim total As Decimal = dt.AsEnumerable().Sum(Function(row) row.Field(Of Decimal)("Freight"))
                GridView1.FooterRow.Cells(1).Text = "Total"
                GridView1.FooterRow.Cells(1).HorizontalAlign = HorizontalAlign.Right
                GridView1.FooterRow.Cells(3).Text = total.ToString()
            End Using
        End Using
    End Using
End Sub

Protected Sub Search(ByVal sender As Object, ByVal e As EventArgs)
    BindGrid(txtStartDate.Text, txtEndDate.Text)
End Sub

Screenshot