How do I solve the subgrouping in nested gridview

I have a gridview problem while binding more than two gridviews.

I am giving a sample scenario of what I have done so far

I have two tables

First table: Department , having columns DeptId and DeptName

10 Accounts 20 Software

Second Table: Emp having columns EmpId, EmpName,Salary, DeptID.

101 aaa 100 10 101 aaa 200 10 101 aaa 300 10 202 bb 101 20 202 bb 102 20 102 cc 100 10 201 dd 103 20

I am supposed to show first all the employees ordered by their deptname.

Also I am supposed to show the sum of each employees salary and sum of the entire department's salary

So the above values should appear as below

101 aaa 100 10 101 aaa 200 10 101 aaa 300 10 sum of emp 101 is 600 102 cc 100 10 sum of emp 102 is 100 SUM OF DEPT 10 IS 700

202 bb 101 20 202 bb 102 20 sum of emp 202 is 203 201 dd 103 20 sum of emp 202 is 103 SUM OF DEPT 20 IS 306

I am able to fetch sum of dept salary but not of employees

I have tried as below

My Design Page

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">
                <Columns>
                <asp:BoundField DataField="DeptID" />
                    <asp:TemplateField>
                        <ItemTemplate>

                                <asp:GridView ID="GridView2"  AutoGenerateColumns="false" runat="server">
                                    <Columns>
                                        <asp:BoundField DataField="EmpId" />
                                        <asp:BoundField DataField="EmpName" />
                                        <asp:BoundField DataField="Salary" />
                                    </Columns>
                                </asp:GridView>
                                <asp:GridView ID="GridView3"  AutoGenerateColumns="false" runat="server">
                                    <Columns>
                                        <asp:BoundField DataField="SumSalary" />
                                    </Columns>
                                </asp:GridView>

                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>

My CodeBehind Page

public partial class _Default : System.Web.UI.Page 
{
      SqlConnection MyCon=new SqlConnection("server=ABC-415D0247602\\SQLEXPRESS; database=Employee ;integrated security=true;connection timeout=600");

    protected void Page_Load(object sender, EventArgs e)
    {
        MyCon.Open();
        SqlDataAdapter MyDa = new SqlDataAdapter("select * from department", MyCon);
        DataSet MyDs = new DataSet();
        MyDa.Fill(MyDs);
        DataTable MyDt = new DataTable();
        GridView1.DataSource = MyDs.Tables[0];
        GridView1.DataBind();


    }
    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType 开发者_如何学Python== DataControlRowType.DataRow)
        {
            string j = e.Row.Cells[0].Text;

            GridView GridView2 = (GridView)e.Row.FindControl("GridView2");
            SqlDataAdapter MyDa1 = new SqlDataAdapter("select * from Emp where deptid =" +Convert.ToInt16(j), MyCon);
            DataSet MyDs1 = new DataSet();
            MyDa1.Fill(MyDs1);
            DataTable MyDt1 = new DataTable();
            GridView2.DataSource = MyDs1.Tables[0];
            GridView2.DataBind();

            GridView GridView3 = (GridView)e.Row.FindControl("GridView3");
            SqlDataAdapter MyDa2 = new SqlDataAdapter("select sum(salary) as SumSalary from Emp where deptid =" + Convert.ToInt16(j), MyCon);
            DataSet MyDs2 = new DataSet();
            MyDa2.Fill(MyDs2);
            DataTable MyDt2 = new DataTable();
            GridView3.DataSource = MyDs2.Tables[0];
            GridView3.DataBind();
        }
    }
}

I do not know where to place the sum of the employee's salary

Thanks in advance Regards cmrhema

You can do it easier, using repeaters :) however because of the challange i did the following:

 //Here lays the dragons 

A small note: 1st you dont need 3 gridViews to order by depid , you can use :

 SELECT * FROM Emp  Order By depid 

this can Combine both GridwView1 and GridView2, and to design it, just combine the columns from Gridview1 and 2 like Shown in the solution below.

The design of GridView1

 < asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">
            <Columns>
            <asp:BoundField DataField="DeptID" /> 
             <asp:BoundField DataField="EmpId" />
            <asp:BoundField DataField="EmpName" />
            <asp:BoundField DataField="Salary" />
            </Columns>

Solution:

WARNING : This Solution is not simple,and definetly not optimized :)

The idea is to do the manipulation before binding the data to the GridViews.

The solution If your using 2 GridViews (like i recommended above)
on page_load, run the above SQL, and fill it in a Dataset (DS1),
Create a table (dt) with the data u filled in DS1
Then create a clone DataTable (dt2)

then start a loop through the rows of dt1
1st you record the EmpID as CurrentID
loop (foreach row in dt1) throughRowDataBound the rows summing the salary from each row untill CurrentID != ReadID
Add a row to the Dataset(DS1) with column "EMPID"'s value = "-54321" //this will be used later to identify the irregular rows//
, and the salary = Sum
make the sum = 0,
Make CurrentID = ReadID Continue the loop;

then Bind DS1 To gridView1.

ON Event: GridView1_RowCreated

you check if the Row being Created has the ID "-54321" (using DataRowView) if it does , u Read the sum into a variable, then Clear , add columns as desired, something like :

           e.Row.Cells.Clear(); 
            TableCell oCell = new TableCell(); 
            e.Row.Cells.Add(oCell); 

A similar approach can be used to add the extra row for the Dep salary sum :)

Certainly I am not going to downvote the answer. You suggested repeaters, but I went for datalist control instead of the parent grid. Actually I am going for 7 different tables and doing inner join in LINQ TO SQL and thereby retrieving the answer in Ilist. As I was not able to frame all the 7 tables and stuff, I just provided a small snapshot of the whole page.

Here's what I did Took a datalist had the keynames as deptid. (1st datalist) Took another datalist (datalist2) in the item template of datalist1, bearing the keynames of empid Dropped a gridview in the datalist2, which will have the employee details in the template field of gridview, and had a footer row .

Now the pseudo code was something nearer to this

for i =1 to datalist1 bind datalist2 for j=1 to datalist2 bind gridview

in the rowdatabound of gridview { sum up the salaries

if (datarow==footerrow) { display the footer along with the summed up salaries }

Thanks Regards cmrhema