Adding many columns to a DataTable

[nicorvp]

Hi, I have built a DataGridView where rows are my resources (carpenters, electricians, welders, ...) and columns are days. Each cell contains the units of the resource avaiable for each day.
Users can choose the first and the last day to be shown in the DG.
For that I use a datatable binded to the DG, adding a column for each day.
I use the code below:

        Dim dt As New DataTable
        Dim firstDay As DateTime
        Dim lastDay As DateTime
        
        While firstDay <= lastDay
            Using col As New DataColumn(firstDay.ToLongDateString, GetType(Int32))
                dt.Columns.Add(col)
                firstDay = firstDay.AddDays(1)
            End Using
        End While

        For Each res As Resource In MyResourceList
            Dim row As DataRow = dt.NewRow
            For Each col As DataColumn In dt.Columns
                Dim day As DateTime = ... FindColumnDay(col)
                row(dt.Columns(col.ColumnName)) = ... res.GetAvailabilityAt(day)
            Next
            dt.Rows.Add(row)
        Next

No problems if the time period between firstDay and lastDay is about a week, but if it's more than a month, the While...End While takes long time to end

I see that

dt.Columns.Add(col)

is more and more slow at each cycle.

Is there a better way to do what I would do?

 

[cjard]

Odd that you'd have it that way round, to be honest. Columns are usually used for the finite resources, and rows for the infinite ones

Database tables extend vertically as more data is added, not sideways

It sounds more like you should be doing this work in a database, not a front end; do you have a database?

 

[nicorvp]

Thanks for your reply. Yes I have a database where I have a table with a ResourceId, a ResourceAvaibility and a ResourceAvaiableFrom (date) columns. It works like that:

Id ResId  ResAvaiability  ResAvaiableFrom
 1     1         3             1/7/2010
 2     1         5             5/8/2010
 3     1         9             7/10/2010
..................

Resouce 1 avaiability is 3 from 1/7 to 4/8, then is 5 from 5/8 to 6/10, then 9 from 7/10and forever ...
I would like to present data in the grid MSProject like, with resources in rows and dates in columns.

I could reverse columns with rows in the datatable I want to bind. But then is there a fast way to transpose it before binding? Or I have to renounce from using binding?

 

[cjard]

Are you putting it to a grid for edit, or display purposes?

 

[nicorvp]

Only for dislay purpose. I will modify values somewhere else in the program

 

[cjard]

You may wish to look into displaying the data in a report, then.. It sounds like something that a crosstab(pivot) table would be used for