[2.0] Binding and Updating a DataGridView to a dynamic column DataSet
I have two tables that include data that I need to display and update in a DataGridView bound to a strongly typed DataSet. The first three columns of the DataSet are fixed but the next columns need to be variable depending on a date range. The columns will start with the month of the beginning date and increase in months until the end date.
Here's a rough example of the data:
Code:
table - Schedule
idSchedule startDate endDate
0 9/1/2007 11/31/2007
1 12/1/2007 3/31/2008
table - ScheduleDates
idScheduleDate idSchedule Date isChecked
0 0 9/1/2007 1
1 0 10/1/2007 1
2 0 11/1/2007 0
3 1 12/1/2007 1
4 1 1/1/2008 1
5 1 2/1/2008 1
6 1 3/1/2008 0
Calculated Grid Date Range is 9/1/2007 to 3/31/2008
Data to display in the grid --
The x represents a CheckBox checked, the blank is no check, and the -- means the field is disabled.
idSchedule startDate endDate Sep07 Oct07 Nov07 Dec07 Jan08 Feb08 Mar08
0 9/1/2007 11/31/2007 X X -- -- -- --
1 12/1/2007 3/31/2007 -- -- -- X X X
Notice the ScheduleDates table has exactly as many rows for each idSchedule as there are months between and including the startDate and endDate.
The DataSet of course doesn't include the columns for the months until I add them. The problem then is that isn't really the same DataSet anymore so Updates and Deletes to the DataSet won't be capture properly.
I'm going to have to write a lot of convoluted code to scan the grid and update the two DataSet tables manually. I also don't have a clear idea of how I could tie each month column to the [Date] row value in the ScheduleDates table.
Is there any better way to do this?