locked
Insert multiple child records RRS feed

  • Question

  • I have a form which allows a user to create an asset in a database. The asset can be assigned to multiple users and to do this I've used a datatable in the session as a temp store to hold the assignment selection until the parent asset record is created. I then have the following code which creates the asset, loops through the datatable and creates the child records as required. My question is whether this code is efficient and the best way to do this? My main query is whether re-assigning the newAssignment variable as a new AssetUserAssignment entity each time is the best way. Also it seems long winded to save the changes multiple times to the database, however I can't think of a better way.

            Using db As New ASSETEntities
    
                Dim newAsset As New Asset
    
                With newAsset
                    .AssetNumber = txtAssetNumber.Text
                    .Make = txtMake.Text
                    .Model = txtModel.Text
                    .SerialNumber = txtSerialNo.Text
                    .AssetTypeID = drpAssetType.SelectedValue
                    .CompanySiteID = 1
                    .ImpactRatingID = 1
                End With
    
                db.AddToAssets(newAsset)
                db.SaveChanges()
    
                If Not Session("myDatatable") Is Nothing Then
    
                    Dim myDT As New DataTable
                    myDT = Session("myDatatable")
    
                    Dim newAssignment As New AssetUserAssignment
    
                    For Each dr As DataRow In myDT.Rows
    
                        With newAssignment
                            .AssetID = newAsset.AssetID
                            .AssignmentTypeID = dr("AssignmentTypeID")
                            .CreatedDate = DateTime.Now
                            .PersonID = dr("PersonID")
                        End With
    
                        db.AddToAssetUserAssignments(newAssignment)
                        db.SaveChanges()
                        newAssignment = New AssetUserAssignment
    
                    Next
    
                End If
    
            End Using
    

    Tuesday, March 16, 2010 12:20 PM

Answers

  • Hello,

    If I understand the problem correctly, the Asset amd AssetUserAssignment are between a one-to-many relationship.  If so, we can reduce the database calls by setting the entity navigation properties instead of directly setting the AssentID foreign key, and call the SaveChanges() at last.

        Using db As New ASSETEntities
                Dim newAsset As New Asset
                With newAsset
                   ...
                End With

                If Not Session("myDatatable") Is Nothing Then
                    Dim myDT As New DataTable
                    myDT = Session("myDatatable")

                    Dim newAssignment As New AssetUserAssignment
                    For Each dr As DataRow In myDT.Rows
                        With newAssignment
                            .Asset = newAsset
                            .AssignmentTypeID = dr("AssignmentTypeID")
                            .CreatedDate = DateTime.Now
                            .PersonID = dr("PersonID")
                        End With

                        newAssignment = New AssetUserAssignment
                    Next
                End If
                db.AddToAssets(newAsset)
                db.SaveChanges()
            End Using

     

    Or we can use the foreign key association, but all the AssetUserAssignment can be inserted by one SaveChanges():

             Using db As New ASSETEntities
                Dim newAsset As New Asset
                With newAsset
                   ...
                End With

                db.AddToAssets(newAsset)
                db.SaveChanges()

                If Not Session("myDatatable") Is Nothing Then
                    Dim myDT As New DataTable
                    myDT = Session("myDatatable")

                    Dim newAssignment As New AssetUserAssignment
                    For Each dr As DataRow In myDT.Rows
                        With newAssignment
                            .AssetID = newAsset.AssetID
                            .AssignmentTypeID = dr("AssignmentTypeID")
                            .CreatedDate = DateTime.Now
                            .PersonID = dr("PersonID")
                        End With

                        db.AddToAssetUserAssignments(newAssignment)
                        newAssignment = New AssetUserAssignment
                    Next
                End If


                db.SaveChanges()
            End Using

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Monday, March 22, 2010 4:17 PM