GridView Grouping in Asp.Net

Nanhe Siddique
9y
2.5k
0
0

Blog

proj.rar

Like

Figure 1 GridView

For this, I will use the following table structure.

Table Name

Department (ID int, DeptName varchar(25) )
Employee (Id int,deptid int,Name varchar(50),Address varchar(50),Phone varchar(15),datecreated datetime )
EmployeeSalary (EmployeeId int,deptid int,Salary double,datecreated)

Add three departments in Department table.
Add three employees in each Department.
Add minimum three month salary of each Employee.
And find the three month salary sum of each Employee and also find the sum of all salaries department wise as shown above.

aspx page

<%@ 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>
</div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound" OnRowCreated="GridView1_RowCreated">
<Columns>
<asp:BoundField DataField="Id" HeaderText="id" />
<asp:BoundField DataField="name" HeaderText="name" />
<asp:BoundField DataField="Phone" HeaderText="phone" />
<asp:BoundField DataField="tot" HeaderText="salary" />
<asp:TemplateField Visible="false" ItemStyle-HorizontalAlign="Right">
<FooterTemplate>
<div style="text-align: right;">
<asp:Label ID="lblTotalqty" runat="server" Text='<%# Eval("total") %>' Font-Bold=true />
</div>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</form>
</body>
</html>

.aspx.cs page

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;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
int qtyTotal = 0;
int grQtyTotal = 0;
int storid = 0;
int rowIndex = 1;
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings ["conn"].ConnectionString);
try
{
con.Open();
SqlCommand cmd = new SqlCommand(" select a.id,a.DeptId,a.Name,a.Phone,a.tot,b.total,b.DeptId from ( select e1.id, e1.DeptId,e1.Name,e1.Phone,sum(es.Salary) as tot from Department D1 join Employee e1 on D1.ID=e1.Deptid join EmployeeSalary es on e1.Id = es.EmployeeId group by d1.id ,e1.Id,e1.deptid,e1.Name,e1.Phone,es.Salary ) a join ( select deptid, SUM(Salary) as total from EmployeeSalary group by deptid ) b on a.DeptId=b.deptid", con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adp.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (SqlException ex)
{
//
}
finally
{
con.Close();
}
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "deptid").ToString());
int tmpTotal = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "tot").ToString());
qtyTotal += tmpTotal;
grQtyTotal += tmpTotal;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lblTotalqty = (Label)e.Row.FindControl("lblTotalqty");
lblTotalqty.Text = grQtyTotal.ToString();
}
}
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
bool newRow = false;
if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "DeptId") != null))
{
if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "DeptId").ToString()))
newRow = true;
}
if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "DeptId") == 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 = "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;
}
}
}