Subtotal row in Gridview
In the following lesson, you can learn how to implement a grouping and calculate subtotal for each group in ASP.Net GridView.
Database
In this article I have used Microsoft's Pubs database for sample data. You can download it free from the following link.
Download Subtotal in GridView
To obtain the desired result of calculating the quantities supplied from each store, the program retrieves data from the STOR table in the PUBS database. To achieve this, the program executes SQL statements specifically designed to retrieve store-wise data.
By formulating appropriate SQL queries, developers can extract the necessary information from the STOR table. These queries are tailored to retrieve the relevant data, such as quantities supplied, from each individual store.
The program processes these SQL statements and retrieves the required data, allowing for further calculations to determine the quantities supplied by each store. This calculation can be performed using programming logic, such as iterating through the retrieved data and aggregating the quantities accordingly.
By employing SQL statements and implementing suitable programming logic, developers can successfully retrieve store-wise data from the STOR table and calculate the quantities supplied by each store. This information provides valuable insights into the distribution and supply chain aspects of the PUBS database, facilitating informed decision-making and analysis.
select distinct stor_id,ord_num,title_id,qty from sales
group by stor_id,ord_num,title_id,qty
From the result of the above sql, the program find the subtotal of each store and display in the GridView. After calculating the subtotal, we are forced to insert a new row after each store data and display the subtotal.
Default.aspx
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
onrowdatabound="GridView1_RowDataBound" onrowcreated="GridView1_RowCreated">
<Columns>
<asp:BoundField DataField="stor_id" HeaderText="stor_id" />
<asp:BoundField DataField="ord_num" HeaderText="ord_num" />
<asp:BoundField DataField="title_id" HeaderText="title_id" />
<asp:BoundField DataField="qty" HeaderText="qty" ItemStyle-HorizontalAlign="Right"/>
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>
C# Source Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
public partial class _Default : System.Web.UI.Page
{
int qtyTotal = 0;
int storid = 0;
int rowIndex = 1;
protected void Page_Load(object sender, EventArgs e)
{
SqlDataAdapter adapter = new SqlDataAdapter();
DataSet ds = new DataSet();
int i = 0;
string sql = null;
string connetionString = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****";
sql = "select distinct top 14 stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty";
SqlConnection connection = new SqlConnection(connetionString);
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
adapter.SelectCommand = command;
adapter.Fill(ds);
adapter.Dispose();
command.Dispose();
connection.Close();
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString());
int tmpTotal = Convert.ToInt32 (DataBinder.Eval(e.Row.DataItem, "qty").ToString());
qtyTotal += tmpTotal;
}
}
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
bool newRow = false;
if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") != null))
{
if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()))
newRow = true;
}
if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "stor_id") == null))
{
newRow = true;
rowIndex = 0;
}
if (newRow)
{
GridView GridView1 = (GridView)sender;
GridViewRow NewTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
NewTotalRow.Font.Bold = true;
NewTotalRow.BackColor = System.Drawing.Color.Gray;
NewTotalRow.ForeColor = System.Drawing.Color.White;
TableCell HeaderCell = new TableCell();
HeaderCell.Text = "Sub Total";
HeaderCell.HorizontalAlign = HorizontalAlign.Left ;
HeaderCell.ColumnSpan = 3;
NewTotalRow.Cells.Add(HeaderCell);
HeaderCell = new TableCell();
HeaderCell.HorizontalAlign = HorizontalAlign.Right;
HeaderCell.Text = qtyTotal.ToString();
NewTotalRow.Cells.Add(HeaderCell);
GridView1.Controls[0].Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow);
rowIndex++;
qtyTotal = 0;
}
}
}
VB.Net Source Code
Imports System.Drawing
Imports System.Data.SqlClient
Imports System.Data
Partial Class _Default
Inherits System.Web.UI.Page
Dim qtyTotal As Integer = 0
Dim storid As Integer = 0
Dim rowIndex As Integer = 1
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim adapter As New SqlDataAdapter()
Dim ds As New DataSet()
Dim i As Integer = 0
Dim sql As String = Nothing
Dim connetionString As String = "Data Source=.;Initial Catalog=pubs;User ID=sa;Password=*****"
sql = "select distinct top 14 stor_id,ord_num,title_id,qty from sales group by stor_id,ord_num,title_id,qty"
Dim connection As New SqlConnection(connetionString)
connection.Open()
Dim command As New SqlCommand(sql, connection)
adapter.SelectCommand = command
adapter.Fill(ds)
adapter.Dispose()
command.Dispose()
connection.Close()
GridView1.DataSource = ds.Tables(0)
GridView1.DataBind()
End Sub
Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString())
Dim tmpTotal As Integer = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "qty").ToString())
qtyTotal += tmpTotal
End If
End Sub
Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
Dim newRow As Boolean = False
If (storid > 0) AndAlso (DataBinder.Eval(e.Row.DataItem, "stor_id") IsNot Nothing) Then
If storid <> Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "stor_id").ToString()) Then
newRow = True
End If
End If
If (storid > 0) AndAlso (DataBinder.Eval(e.Row.DataItem, "stor_id") Is Nothing) Then
newRow = True
rowIndex = 0
End If
If newRow Then
Dim GridView1 As GridView = DirectCast(sender, GridView)
Dim NewTotalRow As New GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert)
NewTotalRow.Font.Bold = True
NewTotalRow.BackColor = System.Drawing.Color.Gray
NewTotalRow.ForeColor = System.Drawing.Color.White
Dim HeaderCell As New TableCell()
HeaderCell.Text = "Sub Total"
HeaderCell.HorizontalAlign = HorizontalAlign.Left
HeaderCell.ColumnSpan = 3
NewTotalRow.Cells.Add(HeaderCell)
HeaderCell = New TableCell()
HeaderCell.HorizontalAlign = HorizontalAlign.Right
HeaderCell.Text = qtyTotal.ToString()
NewTotalRow.Cells.Add(HeaderCell)
GridView1.Controls(0).Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow)
rowIndex += 1
qtyTotal = 0
End If
End Sub
End Class