MClarkCCR
asked on
Complex Datagrid Databinding Help
Hello,
I am developing a timesheet application in ASP.net that has pretty straightforward functionality, although achieving it in ASP.net 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.
Thanks!
Mike
Timesheet-Prototype.pdf
I am developing a timesheet application in ASP.net that has pretty straightforward functionality, although achieving it in ASP.net 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.
Thanks!
Mike
Timesheet-Prototype.pdf
ASKER
Volox,
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.
Mike
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.
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
-Mike
-Mike
Glad to hear I was able to help. Happy coding.
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...
job_id
entry_type - types would be 'in', 'out', 'lunch', 'dinner', 'total'
friday
sat
sun
monday
tues
wed
thurs
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.