Like


Figure 1 GridView

For this, I will use the following table structure.

Table Name
  1. Department (ID int, DeptName varchar(25) )
  2. Employee (Id int,deptid int,Name varchar(50),Address varchar(50),Phone varchar(15),datecreated datetime )
  3. 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

  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
  2. <!DOCTYPE html>
  3. <html xmlns="http://www.w3.org/1999/xhtml">
  4. <head runat="server">
  5. <title></title>
  6. </head>
  7. <body>
  8. <form id="form1" runat="server">
  9. <div>
  10. </div>
  11. <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound" OnRowCreated="GridView1_RowCreated">
  12. <Columns>
  13. <asp:BoundField DataField="Id" HeaderText="id" />
  14. <asp:BoundField DataField="name" HeaderText="name" />
  15. <asp:BoundField DataField="Phone" HeaderText="phone" />
  16. <asp:BoundField DataField="tot" HeaderText="salary" />
  17. <asp:TemplateField Visible="false" ItemStyle-HorizontalAlign="Right">
  18. <FooterTemplate>
  19. <div style="text-align: right;">
  20. <asp:Label ID="lblTotalqty" runat="server" Text='<%# Eval("total") %>' Font-Bold=true />
  21. </div>
  22. </FooterTemplate>
  23. </asp:TemplateField>
  24. </Columns>
  25. </asp:GridView>
  26. </form>
  27. </body>
  28. </html>
.aspx.cs page
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using System.Data.SqlClient;
  8. using System.Data;
  9. using System.Configuration;
  10. public partial class _Default : System.Web.UI.Page
  11. {
  12. int qtyTotal = 0;
  13. int grQtyTotal = 0;
  14. int storid = 0;
  15. int rowIndex = 1;
  16. protected void Page_Load(object sender, EventArgs e)
  17. {
  18. SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings ["conn"].ConnectionString);
  19. try
  20. {
  21. con.Open();
  22. 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);
  23. SqlDataAdapter adp = new SqlDataAdapter(cmd);
  24. DataSet ds = new DataSet();
  25. adp.Fill(ds);
  26. GridView1.DataSource = ds;
  27. GridView1.DataBind();
  28. }
  29. catch (SqlException ex)
  30. {
  31. //
  32. }
  33. finally
  34. {
  35. con.Close();
  36. }
  37. }
  38. protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
  39. {
  40. if (e.Row.RowType == DataControlRowType.DataRow)
  41. {
  42. storid = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "deptid").ToString());
  43. int tmpTotal = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "tot").ToString());
  44. qtyTotal += tmpTotal;
  45. grQtyTotal += tmpTotal;
  46. }
  47. if (e.Row.RowType == DataControlRowType.Footer)
  48. {
  49. Label lblTotalqty = (Label)e.Row.FindControl("lblTotalqty");
  50. lblTotalqty.Text = grQtyTotal.ToString();
  51. }
  52. }
  53. protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
  54. {
  55. bool newRow = false;
  56. if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "DeptId") != null))
  57. {
  58. if (storid != Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "DeptId").ToString()))
  59. newRow = true;
  60. }
  61. if ((storid > 0) && (DataBinder.Eval(e.Row.DataItem, "DeptId") == null))
  62. {
  63. newRow = true;
  64. rowIndex = 0;
  65. }
  66. if (newRow)
  67. {
  68. GridView GridView1 = (GridView)sender;
  69. GridViewRow NewTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
  70. NewTotalRow.Font.Bold = true;
  71. NewTotalRow.BackColor = System.Drawing.Color.Gray;
  72. NewTotalRow.ForeColor = System.Drawing.Color.White;
  73. TableCell HeaderCell = new TableCell();
  74. HeaderCell.Text = "Total";
  75. HeaderCell.HorizontalAlign = HorizontalAlign.Left;
  76. HeaderCell.ColumnSpan = 3;
  77. NewTotalRow.Cells.Add(HeaderCell);
  78. HeaderCell = new TableCell();
  79. HeaderCell.HorizontalAlign = HorizontalAlign.Right;
  80. HeaderCell.Text = qtyTotal.ToString();
  81. NewTotalRow.Cells.Add(HeaderCell);
  82. GridView1.Controls[0].Controls.AddAt(e.Row.RowIndex + rowIndex, NewTotalRow);
  83. rowIndex++;
  84. qtyTotal = 0;
  85. }
  86. }
  87. }
I hope this blog is helpful for beginners.