.Net Winforms DataGridView DataBinding

Question

I support a winforms application that is used by a financial planning team. One of the main screens of the program, referred to as the Planning Grid, uses a datagridview to show changes in the financial plan, week over week, for product groups that the company sells.

The datagridview displays financial measures, by week, for selected product groups. There is one column for each week of the year (total of 52 columns). There are 15 financial measures per product group, so performing a product group selection in the UI adds 15 sequential rows of data to the datagridview.

Currently, the program gets the data back from the business layer in a 2 dimensional array. It then builds the rows programatically by looping through the array and using each row of the array to create a row in the data grid.

My question relates to data binding. I would like to refactor the code to use data binding to automatically build the data grid. Essentially, I want to return a list of business objects (PlannedProductGroup) and set that list as the data source for the datagridview. My problem however, is that I don't know how to handle the binding. When I have done datagridview binding in the past each object in the data source equates to one row in the grid. This time however, one object would be creating 15 rows in the grid, and I am not sure if this is possible using data binding.

Is what I want to do possible? If so, does anyone know how to implement this more complex data binding scenario?

Edit - Adding Snippets of current implementation that I want to refactor

        'create rows
        Dim _planningDetailsArray(,) As Single = Load_Planning_Details()
        For Each productGroup As ProductGroup In _productGroups
            If Product_Group_Checked(productGroup.ID) Then

                'Measure.ProductGroupId
                productGroupId = Convert.ToInt32(_planningDetailsArray((productGroupCounter * Measure.Count) + Measure.ProductGroupId, 0))
                productGroupName = _productGroups.Find(Function(p) p.ID = productGroupId).Name
                myDataGridViewRow = New DataGridViewRow
                myDataGridViewRow.Cells.Add(NewDataGridViewIntegerCell(productGroupId))
                For weekCounter As Integer = 0 To 51
                    Dim priceMultipleId As Integer = CInt(_planningDetailsArray((productGroupCounter * Measure.Count) + Measure.PriceMultiple, weekCounter))
                    Dim priceMultipleName As String = _priceMultiples.Find(Function(p) p.ID = priceMultipleId).Name
                    Dim complexOffer = String.Empty
                    If CInt(_planningDetailsArray((productGroupCounter * Measure.Count) + Measure.Complex, weekCounter)) = 1 Then
                        complexOffer = " *"
                    End If
                    Dim retailPrice As String = Math.Abs(_planningDetailsArray((productGroupCounter * Measure.Count) + Measure.RetailPrice, weekCounter)).ToString("C2") & complexOffer
                    Dim cellText = $"{priceMultipleName} {retailPrice}"
                    _myDataGridViewCell = NewDataGridViewTextCell(cellText)
                    If _blackoutCollection.Contains((weekCounter + 1).ToString) Then
                        _myDataGridViewCell.Style.BackColor = Color.LightSlateGray
                    Else
                        _myDataGridViewCell.Style.BackColor = Color.LightGray
                    End If
                    myDataGridViewRow.Cells.Add(_myDataGridViewCell)
                Next
                myDataGridViewRow.Cells.Add(NewDataGridViewIntegerCell(Measure.ProductGroupId))
                myDataGridViewRow.HeaderCell.Value = productGroupName
                myDataGridViewRow.ReadOnly = True
                _dgvPlanningDetails.Rows.Add(myDataGridViewRow)

                'Measure.Retail_Price
                myDataGridViewRow = New DataGridViewRow
                myDataGridViewRow.Cells.Add(NewDataGridViewIntegerCell(productGroupId))
                For weekCounter As Integer = 0 To 51
                    myDataGridViewRow.Cells.Add(NewDataGridViewSingleCellDash(_planningDetailsArray((productGroupCounter * Measure.Count) + Measure.RetailPrice, weekCounter), False, True))
                Next
                myDataGridViewRow.Cells.Add(NewDataGridViewIntegerCell(Measure.RetailPrice))
                myDataGridViewRow.HeaderCell.Value = "   Retail Price"
                myDataGridViewRow.Visible = ToolStripCheckBox_Retail.Checked
                myDataGridViewRow.ReadOnly = _isReadOnly
                myDataGridViewRow.DefaultCellStyle.Format = "C2"
                _dgvPlanningDetails.Rows.Add(myDataGridViewRow)

                'Measure.Price_Multiple_ID
                myDataGridViewRow = New DataGridViewRow
                myDataGridViewRow.Cells.Add(NewDataGridViewIntegerCell(productGroupId))
                For weekCounter As Integer = 0 To 51
                    myDataGridViewRow.Cells.Add(NewDataGridViewComboBoxCell(_priceMultiples.AsEnumerable(), Convert.ToInt32(_planningDetailsArray((productGroupCounter * Measure.Count) + Measure.PriceMultiple, weekCounter))))
                Next
                myDataGridViewRow.Cells.Add(NewDataGridViewIntegerCell(Measure.PriceMultiple))
                myDataGridViewRow.HeaderCell.Value = "   Price Multiple"
                myDataGridViewRow.Visible = ToolStripCheckBox_Retail.Checked
                myDataGridViewRow.ReadOnly = _isReadOnly
                _dgvPlanningDetails.Rows.Add(myDataGridViewRow)            
            End If
        Next

Edit 2 - Adding POC code I created a quick Proof of Concept project to show what I want to do. Each person object should be two rows in the data grid, one row for the FavoriteColors list and one row for the FavoriteFoods list.

Public Class Form1

Public Sub New ()

    ' This call is required by the designer.
    InitializeComponent()

    ' Add any initialization after the InitializeComponent() call.

End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim people = New List(Of Person)

    Dim person1 = New Person
    person1.Name = "Jim"
    person1.FavoriteColors = New List(Of String)(New String() {"Red", "Green", "Blue"})
    person1.FavoriteFoods = New List(Of String)(New String() {"Pizza", "Salad", "Burger"})

    Dim person2 = New Person
    person2.Name = "Bob"
    person2.FavoriteColors = New List(Of String)(New String() {"Yellow", "Black", "Pink"})
    person2.FavoriteFoods = New List(Of String)(New String() {"Hotdog", "French Fries", "Steak"})

    people.Add(person1)
    people.Add(person2)

    DataGridView1.DataSource = people
End Sub

Private Class Person
    Public Property Name As String
    Public Property FavoriteColors As List(Of String)
    Public Property FavoriteFoods As List(Of  String)
End Class

End Class

Answer 1

It is difficult to picture what you describe. So, I will keep it simple and focus on the Person example.

Using a List<T> as a DataSource to a DataGridView will work. However, it will only display “publicly” exposed “Properties” that are NOT COLLECTIONS.

In the Person class example, the grid will only display the "name" column. And, this makes sense… the grid is not going to know how to make a single “Cells” value equal to a collection of values.

Therefore, if you want to display each item in the list as a “column” in the grid…. Then you will need to do this. I am not sure if binding or some other mechanism would help, however, I am confident it would not be difficult to create a method that given a List<T> would return a DataTable that is set up as you describe.

Given that there are TWO (2) list for each Person (I assume 15 in the original example), this implies there would be two rows for each Person. If this is correct, then about the only thing you would need to worry about is… HOW MANY COLUMNS are you going to need given a List of Person such that, each person “could” have a different number of items (Colors, Foods) in one of the lists.

It appears obvious that the number of columns you will need will be the items count from the largest (Colors, Foods) List of ALL Persons in the people list. I am guessing this is always going to be 52 in the original case, however, it would be prudent to check this as a crash is guaranteed otherwise.

To help, a method is needed to get the number of columns for the DataTable. This GetMaxColumns method takes a List<Person> and returns the count from the largest “Color” and “Food” lists. This will guarantee that we will stay inbounds regardless of the size of any color or food list. It may look like below…

Private Function GetMaxColumns(people As List(Of Person)) As Int32
    Dim max = 0
    For Each person In people
        If (person.FavoriteColors.Count > max) Then
            max = person.FavoriteColors.Count
        End If
        If (person.FavoriteFoods.Count > max) Then
            max = person.FavoriteFoods.Count
        End If
    Next
    Return max
End Function

Next a method GetDataTable takes a List<Person> and returns a DataTable with the proper number of columns for the given List<Person>. It is here where naming the columns may be convenient.

Private Function GetDataTable(people As List(Of Person)) As DataTable
    Dim dt = New DataTable()
    Dim maxColumns = GetMaxColumns(people)
    For index = 0 To maxColumns
        dt.Columns.Add()
    Next
    Return dt
End Function

A FillDataTable method takes a List<Person> and a DataTable then fills the DataTable from the List<Person> as previously described. There will be two rows for each person, however, the second row under the name column will be empty since it will be the same name as the previous row.

In the code below, a loop begins through each Person in the list. The name is added to the row, then a loop through the “Color” list to add each color value. Then a second row is added which contains the values in the “Food” list. The name is not added to the second row.

Private Sub FillDataTable(people As List(Of Person), dt As DataTable)
    Dim dr As DataRow
    Dim curCol = 0
    For Each person In people
        dr = dt.NewRow()
        curCol = 0
        dr(curCol) = person.Name.ToString()
        curCol += 1
        For Each favColor In person.FavoriteColors
            dr(curCol) = favColor
            curCol += 1
        Next
        dt.Rows.Add(dr)
        dr = dt.NewRow()
        curCol = 1
        For Each favFood In person.FavoriteFoods
            dr(curCol) = favFood
            curCol += 1
        Next
        dt.Rows.Add(dr)
    Next
End Sub

Lastly, putting all this together may look something like below….

 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim people = New List(Of Person)
    Dim person = New Person
    person.Name = "Jim"
    person.FavoriteColors = New List(Of String)(New String() {"Red", "Green", "Blue"})
    person.FavoriteFoods = New List(Of String)(New String() {"Pizza", "Salad", "Burger"})
    people.Add(person)

    person = New Person
    person.Name = "Bob"
    person.FavoriteColors = New List(Of String)(New String() {"Yellow", "Black", "Pink"})
    person.FavoriteFoods = New List(Of String)(New String() {"Hotdog", "French Fries", "Steak"})
    people.Add(person)

    person = New Person
    person.Name = "John"
    person.FavoriteColors = New List(Of String)(New String() {"Purple", "Olive Grey", "Polka Dot"})
    person.FavoriteFoods = New List(Of String)(New String() {"Ice Cream", "Fish", "Crutons"})
    people.Add(person)

    Dim GridTable = GetDataTable(people)
    FillDataTable(people, GridTable)
    DataGridView1.DataSource = GridTable
End Sub

Hope this helps.

Answer 2

This depends on the structure of your data in the database. For example, is the relation data stored in 52 columns, one for each week? Is a query doing a PIVOT on that data to generate the 52 columns? Is there one query populating the existing 2D array? Multiple queries?

Regardless, if you can refactor the code and put the query results in a DataTable, then you just bind that DataTable to the DataGridView as such:

myDataGridView.DataSource = myDataTable

Just make sure the DataGridView's AutoGenerateColumns property is set to True. And that's all there really is to it, to bind the data. However, you have to realize that simple binding such as this will display ALL fields in the DataTable. You'd have to manually set columns you don't want displayed to Visible = False in your code.

Conversely, you could create a Data Transfer Object (DTO), which is essentially just a class with only Properties. Then you'd instantiate one DTO object for each row in the query results, and add all those DTOs to a List(of T). Then, bind that List(Of T) to the DataGridView:

Dim myResults As List(Of PlanningDetailDTO) = myFunction.ReadTheData()
myDataGridView.DataSource = myResults

But I wouldn't really want to create a DTO class with 52+ fields in it. The advantage of the DataTable method is that it will create columns in the DataTable based on the query results.