Working with datagridview c # to load data

Question

0.00/5 (No votes)

See more:

I have a problem, i need help
I have table

ID EmpID Month_salary DaTe WorkShifts
1 001 MONTH01 2012-01-01 1
2 001 MONTH01 2012-01-02 2
3 001 MONTH01 2012-01-03 3
4 001 MONTH01 2012-01-03 1
5 001 MONTH01 2012-01-04 2
6 001 MONTH01 2012-01-05 3
.. ... ........ .......... ..
31 001 MONTH01 2012-01-31 3

I want datagridview :

EmpID	2012-01-01 1 \|2 \|3	2012-01-02 1 \|2 \|3	2012-01-03 1 \|2 \|3	..........	2012-01-31 1 \|2 \|3
001	x\|x\|x	x\|x\|x	x\|x\|x	..........	x\|x\|x
002	x\|x\|x	x\|x\|x	x\|x\|x	..........	x\|x\|x

1,2,3 means the work shift, each day there are three shifts. For example, here a month has 31 days. letter x means that the employee has to work.
How can I do to do as requested? Thanks all

Posted 3-Jun-12 17:48pm

violetqa

Updated 3-Jun-12 18:02pm

v2

Add a Solution

Comments

VJ Reddy 4-Jun-12 4:04am

Thank you for accepting the solution :)

2 solutions

Solution 1

Pivot your data on the date, it should be done in the database

Posted 3-Jun-12 18:00pm

Mycroft Holmes

Comments

violetqa 4-Jun-12 0:04am

Can you guide details more? Thanks

violetqa 4-Jun-12 0:13am

I were pivot your data on the date but how do I get for each day work shift?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

VJ Reddy · Accepted Answer · 2012-06-03T19:42:00

The given data can be transformed into the required format using LINQ query as shown below:

C#

DataTable shiftData = new DataTable("ShiftData");
shiftData.Columns.Add("Id",typeof(int),null);
shiftData.Columns.Add("EmpId",typeof(string),null);
shiftData.Columns.Add("Month",typeof(string),null);
shiftData.Columns.Add("Date",typeof(DateTime),null);
shiftData.Columns.Add("Shift",typeof(int),null);

shiftData.Rows.Add(1,"001","Month01",new DateTime(2012,1,1),1);
shiftData.Rows.Add(2,"001","Month01",new DateTime(2012,1,2),2);
shiftData.Rows.Add(3,"001","Month01",new DateTime(2012,1,3),3);
shiftData.Rows.Add(4,"001","Month01",new DateTime(2012,1,3),1);
shiftData.Rows.Add(5,"001","Month01",new DateTime(2012,1,4),2);
shiftData.Rows.Add(6,"001","Month01",new DateTime(2012,1,5),3);
shiftData.Rows.Add(7,"002","Month01",new DateTime(2012,1,1),1);
shiftData.Rows.Add(8,"002","Month01",new DateTime(2012,1,2),2);
shiftData.Rows.Add(9,"002","Month01",new DateTime(2012,1,3),3);
shiftData.Rows.Add(10,"001","Month02",new DateTime(2012,2,3),1);
shiftData.Rows.Add(11,"001","Month02",new DateTime(2012,2,4),2);
shiftData.Rows.Add(12,"001","Month02",new DateTime(2012,2,5),3);

DataTable empShifts = new DataTable("EmpShifts");
empShifts.Columns.Add("EmpId",typeof(string),null);
empShifts.Columns.Add("Month",typeof(string),null);

for(int i=1; i<= 31; i++)
    for(int j=1; j <=3; j++)
        empShifts.Columns.Add(i.ToString()+"-" + j.ToString(),typeof(string),null);

shiftData.AsEnumerable().GroupBy (dr => dr.Field<string>("EmpId")).Select (er => {
er.GroupBy (dr2 => dr2.Field<string>("Month")).Select (mr => {
    DataRow row = empShifts.NewRow();
    row["EmpId"]=er.Key;
    row["Month"]=mr.Key;
    mr.Select (ms => 
    	row[ms.Field<datetime>("Date").Day + "-" + ms.Field<int>("Shift").ToString()]="x").Count ();
    empShifts.Rows.Add(row);
    return mr;
    }).Count ();
return er;
}).Count ();

	
//ShiftData
//Id EmpId Month Date Shift
//1 001 Month01 1/1/2012 12:00:00 AM 1 
//2 001 Month01 1/2/2012 12:00:00 AM 2 
//3 001 Month01 1/3/2012 12:00:00 AM 3 
//4 001 Month01 1/3/2012 12:00:00 AM 1 
//5 001 Month01 1/4/2012 12:00:00 AM 2 
//6 001 Month01 1/5/2012 12:00:00 AM 3 
//7 002 Month01 1/1/2012 12:00:00 AM 1 
//8 002 Month01 1/2/2012 12:00:00 AM 2 
//9 002 Month01 1/3/2012 12:00:00 AM 3 
//10 001 Month02 2/3/2012 12:00:00 AM 1 
//11 001 Month02 2/4/2012 12:00:00 AM 2 
//12 001 Month02 2/5/2012 12:00:00 AM 3 

//empShift
//Headings--->EmpId Month 1-1 1-2 1-3 2-1 2-2 2-3 3-1 3-2 3-3 4-1 4-2 4-3 5-1 5-2 5-3 6-1 6-2 6-3 7-1 7-2 7-3 8-1 8-2 8-3 9-1 9-2 9-3 10-1 10-2 10-3 11-1 11-2 11-3 12-1 12-2 12-3 13-1 13-2 13-3 14-1 14-2 14-3 15-1 15-2 15-3 16-1 16-2 16-3 17-1 17-2 17-3 18-1 18-2 18-3 19-1 19-2 19-3 20-1 20-2 20-3 21-1 21-2 21-3 22-1 22-2 22-3 23-1 23-2 23-3 24-1 24-2 24-3 25-1 25-2 25-3 26-1 26-2 26-3 27-1 27-2 27-3 28-1 28-2 28-3 29-1 29-2 29-3 30-1 30-2 30-3 31-1 31-2 31-3 
//Row1--->001 Month01 x null  null  null  x null  x null  x null  x null  null  null  x null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  
//Row2--->001 Month02 null  null  null  null  null  null  x null  null  null  x null  null  null  x null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  
//Row3--->002 Month01 x null  null  null  x null  null  null  x null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  null  </int></datetime></string></string>

The LINQ has deferred execution model, which means the query will be executed only when it is iterated or a scalar method like Count is called on it. Hence, in the above code to force immediate execution of the query Count extension method is used.

The DataTable generated above can be bound to the DataGridView.

The DataGridView does not have an in built feature to display Header in two rows as required. However, as explained here http://www.murrayhilltech.com/articles/articles.aspx?key=8[^] the DataGridView headers can be custom drawn.

EmpID	2012-01-01 1 \|2 \|3	2012-01-02 1 \|2 \|3	2012-01-03 1 \|2 \|3	..........	2012-01-31 1 \|2 \|3
001	x\|x\|x	x\|x\|x	x\|x\|x	..........	x\|x\|x
002	x\|x\|x	x\|x\|x	x\|x\|x	..........	x\|x\|x