none
Bind A DataRow to Another DataRow in a Different Table RRS feed

  • Question

  • You can bind a control to a DataSource such as a DataRow, but can you bind a datarow column to another datarow column?  For example, I have a typed dataset that contains two different tables Parent and Child.  I want to 2-way bind the Name property of the parent row to the ParentName property of the child row.

                Dim parent As New DataTable("Parent")
                parent.Columns.Add("ParentID", GetType(Integer))
                parent.Columns.Add("Name", GetType(String))
    
                Dim child As New DataTable("Child")
                child.Columns.Add("ChildID", GetType(Integer))
                child.Columns.Add("ParentID", GetType(Integer))
                child.Columns.Add("ParentName", GetType(String))
    
                parent.Rows.Add({1, "John"})
                parent.Rows.Add({2, "Sam"})
                child.Rows.Add({1, 1, "John"})
                child.Rows.Add({2, 1, "John"})
                child.Rows.Add({3, 2, "Sam"})

    The only idea I have is to subscribe to the ColumnChanged event of each table and update the related datarow(s).  For example, if the Name property of a parent row is updated, I'd update the ParentName property of the related child rows.  If the ParentName of a child row is updated, I'd update Name of the parent row.

    Perhaps I could take it a step further and extend the parent and child row classes by inheritance and add a DataBindings collection property (just like controls) that would manage the ColumnChanged idea. 


    Ryan

    Thursday, August 9, 2018 3:10 PM

All replies

  • You should not have to (for what you are asking) update the Name property in the related child rows when the name changes in the parent row. You can get the name in the parent row from the child row via GetParentRow when a relationship is set between the two tables and from parent GetChildRows.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 9, 2018 3:27 PM
    Moderator
  • I'm aware of the GetParentRow method but that doesn't help me bind the datarow columns.  In my application I have two grid views, one is bound to the parent table and one is bound to the child table.  If the user updates the parent grid view I want those updates to reflect in the child grid view.  I was curious if there is a way to use binding, instead of writing code to manually create updates between the two tables.

    Ryan

    Thursday, August 9, 2018 4:23 PM
  • I'm aware of the GetParentRow method but that doesn't help me bind the datarow columns.  In my application I have two grid views, one is bound to the parent table and one is bound to the child table.  If the user updates the parent grid view I want those updates to reflect in the child grid view.  I was curious if there is a way to use binding, instead of writing code to manually create updates between the two tables.

    Ryan

    I my opinion there should be nothing to update when your database (or without a database) schema is done right so what you are asking does not make sense. Best I can say is to subscribe to datatable events.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, August 9, 2018 4:26 PM
    Moderator
  • This does not make sense to me, are you trying to have child column values = parent value outside of the ID columns in which they are related? My first thought is that you should be using a DataSet and creating relations and a bindingsource bound to the paent tables and a bindingsource bound to the relation. At this point you could set your child column values to that of the current parent bindingsource values. You may even be able to create a default value on the child table and use an expression to set the value to the parent value.

    Live as if you were going to die today, learn as if you were going to live forever -Mahatma Gandhi


    • Edited by Gtripodi Thursday, August 9, 2018 7:08 PM
    • Proposed as answer by Gtripodi Friday, August 10, 2018 12:37 PM
    Thursday, August 9, 2018 7:07 PM
  • I would agree that this is a symptom of a database design issue.  That ParentName field in the child table should be an expression field that pulls its value from the parent.  This can be useful when the child table is frequently accessed without a relation (join) to the parent table.  There is some overhead involved at the database level, but these are design decisions based on anticipated usage of the data.

    If redesigning the data tables is not an option, then adding the functionality at the data table level through its own events may be a reasonable solution.  If there are cases where you want this functionality and cases where you do not, then an inherited or secondary table definition should also work.

     

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, August 9, 2018 8:35 PM
    Moderator
  • See if this is what you want. 

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New DataOperations
            Dim ds As DataSet = ops.GetDataSet()
    
            ' ReSharper disable once VBUseFirstInstead
            Console.WriteLine(ds.Tables("Parent").AsEnumerable().FirstOrDefault().Field(Of String)("Name"))
            ' ReSharper disable once VBUseFirstInstead
            Console.WriteLine(ds.Tables("Child").AsEnumerable().FirstOrDefault().Field(Of String)("ParentName"))
    
            Console.WriteLine()
    
            ' ReSharper disable once VBUseFirstInstead
            ds.Tables("Parent").AsEnumerable().FirstOrDefault().SetField(Of String)("Name", "Karen")
    
            ' ReSharper disable once VBUseFirstInstead
            Console.WriteLine(ds.Tables("Parent").AsEnumerable().FirstOrDefault().Field(Of String)("Name"))
            ' ReSharper disable once VBUseFirstInstead
            Console.WriteLine(ds.Tables("Child").AsEnumerable().FirstOrDefault().Field(Of String)("ParentName"))
        End Sub
    End Class
    Public Class DataOperations
        Public Function GetDataSet() As DataSet
            Dim ds As New DataSet
    
            Dim parent As New DataTable("Parent")
            parent.Columns.Add("ParentID", GetType(Integer))
            parent.Columns.Add("Name", GetType(String))
    
            parent.Rows.Add({1, "John"})
            parent.Rows.Add({2, "Sam"})
    
            ds.Tables.Add(parent)
    
            Dim child As New DataTable("Child")
            child.Columns.Add("ChildID", GetType(Integer))
            child.Columns.Add("ParentID", GetType(Integer))
            child.Columns.Add(New DataColumn() With {.ColumnName = "ParentName", .DataType = GetType(String)})
    
            child.Rows.Add({1, 1, "John"})
            child.Rows.Add({2, 1, "John"})
            child.Rows.Add({3, 2, "Sam"})
    
            ds.Tables.Add(child)
    
            ds.Relations.Add(
                New DataRelation("ParentChild",
                                 ds.Tables("Parent").Columns("ParentID"),
                                 ds.Tables("Child").Columns("ParentID")
                                 )
                )
    
    
            child.Columns("ParentName").Expression = "Parent(ParentChild).Name"
    
    
            Return ds
    
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Gtripodi Friday, August 10, 2018 12:32 PM
    Thursday, August 9, 2018 10:57 PM
    Moderator