Topic: Best Practice advice on multi table inserts

Fri, 26 Sep 2008 19:35:02 GMT

Topic Description: We are writing an "assignment" creation form and are wondering the best way to do all the inserts into the tables we store the data in.

we are setting UseTransaction = true on each object we use.

Q1) what is the proper way to do all these inserts able be able to roll back ALL of them if ONE fails on insert? are these all one transaction? is there a way to have one transaction we have to commit/rollback?

My co worker had at first had the first table doing the .Insert command with UseTransaction set true, and then going through and setting the other objects up for the inserts and doing each one's .Insert and then after the final one inserted doing all the .CommitTransaction calls or .RollbackTransaction call. He was getting a SQL timeout when it went to do the first CommitTransaction call. So I went under the premise that since the transaction was started back when the first .Insert was done and he did much processing since then we should move all the .Inserts to after setting up all the objects.

Q2) Is this right thinking on the process? Should we set up all the objects and then do all the .Insert calls and then commit and rollback as needed?

we have one main assignment table which the GUID is used for the other table inserts, and then a Parties table which the GUID id is used for some other tables containing contact info for the parties.

Thanks,

Mike

Replies: 2
Last Reply: Fri, 26 Sep 2008 19:35:02 GMT
Reply By: Ish Singh

Reply: Re: Best Practice advice on multi table inserts

Fri, 26 Sep 2008 19:35:02 GMT

I noticed I made a small mistake in the sample code above. I put the Customer for loop to start from 21 and go till 25 and I meant to do 0 to 20. Just thought will mention that since my comments say that we'll be inserting 20 customers.

Also, I am posting the code that shows how to use the same connection and transaction for unrelated objects.

Using customer As Customers = New Customers() 
customer.UseTransaction = True  ' Set UseTransaction before sharing the connection and this will ensure that both the connection and transaction is shared.
 
' Lets create an instance of a business object that is not directly related with customer. 
 
Dim product As Products = New Products() 
 
' Now you can call the AssignConnection method on customer and pass another business object instance that will receive not just the connection but also the transaction. 
customer.AssignConnection(product) 
 
' Now do the inserts/updates/deletes on either of the objects and you can call Commit and Rollback on either and it will affect the data from both the objects. 
 
End Using 

Reply: Re: Best Practice advice on multi table inserts

Fri, 26 Sep 2008 18:34:47 GMT

Hi Mike,

Answers:

Q1) Objects allow you to share the same connection and transaction. However if you require two or multiple seperate transaction that is possible as well but I think your intent is to use the same transaction. I am posting some sample code that inserts a Customer then inserts multiple Orders and OrderItems. Hopefully that will show you how this can be used.

I haven't seen the actual code so can't really comment on the SQL Timeout so if you can post your code or email me I can take a look.

Q2) I think we didn't a good job in explaining the transaction and connection sharing options very well in our samples and documentation. But I'll try to explain the process below in short:

Option 1: You can use multiple related objects to share the connection and use the same transaction. The requirement is that you set the UseTransaction property of the business object to true on the first object before you call any of the join methods. This automatically ensures that rest of the related objects (no matter how many levels deep you go) share the same connection and transaction.

Option 2: You can use multiple unrelated objects and still share the same connection and use the same transaction as well. I will post the code for Option 2 seperately. For now I will show the code for Option 1.

The following code inserts 20 customers and for each customer it inserts multiple orders and order items. And at the end we call commit (or as a test I put the code for rollback as well).

        Using customer As Customers = New Customers() 
            ' If we want multiple objects to use the same transaction we need to set it the first thing. 
            customer.UseTransaction = True 
 
            ' If we set the ObjectMode to save, any joins we create will automatically receive the ObjectMode as well. 
            customer.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Save 
 
            ' Now the following call will make sure that the "Orders" object also is using the same connection and transaction objects. 
            customer.Join_CustomerID_Orders_Child() 
            ' The following call will also share the same connection and transaction with "OrderITems" object as well. 
            customer.CustomerID_Orders_Child.Join_OrderID_OrderItems_Child() 
 
            ' Now just as an example we are going to use nested loops and we'll insert 20 customers 
            ' For each customer we'll insert 50 orders and for each order we'll insert 100 order items. 
            With customer 
                For customerCounter As Integer = 21 To 25 
                    ' Assign values to the customer's fields 
                    .FirstName.Value = "C" + customerCounter.ToString() 
                    .LastName.Value = "C" + customerCounter.ToString() 
                    If .Insert Then 
                        With .CustomerID_Orders_Child 
                            For orderCount As Integer = 0 To 50 
                                ' Assign values to the order's fields 
                                .CustomerID.Value = customer.CustomerID.Value 
                                .OrderAmount.Value = orderCount * 100 
                                .OrderDate.Value = DateTime.UtcNow.AddDays((orderCount * -1)) 
                                .OrderProcessed.Value = False 
                                If .Insert Then 
                                    With .OrderID_OrderItems_Child 
                                        For orderItemCount As Integer = 0 To 100 
                                            .OrderID.Value = customer.CustomerID_Orders_Child.OrderID.Value 
                                            .Quantity.Value = orderItemCount 
                                            .ItemAmount.Value = orderCount * orderItemCount 
                                            .ProductID.Value = 1 
                                            If Not .Insert Then 
                                                .RollBackTransaction() 
                                                MessageBox.Show("Ooops the order was not inserted. Error: " + .ErrorString) 
                                                Stop 
                                            End If 
                                        Next 
                                    End With 
                                Else 
                                    .RollBackTransaction() 
                                    MessageBox.Show("Ooops the order was not inserted. Error: " + .ErrorString) 
                                    Stop 
                                End If 
                            Next 
                        End With 
                    Else 
                        .RollBackTransaction() 
                        MessageBox.Show("Ooops the customer was not inserted. Error: " + customer.ErrorString) 
                        Stop 
                    End If 
                Next 
            End With 
            ' If we reached so far that indicates that there were no errors and we can commit the transaction 
            ' You can use any other way.. perhaps keep a Boolean variable that tracks the success of all the inserts 
            ' And if nothing failed only then call commit else call the rollback 
            If Not customer.DBTransaction Is Nothing Then 
                MessageBox.Show("Everything looks good time to commit") 
                ' You can call the Commit or Rollback on any of the joined objects as long as you used the UseTransaction before creating joins 
                ' This ensures that all the objects used the same transaction and connection and hence when you commit or rollback on any of the objects it has the same affect for every object. 
                customer.CommitTransaction() 
 
                ' Or just to see if Rollback works you can uncomment the line below and comment out the commit 
                'customer.RollBackTransaction() 
            End If 
 
        End Using 
 

Basically you can call CommitTransaction on RollBackTransaction on any of the related objects and everything will commit or rollback together.

Hope this helps, but if you have any further questions or not clear feel free to let me know.

Thanks,
Ish

Akal Tech RSS Feed

Topic: Best Practice advice on multi table inserts

Reply: Re: Best Practice advice on multi table inserts

Reply: Re: Best Practice advice on multi table inserts