Complex Datagrid Databinding Help


I am developing a timesheet application in that has pretty straightforward functionality, although achieving it in will require some fairly complex code.  I will do my best to be as clear and complete as possible, but I will be more than willing to provide any clarification that you might need.  I've attached a PDF that contains the mock up of the interface that I am going off of to create this app so you can more clearly see what I am trying to do.

When an employee logs into their company site and navigates to the Timesheet page, they will see a datagrid that contains a column with a dropdown which will allow the user to select a Job # that they are recording time for.  There are also 7 columns for each day of the week so that they may enter their times for the entire week.  Each column will contain textboxes that allow the user to enter their Time In (Date/Time), Time Out(Date/Time), Time Spent At Lunch (In Hours), Time Spent at Dinner (Hours).  There are also fields that contain the total hours for each day and a column that contains the grand total number of hours for the week.

An employee will be able to enter hours for multiple jobs in a week, so there is an "Add Job" that will add another row to the datagrid just like the one described above and allow them to begin entering times for another job code.

The final row of this grid will contain totals hours for all jobs for each day, and one grand total that has the total number of hours per week.  All in all, it behaves like most spreadsheets you've used.

In the database I am storing the entries in a table called TimesheetEntries, each record in the table has the fields:

id - int
employee_id - int
job_id - int
entry_date - date/time
entry_in - date/time
entry_out - float
lunch_time - float

Essentially each cell in the datagrid will be a record in this table.  What I need to do is to figure out how to pull these records out into a dataset, and arrange them in a way that fits the timesheet format described above.  I would also like to be able to work with and manipulate this dataset without actually committing changes to the database until the user clicks 'save' on the timesheet.  

1) Is this possible
2) Can you help me do it? :)

Please check out the PDF for a clearer picture on how the timesheet should look.  I appreciate any thoughts that you have on this matter.


Who is Participating?
I'm not in the office this weekend either so my head isn't really in the game right now but I will address one of your questions regarding the dataset...
Yes, what I was suggesting with the dataset is that you use the dataset schema to create a structure  in-memory that you can use to hold your data and bind the grid to.  I wouldn't say that you would be 'manually' populating it but the point is that you leave the data structure in your database as-is and then populate a structure in your application that is easier to deal with.

Regarding the differing data types, there are various approaches there... ultimately a datetime is just an integer with a specific translation, so you could use 00:30 to represent the half hour; but even I would admit that is a bit hokey.  I guess where I would start is how do you intend to translate the values from your database values into the GUI.  What I mean to say is that if you have an in/out time that is 06:30 (essentially a 5/22/2009 06:30 in the database) and a lunch value that is 0.5 which is supposed to be represented as 0:30 on the GUI, then how are you going to make that formatting happen?  If you are going to use formatting in the grid, then you essentially have to have different formatting for different rows (which isn't very cool).  If you are going to format consistently in the grid, then you are going to have to translate to some consistent number / value before you get to the grid.  I think where you will run into the largest issue with this is with the user input... are you going to allow them to enter 0:30 and / or 0.5 into the boxes for lunch?  Or are you going to constrain them to a particular format?

It may be that once you really exmaine the nuiances of this aspect of your problem that using a grid turns out to not be such a great idea after all.  You may just want to create a control that represents the 4 high by 7 wide grid and binds the right stuff in the right places and then use a repeater to repeat the control for each job ID.  Part of it depends on whether you ever think the grid is going to need any other form of row and the other piece that plays in here is whether or not you need any other functionality that the native .Net grid provides.

If you do end up building a control like that, it may make your life easier because you could essentially bind in a way that more closely matches your data structure.  In this case you would essentially scratch the need for a dataset and have a collection of JobLine objects that drive your repeater and each JobLine would have a collection of DayEntries that would have the properties: DateofEntries, TimeIn, TimeOut, LunchDuration, DinnerDuration and you could iterate over the collection to fill in all the cells in your control.  As with many questions of software design, the 'right' answer has a lot to do with how flexible you need it to be in the future.

For information on pivot in SQL 2005, look up the PIVOT statement and give it a read and then let me know if you need more help.  If you take the approach of object collections as I just mentioned above, then you probably wouldn't even need the PIVOT statement.

And I would mention one more thing... if the lunch and dinner durations are meant to be expressed in minutes, you may be better off storing them as an int in the database (and calling them something like lunch_duration_in_minutes).  Two reasons: (1) if you ever have to do math with them it is usally easier (and more reliable) to convert an int to a float / decimal than to go the other way around and depend on a round integer to come out (think about what happens if you end up with a 0.66666666666 value in there) (2) an int is generally going to take less storage space and for these columns you can probably easily make due with a smallint which would take even less space.  Not sure how much concern you have over performance at this point, but it is alway good to keep in mind.

It sounds to me like you are asking two questions here... one is how to get from the database to the GUI and the other is how to get from the GUI back to the datbase, correct?

As with any programming issue of this nature there are mutliple solutions, but I will offer one option that comes to mind for me...

I would use a dataset that has columns like this...
entry_type - types would be 'in', 'out', 'lunch', 'dinner', 'total'
totals - a calculated column that totals the values of the other columns (doesn't get pulled in your SQL statement)

With that type of dataset structure you can then create the grid where you are essentially grouping by job_id.  Of course you coudl also implement with a repeater for the job_id values and then do sub-grids for each job that are bound to views on the dataset.

So then the issue become getting the data into the dataset and back out again, which is basically a pivot table type problem.  I'd give you more specific advice on a query to do that if I new what database system you are using; on SQL 2000 you will be doing the pivot the 'old fashioned way' but on the newer versions there is the PIVOT clause you can use.  Either way, you ought to be able to get the data in a single stored procedure call to the database.  

Going the other way (into the database) is a bit more complicated as you have to 'un-pivot' the data.  The easy, dirty, and inefficient way is to just iterate through the changed rows and save each cell one by one.  Another method would be to move the data from your display dataset back into a dataset (or collection of object) that match your data structure.  You could probably use LINQ to do this as it is essentially a pivot in the other direction (but not pretty).  The other method would be to have a stored procedure that takes a job_id and 28 other parameters so you essentially send the whole job grid in one shot and then execute 7 update statements with the data.

Hope that helps and let me know if you need more details or if I've left anything out that you were looking for.
MClarkCCRAuthor Commented:

Thanks for you response.  I won't be in the office again until Tuesday so I won't be able to begin working with your solution until then, however I do have some questions now that you might be able to resolve.

When you suggested I use a dataset like the one you described above, are you saying that is what my database table schema should be, or simply the schema of a dataset that I will create in .Net and I will be pulling the data from the database I described and manually populating the .Net dataset that better suits my interface?

The dataset schema you described essentially makes a row hold a week's worth of entries for a particular punch type (in, out, etc) for one job.  I do like this approach, but my only concern is that some of the punch types are for a specific date/time (in / out) and the lunch/dinner punches are for a timespan in hours (a value of .5 represents a 30 minute timespan).  Will the dataset schema you described work with these specifications?  If not, is there another schema that might be better suited for this type of mixing and matching of datatypes?

I am using SQL Server 2005 as my DB server.  I am not exactly a database expert, so you may have to map out your 'pivot table' approach for me in a bit of detail so that I can understand it.  I am vaguely aware of how PivotTables function and can see how this might be used to convert from one data structure to another and back again, but that is about it.  

Thanks again for your response, I realize that I am asking for a pretty complex solution and appreciate your time and patience in assisting me with this matter.

MClarkCCRAuthor Commented:
You were absolutely right.  Once I started to examine the nuances involved with both the lunch/dinner and other complexities of trying to essentially FORCE the datagrid to fit into my requirements, I've decided to build user controls that will represent one cell in the grid with all relevant fields and then another one that is a row that contains seven of the cells plus other necessary fields like a dropdown for job and grand totals, etc.  I haven't gotten incredibly far on it just yet but it is already shaping up to be way less painful than if I had continued using a datagrid.  Thank you for your assistance with my design, I think it will go a long way in helping me produce a great app.

Glad to hear I was able to help.  Happy coding.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.