Creating Transactions

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 multiple records using implicit transactions:

Private Sub MultipleInsertImplicit()

'insert a number of records into the sales table 'using implicit transactions

Dim cmdString As String = "INSERT INTO Sales(stor_id, ord_num, " & "ord_date, qty, payterms, title_id) " & "VALUES (@storeID, @ordNum, @ordDate, " & "@qty, @payterms, @titleID)" Dim cmd As New SqlCommand(cmdString, connection) 'add the parameters to the command. We'll set the values later With cmd.Parameters

.Add("@storeID", SqlDbType.Char, 4) .Add("@ordNum", SqlDbType.VarChar, 20) .Add("@ordDate", SqlDbType.DateTime) .Add("@qty", SqlDbType.Int) .Add("@payterms", SqlDbType.VarChar, 12) .Add("@titleID", SqlDbType.VarChar, 6) End With

'start implicit transaction Using txn As New TransactionScope Try

'insert 10 random records For i As Integer = 1 To 10

cmd.Parameters("@storeID").Value = PickRandomStore() cmd.Parameters("@ordNum").Value = PickRandomOrderNumber() cmd.Parameters("@ordDate").Value = (DateTime.Now) cmd.Parameters("@qty").Value = (New Random().Next(1, 100)) cmd.Parameters("@payterms").Value = ("NET 30") cmd.Parameters("@titleID").Value = (PickRandomTitle())

cmd.ExecuteNonQuery()

Next

Catch ex As Exception

Console.WriteLine(ex.Message) End Try

End Using

'if no exceptions occur, transaction will commit here

Code snippet from SubSimpleTransactions

This code inserts multiple sales orders into the sales table. The bulk of the code is setting up the query to insert the record, and creating random parameters to insert. 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. If something happens, the transaction is automatically rolled back, otherwise it is committed.

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:

Private Sub MultipleInsertExplicit()

'insert a number of records into the sales table 'using explicit transactions

Dim cmdString As String = "INSERT INTO Sales(stor_id, ord_num, " & "ord_date, qty, payterms, title_id) " & "VALUES (@storeID, @ordNum, @ordDate, " & "@qty, @payterms, @titleID)"

Dim cmd As New SqlCommand(cmdString, connection) 'add the parameters to the command. We'll set the values later With cmd.Parameters

.Add("@storeID", SqlDbType.Char, 4) .Add("@ordNum", SqlDbType.VarChar, 20) .Add("@ordDate", SqlDbType.DateTime) .Add("@qty", SqlDbType.Int) .Add("@payterms", SqlDbType.VarChar, 12) .Add("@titleID", SqlDbType.VarChar, 6) End With

'start implicit transaction Using txn As New CommittableTransaction Try

'insert 10 random records For i As Integer = 1 To 10

cmd.Parameters("@storeID").Value = PickRandomStore() cmd.Parameters("@ordNum").Value = PickRandomOrderNumber() cmd.Parameters("@ordDate").Value = (DateTime.Now) cmd.Parameters("@qty").Value = (New Random().Next(1, 100)) cmd.Parameters("@payterms").Value = ("NET 30") cmd.Parameters("@titleID").Value = (PickRandomTitle())

cmd.ExecuteNonQuery()

Next

'commit the transaction txn.Commit() Catch ex As Exception

'if an exception occurs, we rollback the attempt 'this could also have been done elsewhere txn.Rollback()

Console.WriteLine(ex.Message) End Try

End Using

Code snippet from SubSimpleTransactions

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.

0 0

Post a comment