While you can do simple transaction support with ADO.NET, Visual Basic includes a set of classes specifically designed for working with transactions: the System.Transactions namespace. As the name implies, these classes allow you to define and work with transactions in your code.
You may well be wondering at this point why you need two methods of working with transactions. The classes of System.Transaction, particularly the Transaction class itself, abstract the code from the resource managers participating in the transaction. Transactions in ADO.NET are specific to each database you may access. There is no unified method of creating a transaction, nor is there a standard way of sharing a database transaction across multiple databases or other transaction supporters. The Transaction class provides for these limitations, and can coordinate multiple resource managers itself.
The classes of System.Transaction also provide the means to create your own resource managers. These resource managers may then participate in transactions. At first, you may balk at this prospect, wondering how you could write something that manages all the details of a transactional data store. Aren't the details enormous? Fortunately, the classes make it easy to enlist in a transaction and report on your results.
System.Transaction supports two means of working with transactions: implicit and explicit. With implicit transactions, you define a boundary for the transaction. Any resource managers you use within this boundary become part of the transaction. That is, if you have defined a boundary and then call a database such as SQL Server, the actions performed on the database are part of the transaction. If the code reaches the boundary without incident, then the transaction is committed. If an exception occurs during this implicit transaction, then the transaction is rolled back. Explicit transactions, as you may have guessed, mean that you explicitly commit or roll back the transaction as needed.
Using the implicit model can greatly simplify the code involved in a transaction. The following code demonstrates inserting a record using the insert command seen previously using an implicit transaction (code file: DataAdapterAdvancedExample):
Using ts As New TransactionScope Try 'Add a new document to the DataTable. Dim row As DataRow = table.NewRow() row("Title") = "Test Document " + New Random().Next(100, 999).ToString row("FileName") = My.Computer.FileSystem.CombinePath( Environment.GetFolderPath( Environment.SpecialFolder.MyDocuments), "TestFile" + New Random().Next(10000, 99999).ToString) row("FileExtension") = ".docx" row("Revision") = New Random().Next(0, 999).ToString row("ChangeNumber") = New Random().Next(1, 999).ToString row("Status") = 1 row("ModifiedDate") = DateTime.Now.ToString table.Rows.Add(row) 'Send only the changes in the DataTable to the database for updating. adapter.Update(table.GetChanges()) 'Complete the transaction ts.Complete() Catch ex As Exception MessageBox.Show(ex.Message) Finally connection.Close() End Try End Using
The Using clause wraps the inserts within an implicit transaction. All resource managers that recognize transactions participate in this transaction. The Using clause guarantees that the TransactionScope object is disposed of when the transaction is complete. The TransactionScope Complete method completes the transaction and saves the row to the database.
Using explicit transactions requires a bit more code but provides greater control over the transaction. You can use either the Transaction class or the CommittableTransaction class to wrap transactions in this model. CommittableTransaction is a child class of Transaction, and adds the capability to commit a transaction, as the name implies.
Using a CommittableTransaction in the above scenario changes it as follows:
Using ct As New CommittableTransaction Try 'Add a new document to the DataTable. Dim row As DataRow = table.NewRow() row("Title") = "Test Document " + New Random().Next(100, 999).ToString row("FileName") = My.Computer.FileSystem.CombinePath( Environment.GetFolderPath( Environment.SpecialFolder.MyDocuments), "TestFile" + New Random().Next(10000, 99999).ToString) row("FileExtension") = ".docx" row("Revision") = New Random().Next(0, 999).ToString row("ChangeNumber") = New Random().Next(1, 999).ToString row("Status") = 1 row("ModifiedDate") = DateTime.Now.ToString table.Rows.Add(row) 'Send only the changes in the DataTable to the database for updating. adapter.Update(table.GetChanges()) 'Complete the transaction ct.Commit() Catch ex As Exception ct.Rollback() MessageBox.Show(ex.Message, "Error - Data Rolled Back") Finally connection.Close() End Try End Using
Notice that the transaction must now be explicitly committed or rolled back. You could also pass the transaction variable to other methods to vote on the transaction. If you do this, you can enlist other transaction containers using the EnlistTransaction method (or EnlistDistributedTransaction if the transaction will span multiple computers). Once it is a part of the transaction, it can then use the transaction methods to commit or roll back each part of the transaction.
Using the TransactionScope and Transaction classes can greatly decrease the amount of effort involved in creating and working with transactions in your applications. Generally, using implicit transactions using TransactionScope is easier and less error prone, and should be your first choice.
In addition to using the classes in System.Transactions for managing transactions, you can also use them to define your own resource managers. These resource managers can then participate in transactions with databases, MSDTC, message queues, and more. There are three basic steps to defining a resource manager:
Why would you define your own resource managers, rather than simply use an existing one such as SQL Server? You might need to store data in another database that does not directly participate in transactions. Alternately, you may want to enable a normally nontransactional component with transactional behavior. For example, the cache in ASP.NET doesn't support the addition of items using transactions. You could create a resource manager that wraps the ASP.NET cache and adds support for commit and rollback of entries. This might be part of a system in which you want to use the cache as an in-memory data store. While this would work without the transactions, adding transactional support would ensure that if the database write fails for any reason, then the entry could be rolled back out of the cache.