none
Inserting Master-Detail records with Identity primary key using the designer RRS feed

  • Question

  • I am pretty new/rusty when it comes to Visual Studio and .NET, so please bear with me!

    I'm using the Visual Studio designer, dragging tables from a dataset onto a form so VS automatically creates the textboxes, labels, table adapters, binding sources, navigation, and datagridview, etc.

    I've also create a relation between two tables in the dataset designer, at the moment set to relation only. 

    My primary key for the Master table is an identity, and I am using a stored procedure for that table adapter's insert command. The stored procedure assigns the parameter for the table ID using Set @ID = Scope_Identity() and the parameter in the insert command's parameter collection is set to InputOutput. 

    My first question is: should I be able, just using the auto-generated code, to get the Master's primary key to cascade to its Details record when inserting both at the same time? At the moment, unless I add some code, I can't see how to get the new ID for the master copied to the details record?

    I have been able to do this by picking up the new ID after the masterTableAdapter_RowUpdated event, and assigning it to the corresponding foreigh key in detailsAdapter_RowUpdating.

    I ask because a master-detail with identity primary keys must be a pretty common situation, and becuase the designer-generated code seems to work fine for just about all other standard CRUD situations.

    My second question, if the coding intervention above is the way to do this, is this. The designer code allows the user to create a multiple master-detail records before they are committed to the database. So the user can click the '+' button, create the master-detail set, click it again to create a second set, and then hit the save button. 

    However, at the moment, all the master commits are made first, and then the details, so the details end up with the PK value from the last-entered record. What is the best way to resolve this? 

    Thanks! Adrian


    Tuesday, June 30, 2020 8:40 AM

Answers

  • Hello,

    First off, using TableAdapter method to work with data is rarely used these days as you have learned going past single table CRUD can be challenging along with if the underlying database tables change this means changing data bindings in existing forms. If one is not open to other ways of working with data by writing code with drag-drop TableAdapter method then one must fully embrace everything about them and work through things while at the same time realize about 1 percent of developers use drag-drop TableAdapter.

    The following code may or may not help, it was written about 15 years ago, done in VB.NET but the same logic holds true if using C#.

    Public Class Form1
        Private Sub Form1_Load(sender As System.Object, e As EventArgs) Handles MyBase.Load
            Try
                HandleRowUpdatedEvents()
                LoadData()
            Catch ex As Exception
                MessageBox.Show(ex.ToString(), "Error")
            End Try
        End Sub
        ''' <summary>
        ''' Handles the RowUpdated event of the parent adapter.
        ''' </summary>
        ''' <param name="sender">
        ''' The adapter that saved the row.
        ''' </param>
        ''' <param name="e">
        ''' The data for the event.
        ''' </param>
        ''' <remarks>
        ''' This event handler is used to retrieve an auto-generated ID 
        ''' from the database after a row is inserted and update the corresponding row 
        ''' in the local data set.
        ''' </remarks>
        Private Sub parentAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs)
            'We are only interested in new records.
            If e.StatementType = StatementType.Insert Then
                'Get the last ID auto-generated by the database.
                Dim lastAutoNumber = Me.parentAdapter.GetLastAutoNumber().Value
    
                'Update the ID of the local row.
                DirectCast(e.Row, ParentChildDataSet.ParentRow).ParentID = lastAutoNumber
            End If
        End Sub
        ''' <summary>
        ''' Handles the RowUpdated event of the child adapter.
        ''' </summary>
        ''' <param name="sender">
        ''' The adapter that saved the row.
        ''' </param>
        ''' <param name="e">
        ''' The data for the event.
        ''' </param>
        ''' <remarks>
        ''' This event handler is used to retrieve an auto-generated ID from the database 
        ''' after a row is inserted and update the corresponding row in the local data set.
        ''' </remarks>
        Private Sub childAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs)
            'We are only interested in new records.
            If e.StatementType = StatementType.Insert Then
                'Get the last ID auto-generated by the database.
                Dim lastAutoNumber = Me.childAdapter.GetLastAutoNumber().Value
    
                'Update the ID of the local row.
                DirectCast(e.Row, ParentChildDataSet.ChildRow).ChildID = lastAutoNumber
            End If
    
        End Sub
        ''' <summary>
        ''' Raised when the user clicks the Save button on the parent tool bar.
        ''' </summary>
        ''' <param name="sender">
        ''' The Save button.
        ''' </param>
        ''' <param name="e">
        ''' The data for the event.
        ''' </param>
        ''' <remarks>
        ''' Saves all parent and child data.
        ''' </remarks>
        Private Sub SaveToolStripButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveToolStripButton.Click
            If Me.Validate() Then
                Me.parentBindingSource.EndEdit()
                Me.childBindingSource.EndEdit()
                Me.adapterManager.UpdateAll(Me.dataSet)
            End If
        End Sub
        ''' <summary>
        ''' Attaches handlers to the RowUpdated events of the adapters.
        ''' </summary>
        Private Sub HandleRowUpdatedEvents()
            AddHandler Me.parentAdapter.Adapter.RowUpdated, AddressOf parentAdapter_RowUpdated
            AddHandler Me.childAdapter.Adapter.RowUpdated, AddressOf childAdapter_RowUpdated
        End Sub
        ''' <summary>
        ''' Retrieve the data from the database.
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub LoadData()
            Dim p As Integer = Me.parentAdapter.Fill(Me.dataSet.Parent)
            Me.childAdapter.Fill(Me.dataSet.Child)
        End Sub
    
        Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
            LoadData()
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, June 30, 2020 11:40 AM
    Moderator

All replies

  • Hello,

    First off, using TableAdapter method to work with data is rarely used these days as you have learned going past single table CRUD can be challenging along with if the underlying database tables change this means changing data bindings in existing forms. If one is not open to other ways of working with data by writing code with drag-drop TableAdapter method then one must fully embrace everything about them and work through things while at the same time realize about 1 percent of developers use drag-drop TableAdapter.

    The following code may or may not help, it was written about 15 years ago, done in VB.NET but the same logic holds true if using C#.

    Public Class Form1
        Private Sub Form1_Load(sender As System.Object, e As EventArgs) Handles MyBase.Load
            Try
                HandleRowUpdatedEvents()
                LoadData()
            Catch ex As Exception
                MessageBox.Show(ex.ToString(), "Error")
            End Try
        End Sub
        ''' <summary>
        ''' Handles the RowUpdated event of the parent adapter.
        ''' </summary>
        ''' <param name="sender">
        ''' The adapter that saved the row.
        ''' </param>
        ''' <param name="e">
        ''' The data for the event.
        ''' </param>
        ''' <remarks>
        ''' This event handler is used to retrieve an auto-generated ID 
        ''' from the database after a row is inserted and update the corresponding row 
        ''' in the local data set.
        ''' </remarks>
        Private Sub parentAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs)
            'We are only interested in new records.
            If e.StatementType = StatementType.Insert Then
                'Get the last ID auto-generated by the database.
                Dim lastAutoNumber = Me.parentAdapter.GetLastAutoNumber().Value
    
                'Update the ID of the local row.
                DirectCast(e.Row, ParentChildDataSet.ParentRow).ParentID = lastAutoNumber
            End If
        End Sub
        ''' <summary>
        ''' Handles the RowUpdated event of the child adapter.
        ''' </summary>
        ''' <param name="sender">
        ''' The adapter that saved the row.
        ''' </param>
        ''' <param name="e">
        ''' The data for the event.
        ''' </param>
        ''' <remarks>
        ''' This event handler is used to retrieve an auto-generated ID from the database 
        ''' after a row is inserted and update the corresponding row in the local data set.
        ''' </remarks>
        Private Sub childAdapter_RowUpdated(sender As Object, e As OleDbRowUpdatedEventArgs)
            'We are only interested in new records.
            If e.StatementType = StatementType.Insert Then
                'Get the last ID auto-generated by the database.
                Dim lastAutoNumber = Me.childAdapter.GetLastAutoNumber().Value
    
                'Update the ID of the local row.
                DirectCast(e.Row, ParentChildDataSet.ChildRow).ChildID = lastAutoNumber
            End If
    
        End Sub
        ''' <summary>
        ''' Raised when the user clicks the Save button on the parent tool bar.
        ''' </summary>
        ''' <param name="sender">
        ''' The Save button.
        ''' </param>
        ''' <param name="e">
        ''' The data for the event.
        ''' </param>
        ''' <remarks>
        ''' Saves all parent and child data.
        ''' </remarks>
        Private Sub SaveToolStripButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveToolStripButton.Click
            If Me.Validate() Then
                Me.parentBindingSource.EndEdit()
                Me.childBindingSource.EndEdit()
                Me.adapterManager.UpdateAll(Me.dataSet)
            End If
        End Sub
        ''' <summary>
        ''' Attaches handlers to the RowUpdated events of the adapters.
        ''' </summary>
        Private Sub HandleRowUpdatedEvents()
            AddHandler Me.parentAdapter.Adapter.RowUpdated, AddressOf parentAdapter_RowUpdated
            AddHandler Me.childAdapter.Adapter.RowUpdated, AddressOf childAdapter_RowUpdated
        End Sub
        ''' <summary>
        ''' Retrieve the data from the database.
        ''' </summary>
        ''' <remarks></remarks>
        Private Sub LoadData()
            Dim p As Integer = Me.parentAdapter.Fill(Me.dataSet.Parent)
            Me.childAdapter.Fill(Me.dataSet.Child)
        End Sub
    
        Private Sub ToolStripButton1_Click(sender As Object, e As EventArgs) Handles ToolStripButton1.Click
            LoadData()
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, June 30, 2020 11:40 AM
    Moderator
  • Hi Karen, thanks for the reply. It reinforced what I already suspected -- that the Windows Forms designers are ok as far as they go, but that they don't go far enough. Good to know that I was not simply missing something obvious.

    Tuesday, June 30, 2020 4:00 PM
  • Hi Karen, thanks for the reply. It reinforced what I already suspected -- that the Windows Forms designers are ok as far as they go, but that they don't go far enough. Good to know that I was not simply missing something obvious.

    Correct, you are not missing anything :-)

    Before last year I recommended using a DataSet with a BindingSource for the parent and child relations (and we can keep going down the rabbit hole as needed with master-detail/master-detail) while today my recommendation is to (for VB.NET developers) look at using Entity Framework 6 which is a lot more effort/code to produce a nice master-detail in windows forms.

    If interested I have several simple code samples for Entity Framework 

    https://github.com/karenpayneoregon/ef-track-added-modified-vb


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, June 30, 2020 4:44 PM
    Moderator