In this article I will explain how to display SubTotal and Grand Total in ASP.Net GridView using C# and VB.Net.
The records are divided into Groups and then SubTotal is calculated for each Group and then displayed using a dynamic Row in GridView.
Database
For this article I am making use of the Microsoft’s Northwind Database. Download and install instructions are provided in the link below.
HTML Markup
The HTML Markup consists of an ASP.Net GridView which will display details of the Orders table present in the Northwind database. I have made use of OnRowCreated and OnDataBound events of GridView for displaying SubTotal and Grand Total.
<asp:GridView ID = "GridView1" runat = "server" AutoGenerateColumns = "false" OnDataBound = "OnDataBound" OnRowCreated = "OnRowCreated">
<Columns>
<asp:BoundField DataField = "OrderID" HeaderText = "Order ID" ItemStyle-Width = "60" />
<asp:BoundField DataField = "ProductName" HeaderText = "Product Name" ItemStyle-Width = "210" />
<asp:BoundField DataField = "Price" HeaderText = "Price" ItemStyle-Width = "60" DataFormatString = "{0:N2}" ItemStyle-HorizontalAlign = "Right" />
</Columns>
</asp:GridView>
Namespaces
You will need to import the following namespaces
C#
using System.Data;
using System.Drawing;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.Data
Imports System.Drawing
Imports System.Configuration
Imports System.Data.SqlClient
Binding the GridView
The GridView is populated with records inside the Page Load event of the page. The Query gets records of few Orders along with their Products and their respective costs. This result will be used to calculate the SubTotal for each Order and then finally the Grand Total for all Orders.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
this.BindGrid();
}
}
private void BindGrid()
{
string query = "SELECT OrderID,";
query += "(SELECT ProductName FROM Products WHERE ProductID = details.ProductId) ProductName,";
query += "(Quantity * UnitPrice) Price";
query += " FROM [Order Details] details";
query += " WHERE OrderID IN (10248, 10249, 10250)";
query += " ORDER BY OrderID";
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not IsPostBack Then
Me.BindGrid()
End If
End Sub
Private Sub BindGrid()
Dim query As String = "SELECT OrderID,"
query += "(SELECT ProductName FROM Products WHERE ProductID = details.ProductId) ProductName,"
query += "(Quantity * UnitPrice) Price"
query += " FROM [Order Details] details"
query += " WHERE OrderID IN (10248, 10249, 10250)"
query += " ORDER BY OrderID"
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(conString)
Using cmd As New SqlCommand(query)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
GridView1.DataSource = dt
GridView1.DataBind()
End Using
End Using
End Using
End Using
End Sub
Calculating and displaying SubTotal and Grand Total in GridView
I have declared some variables to calculate the SubTotal and the Grand Total so they need to be declared outside directly inside the class so that they are accessible inside all events and methods on the page.
Inside the OnRowCreated event handler, first we need to get the DataTable that was used to populate the GridView using the DataItem object. Once we have the DataTable we can easily get the Price of each product and use it for calculation of SubTotal and Grand Total.
The OrderId is continuously tracked using the currentId variable an as soon as a change is detected a new row is created and added to the GridView using the AddTotalRow method.
C#
int currentId = 0;
decimal subTotal = 0;
decimal total = 0;
int subTotalRowIndex = 0;
protected void OnRowCreated(object sender, GridViewRowEventArgs e)
{
subTotal = 0;
if (e.Row.RowType == DataControlRowType.DataRow)
{
DataTable dt = (e.Row.DataItem as DataRowView).DataView.Table;
int orderId = Convert.ToInt32(dt.Rows[e.Row.RowIndex]["OrderID"]);
total += Convert.ToDecimal(dt.Rows[e.Row.RowIndex]["Price"]);
if (orderId != currentId)
{
if (e.Row.RowIndex > 0)
{
for (int i = subTotalRowIndex; i < e.Row.RowIndex; i++)
{
subTotal += Convert.ToDecimal(GridView1.Rows[i].Cells[2].Text);
}
this.AddTotalRow("Sub Total", subTotal.ToString("N2"));
subTotalRowIndex = e.Row.RowIndex;
}
currentId = orderId;
}
}
}
private void AddTotalRow(string labelText, string value)
{
GridViewRow row = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal);
row.BackColor = ColorTranslator.FromHtml("#F9F9F9");
row.Cells.AddRange(new TableCell[3] { new TableCell (), //Empty Cell
new TableCell { Text = labelText, HorizontalAlign = HorizontalAlign.Right},
new TableCell { Text = value, HorizontalAlign = HorizontalAlign.Right } });
GridView1.Controls[0].Controls.Add(row);
}
VB.Net
Private currentId As Integer = 0
Private subTotal As Decimal = 0
Private total As Decimal = 0
Private subTotalRowIndex As Integer = 0
Protected Sub OnRowCreated(sender As Object, e As GridViewRowEventArgs)
subTotal = 0
If e.Row.RowType = DataControlRowType.DataRow Then
Dim dt As DataTable = TryCast(e.Row.DataItem, DataRowView).DataView.Table
Dim orderId As Integer = Convert.ToInt32(dt.Rows(e.Row.RowIndex)("OrderID"))
total += Convert.ToDecimal(dt.Rows(e.Row.RowIndex)("Price"))
If orderId <> currentId Then
If e.Row.RowIndex > 0 Then
For i As Integer = subTotalRowIndex To e.Row.RowIndex - 1
subTotal += Convert.ToDecimal(GridView1.Rows(i).Cells(2).Text)
Next
Me.AddTotalRow("Sub Total", subTotal.ToString("N2"))
subTotalRowIndex = e.Row.RowIndex
End If
currentId = orderId
End If
End If
End Sub
Private Sub AddTotalRow(labelText As String, value As String)
Dim row As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Normal)
row.BackColor = ColorTranslator.FromHtml("#F9F9F9")
'Empty Cell
row.Cells.AddRange(New TableCell(2) {New TableCell(), New TableCell() With { _
.Text = labelText, _
.HorizontalAlign = HorizontalAlign.Right _
}, New TableCell() With { _
.Text = value, _
.HorizontalAlign = HorizontalAlign.Right _
}})
GridView1.Controls(0).Controls.Add(row)
End Sub
The OnRowCreated event calculates SubTotal of all Groups except the last one since after the last row is added the OnRowCreated event does not fire and hence the SubTotal Row for the last Group cannot be added.
To handle this problem, I have made use of the OnDataBound event that occurs after the GridView is completely populated with data. Here the SubTotal as well as the Grand Total Row are added to the GridView.
C#
protected void OnDataBound(object sender, EventArgs e)
{
for (int i = subTotalRowIndex; i < GridView1.Rows.Count; i++)
{
subTotal += Convert.ToDecimal(GridView1.Rows[i].Cells[2].Text);
}
this.AddTotalRow("Sub Total", subTotal.ToString("N2"));
this.AddTotalRow("Total", total.ToString("N2"));
}
VB.Net
Protected Sub OnDataBound(sender As Object, e As EventArgs)
For i As Integer = subTotalRowIndex To GridView1.Rows.Count - 1
subTotal += Convert.ToDecimal(GridView1.Rows(i).Cells(2).Text)
Next
Me.AddTotalRow("Sub Total", subTotal.ToString("N2"))
Me.AddTotalRow("Total", total.ToString("N2"))
End Sub
![Display SubTotal and Grand Total in ASP.Net GridView]()
Demo
Downloads
