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;
- }
- }
- }
I hope this blog is helpful for beginners.