Nested Loops problem

Lio Liov

12y1.3kVisual Basic .NET

I am trying to achieve the bottom result .So far I tried with nested loops
without sucsees.Any other sugestions?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes12y ago
Accepted answer
I think you'll find it easier if you sort the DataTable by EmpID to start with.

I've done it again without using LINQ but using the data in my first post so I can check it's giving the same results, which it is:

using System;
using System.Data;

class Program
{
static void Main()
{
DataTable dt = new DataTable();
dt.Columns.Add("EmpID");
dt.Columns.Add("Mon", typeof(int));
dt.Columns.Add("Tue", typeof(int));
dt.Columns.Add("Wed", typeof(int));
dt.Columns.Add("Thu", typeof(int));
dt.Columns.Add("Fri", typeof(int));
dt.Columns.Add("Sat", typeof(int));
dt.Columns.Add("Sun", typeof(int));

// mix them up a bit
dt.Rows.Add("Emp1",4,0,0,0,0,0,0);
dt.Rows.Add("Emp2",0,4,0,0,0,0,0);
dt.Rows.Add("Emp1",0,0,0,5,0,0,0);
dt.Rows.Add("Emp2",0,0,5,0,0,0,0);
dt.Rows.Add("Emp2",0,0,0,0,0,0,3);
dt.Rows.Add("Emp1",0,0,0,0,8,0,0);

DataView dv = dt.DefaultView;
dv.Sort = "EmpID"; // sort by EmpID
DataTable dt2 = dv.ToTable();

DataTable dt3 = dt.Clone();

string id = "";
string temp = null;
int[] total = new int[7];
object[] oa = null;
DataRow r = null;

foreach(DataRow dr in dt2.Rows)
{
temp = dr[0].ToString();
if (id == "")
{
id = temp;
}
else if (id != temp)
{
r = dt3.NewRow();
oa = new object[8];
oa[0] = id;
for(int i = 1; i < 8; i++) oa[i] = total[i - 1];
r.ItemArray = oa;
dt3.Rows.Add(r);
id = temp;
Array.Clear(total, 0, 7);
}

for(int i = 0; i < 7; i++)
{
total[i] += (int)dr[i + 1];
}
}

// add the final row to the combined table
r = dt3.NewRow();
oa = new object[8];
oa[0] = id;
for(int i = 1; i < 8; i++) oa[i] = total[i - 1];
r.ItemArray = oa;
dt3.Rows.Add(r);

// check it worked
foreach(DataRow dr in dt3.Rows)
{
foreach(object o in dr.ItemArray)
{
Console.Write("{0} ", o);
}

Console.WriteLine();
}

Console.ReadKey();
}
}

0
Lio Liov12y ago
Thank you very much, I spend one day trying to acomplish that. I realy appreciate your help.

I tried to add first and last name.
and so far I god this code, but it is erroring out.
0
Lio Liov12y ago
Thanks. Unfortunately I dont have Linq avaliable in my framework.
0
Vulpes12y ago
Assuming that the data is held in a DataTable as per your previous thread (http://www.c-sharpcorner.com/Forums/Thread/245498/iteration-throug-array.aspx), then you can do it using LINQ as follows:

using System;
using System.Data;
using System.Linq;

class Program
{
static void Main()
{
DataTable dt = new DataTable();
dt.Columns.Add("EmpID");
dt.Columns.Add("Mon", typeof(int));
dt.Columns.Add("Tue", typeof(int));
dt.Columns.Add("Wed", typeof(int));
dt.Columns.Add("Thu", typeof(int));
dt.Columns.Add("Fri", typeof(int));
dt.Columns.Add("Sat", typeof(int));
dt.Columns.Add("Sun", typeof(int));
dt.Rows.Add("Emp1",4,0,0,0,0,0,0);
dt.Rows.Add("Emp1",0,0,0,5,0,0,0);
dt.Rows.Add("Emp1",0,0,0,0,8,0,0);
dt.Rows.Add("Emp2",0,4,0,0,0,0,0);
dt.Rows.Add("Emp2",0,0,5,0,0,0,0);
dt.Rows.Add("Emp2",0,0,0,0,0,0,3);

DataTable dt2 = dt.Clone();

var query = from DataRow dr in dt.Rows
group dr by dr[0] into g
select new
{
EmpId = g.Key.ToString(),
Mon = (from DataRow r in g select (int)r[1]).Sum(),
Tue = (from DataRow r in g select (int)r[2]).Sum(),
Wed = (from DataRow r in g select (int)r[3]).Sum(),
Thu = (from DataRow r in g select (int)r[4]).Sum(),
Fri = (from DataRow r in g select (int)r[5]).Sum(),
Sat = (from DataRow r in g select (int)r[6]).Sum(),
Sun = (from DataRow r in g select (int)r[7]).Sum()
};

foreach(var r in query)
{
DataRow dr = dt2.NewRow();
object[] oa = {r.EmpId, r.Mon, r.Tue, r.Wed, r.Thu, r.Fri, r.Sat, r.Sun};
dr.ItemArray = oa;
dt2.Rows.Add(dr);
}

// check it worked
foreach(DataRow dr in dt2.Rows)
{
foreach(object o in dr.ItemArray)
{
Console.Write("{0} ", o);
}

Console.WriteLine();
}

Console.ReadKey();
}
}

The output is:

Emp1 4 0 0 5 8 0 0
Emp2 0 4 5 0 0 0 3

0