Post
Ask Question

GridView Grouping in Asp.Net

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.