[RESOLVED] Best way to bind complexly related data to DataGridView

[wolf99]

Hi folks

Im trying to figure out the best way of going about binding some data to a DataGridView. I can bind simple collections etc no problem but now i have to collections with relationships between them that affect what should be shown in the grid.

To explain from the start I have data represented in collections of different types of objects, Mappings and Command, populated by deserializing objects from JSON.

Public Class Mapping
    Public Property Description as String
    Public Property MaximumCommand As byte
    Public Property DefaultCommand As byte
    Public Property MinimumCommand As byte
    '...
End Class
 
Public class Command
    Public Property Code As Byte
    Public Property Value As Decimal
    '...
End Class
 
Dim Mappings as BindingList(Of Mapping)
Dim Commands as BindingList(Of Command)

The DataGridView has four columns "Description" (a DataGridViewComboBoxColumn), "Upper Value", "Default Value" and "Lower Value".

I can bind the DGV ComboBoxColumn to the Mappings such the the options in the drop-down are the description strings, but after that it gets complicated.

I'd like for the selected item on the Description column to be set by the Commands, where Command.Code is equal to Mapping.DefaultCommand

And also for the value columns to be equal to the Command.Value where the Command.Code is equal to the Mapping.*Command of the Mapping selected by the previous condition.


For an example:

Dim MyMapping As Mapping = New mapping
Dim Command1 As Command = New Command
Dim Command2 As Command = New Command
Dim Command3 As Command = New Command
 
Mapping.Description = "foobar"
Mapping.MaximumCommand = 50
Mapping.DefaultCommand = 63
Mapping.MinimumCommand = 70
 
Command1.Code = 50
Command1.Value = 100
 
Command2.Code = 63
Command2.Value = 50
 
Command3.Code = 70
Command3.Value = 0

The DGV should then show

 Description | Upper Value | Default Value | Lower Value |
-------------+-------------+---------------+-------------+
 foobar      | 100         | 50            | 0           |

I also have to be able to do the reverse with data edited or added in the DGV by the user such that changes affect the Commands (the Mappings are fixed). If I have to do that by reading each cell of the table and repopulating the lists it kind of takes away the usefulness of the binding :\

I was thinking maybe of making a new List() from a set of LINQ queries and binding to that but Im not entirely sure how to best do that?
Or even if there might be another better method?

Thanks!

[wolf99]

Maybe if I create a dataset with tables of each list and add some constraints between them to create a new ... something (DataTable? DataView? I dont really know datasets ) and then bind that to the DataGridView?

[wolf99]

So I have this down to forming a new List by running a Join query on the other two Lists. However Im not quite there just yet as it's a looooong time since I've used LINQ or even SQL and I've forgotten how to use most of it

Currently I have added a new class

Private Class MappingsAndCommands
    Public Property c As Command
    Public Property m As Mapping
End Class

And ran the Following query on the Mappings and Commands Lists:

Dim NewList As List(Of MappingsAndCommands) = (From m In Mappings _
                                            Join c In Commands _
                                            On m.DefaultCommand Equals c.Code _
                                            Select New ParametersAndCommands With {.c = c, .m = m}).ToList

Which gets me a list of Commands that have a .Code that matches a Mapping.Defaultcommand value

I have yet to find a succinct manner to also get Commands for each of those Mapping's .MinimumCommand and MaximumCommand. Maybe I just need to add another LINQ query for each of those and then add the results to this new list??

Is it possible to do all that in a single query?

[wolf99]

so I've ended up with:

Private Class ParametersAndCommands
    Public Property Description As String
    Public Property Unit As CommandData.ParameterUnit
    Public Property MinimumCommand As Byte
    Public Property MinimumValue As Decimal
    Public Property MinimumAccess As Command.AccessLevel
    Public Property DefaultCommand As Byte
    Public Property DefaultValue As Decimal
    Public Property DefaultAccess As Command.AccessLevel
    Public Property MaximumCommand As Byte
    Public Property MaximumValue As Byte
    Public Property MaximumAccess As Command.AccessLevel
End Class
 
Dim PacQuery = From p In WorkingProduct.Parameters _
            Join c In WorkingProfile.Commands _
            On p.DefaultCommand Equals c.Code _
            Select New ParametersAndCommands With _
                   {.Description = p.Description, .Unit = p.Unit, _
                    .MinimumCommand = p.MinimumCommand, _
                    .DefaultCommand = p.DefaultCommand, _
                    .DefaultValue = c.Value, _
                    .DefaultAccess = c.Access, _
                    .MaximumCommand = p.MaximumCommand}
Dim Pacs As BindingList(Of ParametersAndCommands) = New BindingList(Of ParametersAndCommands)(PacQuery.ToList)
 
For Each pac As ParametersAndCommands In Pacs
    Dim q As ParametersAndCommands = pac
    Dim MaxCommand As Command
    Dim MinCommand As Command
 
    If q.MinimumCommand <> 0 Then
        MinCommand = (From c In WorkingProfile.Commands _
                                Where c.Code = q.MinimumCommand _
                                Select c).First
        If MinCommand IsNot Nothing Then
            pac.MinimumValue = MinCommand.Value
            pac.MinimumAccess = MinCommand.Access
        End If
    End If
 
    If q.MaximumCommand <> 0 Then
        MaxCommand = (From c In WorkingProfile.Commands _
                                Where c.Code = q.MaximumCommand _
                                Select c).First
        If MaxCommand IsNot Nothing Then
            pac.MaximumValue = MaxCommand.Value
            pac.MaximumAccess = MaxCommand.Access
        End If
    End If
Next

Surely there must be a better way??

[wolf99]

Gah, that doesnt protect the many to one relationship of the min/max commands to the mappings >

[wolf99]

Drat. Ill just go back and rejig my data model to be simpler and forget about the min/max command codes as a complex object. Much simpler and puts the problem off down the road which means the boss gets a deliverable sooner.