Question Help with TableAdapter Transactions

[GrantChl]

I am looking for some help and/or advice. My program is written in Visual Basic 2012, and the back-end database is an Access 2007 accdb file.

I have two tables, "Item" and "Item_Attributes". I need to create a record in table "Item" and then create child records in "Item_attribbutes", but I want to use Transactions so I can roll-back if there are any problems during the record creation process. I previously accomplished this by using SQL statements within my VB code.

I am trying to get away from having SQL statements within my code, separate my progaram into tiers, and use DataSets and TableAdapters in the Data Access Layer. I have created the DataSets and TableAdapters so that I can insert, update, and delete records from each table, and they just work fine for single-table operations. I am stuck on figuring out how to wrap those operations into a single transaction. I've read a lot about using "Partial Classes" for my TableAdapters, and I've read about TransactionScope, but I've yet to find a really good example of Visual Basic code that I can follow.

Can anyone provide some advice or point me to a good reference on how to do this? Am I taking the right approach in getting SQL out of my code, or am I just making extra work for myself?

Thanks much!!

My current code, which appears to work just fine, is shown below (edited to remove irrelevant content):

Private Function CreateItem() As Boolean
 Dim Conn As OleDbConnection
 Conn = New OleDbConnection(getConnectionString)
 Conn.Open()
 Dim cmd As OleDbCommand
 
' Create the transaction
 Dim trans As OleDbTransaction
 
' Start the transaction
 trans = Conn.BeginTransaction(IsolationLevel.ReadCommitted)
 Try
 ItemID = GetNextItemID()
 ' ....
 ' .... code to get data values from data entry form 
'...
 ' Create item record in table Item
 strSQL = "INSERT INTO Item " & _
 "(ItemID, ProductID, Description, DefaultPurchaseCost, InventoryCost, Inactive, ItemCatalogID) " & _
 "VALUES (?, ?, ?, ?, ?, ?, ?)"
 cmd = New OleDbCommand(strSQL, Conn, trans)
 cmd.Parameters.Add(New OleDbParameter("ItemID", ItemID))
 cmd.Parameters.Add(New OleDbParameter("ProductID", ProductID))
 cmd.Parameters.Add(New OleDbParameter("Description", Description))
 cmd.Parameters.Add(New OleDbParameter("DefaultPurchaseCost", DefaultPurchaseCost))
 cmd.Parameters.Add(New OleDbParameter("InventoryCost", DefaultPurchaseCost))
 cmd.Parameters.Add(New OleDbParameter("Inactive", Inactive))
 cmd.Parameters.Add(New OleDbParameter("ItemCatalogID", ItemCatalogID))
 cmd.ExecuteNonQuery()
 
' Create records in item_attributes table
 Dim AttrNum As Integer = 1
 strSQL = "INSERT INTO Item_Attribute " & _
 "(ItemID, AttributeNumber, AttributeValueID) " & _
 "VALUES (?, ?, ?)"
 cmd = New OleDbCommand(strSQL, Conn, trans)
 cmd.Parameters.Add(New OleDbParameter("ItemID", ItemID))
 cmd.Parameters.Add(New OleDbParameter("AttributeNumber", AttrNum))
 cmd.Parameters.Add(New OleDbParameter("AttributeValueID", Attr1))
 cmd.ExecuteNonQuery()
 ' Commit all changes
 trans.Commit()
 Return True
 
Catch ex As Exception
 MsgBox("Items not created", CType((MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation), MsgBoxStyle), "Program Error")
 trans.Rollback()
 Return False
 End Try
 Conn.Close()
 End Function

 

[jmcilhinney]

Using TransactionScope is about as easy as it gets:

Using tx As New TransactionScope
    Try
        'your data access code here

        tx.Complete()
    Catch ex As Exception
        'cleanup here
    End Try
End Using

The TransactionScope object created by the Using statement is disposed at the End Using statement. If Complete has been called then the transaction will be committed, otherwise it will be rolled back.

I'm not sure whether Access works with TransactionScope though. You should see what information you can find and test it out to see. If not, you have to use the same connection object with all your table adapters, because the transaction is associated with the connection. In that case, create one table adapter and get its Connection property value and assign that to the Connection property of all the other table adapters that you want involved in the same transaction. You then create and handle the transaction exactly as you already are. It's cumbersome and definitely a weakness of typed DataSets.

 

[GrantChl]

Thanks for the information, and I could use some additional advice. I tried what you suggested, and at first I got the following error anytime I tried to access one of the TableAdapter queries, so none of my code would execute:
"The ITransactionLocal Interface Is not support by the 'Microsoft.ACE.OLEDB.12.0' Provider"

I found several posts that indicate MS Access just does not support TransactionScope. One post recommended to add the following to my ConnectionString:
"OLE DB Services=-4"

When I made that change my code worked to insert all the new records as desired. However, the Rollback doesn't seem to work. For example, the record is created in the Item table, but then I force the insert into Item_Attribute to fail (I open the MS Access table in design view, which generates a OleDbException). My code block fails to the Catch statement, so the tx.Complete never executes. However, when I go into my database I see that the Item record is still created. Code is shown below, in case you can find anything seriously wrong with it.

You made a suggestion to get the Connection property of one TableAdapter and assign that to the Connection Property of my other adapters. Can you provide some guidance, or point me to a reference on how to do that? I've created all my DataSets and TableAdapters through the Designer, not through code. I think this requires creation of a Partial Class, but I've not found much clear VB guidance on how to do that. Many thanks!

Current code (edited for relvance):

Private Function CreateItems_Test() As Boolean
        ' Create records in Item table
        Dim NewItemID As Integer = 0
        Using tx As New TransactionScope
            Try
                ' 
                ' Retrieve values from data-entry form
                '
                ' Create the Item
                Dim myItem As New ItemClass
                'NewItemID = myItem.AddItem(myProduct.ProductID, Description, 0, PurchaseCost, False, "", ItemCatalogID)
                NewItemID = myItem.AddItem(myProduct.ProductID, "Description", 0, 11, False, "", "11512151213151231")
                If NewItemID > 0 Then
                    ' Item was created successfully
                    ' Create a record in Item_Attribute 
                    Dim Attr1 As Integer = 1
                    Dim myItemAttribute As New ItemAttributeClass
                    If myItemAttribute.AddItemAttribute(NewItemID, 1, Attr1) = 0 Then
                        Throw New System.Exception("There was a problem creating the Item Attribute record.")
                    End If
                End If
                tx.Complete()
                Return True
            Catch ex As System.Data.OleDb.OleDbException
                ErrorMessage("The database is currently locked.", "Database Error")
                Return False
            Catch ex As Exception
                MsgBox("Unknown error.  Items not created", CType((MsgBoxStyle.OkOnly + MsgBoxStyle.Exclamation), MsgBoxStyle), "Program Error")
                Return False
            End Try
        End Using
    End Function

 

[jmcilhinney]

First things first, I have edited your post again to make the code more readable. Please wrap your code snippets in appropriate formatting tags in future as unformatted code is hard to read, primarily, although not only, because of lack of indenting.

As for the question, you don't really need help to assign one property to another, do you?

adapter2.Connection = adapter1.Connection

The Connection property needs to be accessible to do that though, which I think that it's not by default. You can select a table adapter in the DataSet designer and then set the appropriate property (the name of which escapes me but should be obvious when you see it) to make the Connection property Public instead of Private.