How to manage the ASP.NET web service transaction option in VB.NET

Web service can initiate ADO.NET transactions. Web services can also participate in COM+ transactions. You can use COM+ transactions when you want to span multiple different data sources as a SQL Server database and an Oracle database. COM+ transactions commit or rollback automatically. However, they are always slower than using ADO.NET client-initiated/stored procedure transactions, because COM+ transactions use a two-stage commit protocol.

Because of the stateless nature of HTTP, web service methods can act only as the root object in a COM+ transaction. This means that a web service method can start a transaction and use it to perform a series of related tasks, but multiple web services cannot be grouped into one transaction. For that reason you should create a transactional web service carefully. For example, it won’t make sense to create a financial web service with separate DebitBankAccount() and CreditBankAccount()

methods, because they won’t be able to be grouped into a transaction. Instead, you can make sure both tasks are executed as a single unit using a transactional TransferFunds() method.

If you need to use a transaction in a web service, you should follow the next steps:

1. Add a reference to the System.EnterpriseServices assembly. To do this in Visual Studio, right-click References in the Solution Explorer, select Add Reference, and choose System.EnterpriseServices. You should then import the corresponding namespace so that the types you need (TransactionOption and ContextUtil) are at your fingertips: using System.EnterpriseServices.

2. Set the TransactionOption property of the WebMethod attribute. TransactionOption is an enumeration that provides several values that allow you to specify whether a code component uses or requires transactions. Because web services must be the root of a transaction, most of these options don’t apply. To create a web service method that starts a transaction automatically, use the following attribute:

 

<WebMethod(TransactionOption:=TransactionOption.RequiresNew)> _

Public Function TransactionMethod() As DataSet

‘…

End Function

3. The transaction is automatically committed when the web method completes. The transaction is rolled back if any unhandled exception occurs or if you explicitly instruct the transaction to fail using the following code:

 

ContextUtil.SetAbort()

 

This approach is illustrated with a web method in the next example. The method takes two actions: it deletes records in a database and then tries to read from a file. However, if the file operation fails and the exception isn’t handled, the entire transaction will be rolled back, and the deleted records will be restored:

 

 

<WebMethod(TransactionOption := TransactionOption.RequiresNew)> _

Public Sub UpdateDatabase()

 

‘ Create ADO.NET objects.

Dim Con As New SqlConnection(connectionString)

Dim Cmd As New SqlCommand(“DELETE * FROM Books”, Con)

 

‘ Apply the update. This will be registered as part of the transaction.

Using Con

Con.Open()

Cmd.ExecuteNonQuery()

End Using

 

‘ Try to access a file. This generates an exception that isn’t handled.

‘ The web method will be aborted and the changes will be rolled back.

 

Dim Fs As New FileStream(“ReadMe.txt”, IO.FileMode.Open)

 

‘ (If no errors have occurred, the database changes

‘ are committed here when the method ends).

End Sub

 

You can modify the code to catch the error, perform any cleanup that’s required, and then roll back the transaction:

 

<WebMethod(TransactionOption := TransactionOption.RequiresNew)> _

Public Sub UpdateDatabase()

 

‘ Create ADO.NET objects.

Dim Con As New SqlConnection(connectionString)

Dim Cmd As New SqlCommand(“DELETE * FROM Books”, Con)

 

‘ Apply the update.

Try

Con.Open()

Cmd.ExecuteNonQuery()

Dim Fs As New FileStream(“ReadMe.txt”, IO.FileMode.Open)

Catch

If Con.State <> ConnectionState.Closed Then

Con.Close()

End If

ContextUtil.SetAbort()

Finally

Con.Close()

End Try

End Sub