santosh-asp tutorial

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.

We are using SQL SERVER, so we can use following 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)

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="GridView1" runat="server" AutoGenerateColumns="False"
	onrowdatabound="GridView1_RowDataBound"  onrowcreated="GridView1_RowCreated">
	<Columns>
		  <asp:BoundField DataField="StoreId" HeaderText="StoreId" />
		  <asp:BoundField DataField="PName" HeaderText="Product Name" />
		 
		  <asp:BoundField DataField="Quantity" HeaderText="Quantity" ItemStyle-HorizontalAlign="Right"/>
	</Columns>
	</asp:GridView>
    </div>
    </form>
</body>
</html>

Default.aspx.cs 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;
    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;
        }
    }
}