santosh-asp tutorial

SubTotal and GrandTotal in GridView

In the following lesson, you can learn how to implement a grouping and calculate subtotal for each group and GrandTotal for all groups in ASP.Net GridView.

Database
We are using SQL SERVER database:-

  

create table tblProduct
  (
     StoreId int,PName varchar(100),Quantity int
  )
Insert into tblProduct values(3033,'Pen',20)
Insert into tblProduct values(3033,'Car',30)
Insert into tblProduct values(3033,'Bike',56)
Insert into tblProduct values(2020,'Bag',21)
Insert into tblProduct values(2020,'Note Book',11)
Insert into tblProduct values(1001,'Pen',20)
Insert into tblProduct values(1001,'Car',30)
Insert into tblProduct values(2010,'Bike',56)
Insert into tblProduct values(2010,'Bag',21)
Insert into tblProduct values(2010,'Note Book',11)
Insert into tblProduct values(2010,'Note Book',11)

A subtotal is a total of the subgroup values and a grand total is a total of all calculations on a report.

Here we retrieves data from the STOR table of PUBS databae and find the subtotal of quantities from each store and finally find the GrandTotal of quantity from all stores.

For displaying quantity on each row we insert an ItemTemplate for quantity field. subtotal and grandtotal For displaying GrandTotal at the footer, we insert a FooterTemplate at thebottom of the GridView. grandtotal footer And for displaying subtotal , the program dynamically add a new row after each group in the Gridview.

Default.aspx code

  

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" ShowFooter="true"
	onrowdatabound="GridView1_RowDataBound"  onrowcreated="GridView1_RowCreated">
		<Columns>
		  <asp:BoundField DataField="StoreId" HeaderText="StoreId" />
		  <asp:BoundField DataField="PName" HeaderText="Product Name" />
		  
		  <asp:TemplateField HeaderText="Quantity" ItemStyle-HorizontalAlign="Right">
			 <ItemTemplate>
				<asp:Label ID="lblqty" runat="server" Text='<%# Eval("Quantity") %>' />
			 </ItemTemplate>
			 <FooterTemplate>
				<div style="text-align: right;">
				<asp:Label ID="lblTotalqty" runat="server"  Font-Bold="true"  />
				</div>
			 </FooterTemplate>
		  </asp:TemplateField>
		</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 grQtyTotal = 0;
    int storid = 0;
    int rowIndex = 1;
    string connectionString = "Data Source=PS-PC\\SANTOSH;Initial Catalog=dbSantoshTest;Integrated Security=True";
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            loadRecord();
        }
    }

    protected void loadRecord()
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        int count = ds.Tables[0].Rows.Count;
        con.Close();
        if (ds.Tables[0].Rows.Count > 0)
        {

            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
        else
        {
            ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
            GridView1.DataSource = ds;
            GridView1.DataBind();
            int columncount = GridView1.Rows[0].Cells.Count;

        }
    }
    protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        
        bool newRow = false;
        if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "StoreId") != null))
        {
            if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "StoreId").ToString()))
                newRow = true;
        }
        if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "StoreId") == 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;
        }
    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
      
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "StoreId").ToString());
            int tmpTotal = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "Quantity").ToString());
            qtyTotal += tmpTotal;
            grQtyTotal += tmpTotal;
        }
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            Label lblTotalqty = (Label)e.Row.FindControl("lblTotalqty");
            lblTotalqty.Text = grQtyTotal.ToString();
        }
    }
}