Addition in Grid View using Sql Query:

Addition in Grid View using Sql Query:
Table Data:
Here I create two tables department table and employee table:

Queries can be used:
select * from tbdep
select * from tbemployee

select empdept, ISNULL (empname,'S.Total') empname, SUM(empsalary) empsalary, COUNT(*) NOE from tbemployee group by empdept,empname with rollup

select dname, ISNULL(empname,'S.Total') empname, SUM(empsalary) empsalary, COUNT(*) NOE from tbemployee,tbdep where empdept=dno group by dname,empname with rollup

select isnull (dname,'G.Total') dname, ISNULL(empname,'S.Total') empname, SUM(empsalary) empsalary from tbemployee,tbdep where empdept=dno group by dname,empname with rollup

select case when(Grouping (dname)=1) then 'G.Total' else dname end dname, case when (Grouping(empname)=1 )then 'S.Total' else empname end empname, Sum(empsalary) empsalary from tbemployee, tbdep where empdept=dno group by dname, empname with rollup


<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default11.aspx.cs" Inherits="Default11" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="true" OnRowDataBound="GridView1_RowDataBound">
        </asp:GridView>
        &nbsp;<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" OnRowDataBound="GridView2_RowDataBound" OnSelectedIndexChanged="GridView2_SelectedIndexChanged">
            <Columns>
                <asp:BoundField DataField="empname" HeaderText="Name">
                <ControlStyle BackColor="Fuchsia" />
                <HeaderStyle BackColor="White" Font-Bold="True" Font-Size="X-Large" ForeColor="#FF0066" />
                </asp:BoundField>
                <asp:BoundField DataField="empsalary" HeaderText="Salary" >
               <ControlStyle BackColor="Fuchsia" />
                <HeaderStyle BackColor="White" Font-Bold="True" Font-Size="X-Large" ForeColor="#FF0066" />
                </asp:BoundField>
            </Columns>
        </asp:GridView>
        <br />
    
    </div>
    </form>
</body>
</html>

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;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing;

public partial class Default11 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (Page.IsPostBack == false)
        {
            grid_bind();
        }
    }
    private void grid_bind()
    {
        //String qry = "select case when(Grouping (dname=1) then 'G.Total' else dname and dname, case when (Grouping(ename)=1 then 'S.Total' else empnam and empnam, Sum(esal) esal from tbemp, tbdep where edno=dno group by dname, empnam with rollup";
        String qry = "select case when(Grouping (dname)=1) then 'G.Total' else dname end dname, case when (Grouping(empname)=1 )then 'S.Total' else empname end empname, Sum(empsalary) empsalary from tbemployee, tbdep where empdept=dno group by dname, empname with rollup";
        SqlDataAdapter da=new SqlDataAdapter(qry,ConfigurationManager.ConnectionStrings["abhishek"].ConnectionString);
        DataSet ds=new DataSet();
        da.Fill(ds);
        Int32 i=0;
        String pd=" ",nd;
        while(i<ds.Tables[0].Rows.Count-1)
        {
            nd=ds.Tables[0].Rows[i][0].ToString();
            if(pd!=nd)
            {
                pd = nd;
                DataRow dr=ds.Tables[0].NewRow();
                dr[1]=nd;
                dr[2]="-1";
                //to insert row at the centre Insert at is used
                ds.Tables[0].Rows.InsertAt(dr,i);
                i++;
            }
            i++;
            ds.Tables[0].Rows[ds.Tables[0].Rows.Count-1][1]="G.Total";
            GridView1.DataSource=ds;
            GridView1.DataBind();
            GridView2.DataSource = ds;
            GridView2.DataBind();
            }
        }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.Cells[2].Text == "-1")
        {
            e.Row.BackColor = Color.Yellow;
        }
        if (e.Row.Cells[0].Text == "G.Total")
        {
            e.Row.BackColor = Color.Red;
        }
        if (e.Row.Cells[1].Text == "S.Total")
        {
            e.Row.BackColor = Color.Green;
        }

    }
    protected void GridView2_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.Cells[1].Text == "-1")
        {
            e.Row.BackColor = Color.RoyalBlue;
            e.Row.Cells[0].ColumnSpan = 2;
            e.Row.Cells[0].Font.Bold = true;
            e.Row.Cells[0].HorizontalAlign = HorizontalAlign.Center;
            e.Row.Cells.RemoveAt(1);
        }
        if (e.Row.Cells[0].Text == "G.Total")
        {
            e.Row.BackColor = Color.Red;
            e.Row.Cells[0].Font.Bold = true;
        }
        if (e.Row.Cells[0].Text == "S.Total")
        {
            e.Row.BackColor = Color.SkyBlue;
        }
    }
    protected void GridView2_SelectedIndexChanged(object sender, EventArgs e)
    {

    }
}

Ouput:




0 comments:

Post a Comment