How Do I...? Common Tasks QuickStart Tutorial

How Do I...Use System.Transactions with SQL?

The easiest way to perform SQL operations in a transaction is with TransactionScope. Simply place all the SQL operations you want to execute within a TransactionScope block. The TransactionScope object will set the current transaction which SQL will automatically find when you execute SQL statements. If you need more than one SQL database in a single transaction, open a second connection and execute operations against it. The second connection will also automatically find the current transaction.

		
	Try
		Using scope As TransactionScope = New TransactionScope()

			Using myConnection As New SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind")
				Dim myCommand As New SqlCommand()
				myConnection.Open()
				myCommand.Connection = myConnection
			End Using
	
			scope.Complete()
		End Using
        Catch ex As TransactionException
        	Console.WriteLine(ex)
        Catch
        	Console.WriteLine("Cannot complete transaction")
                Throw
	End Try

Here is a full example:

[This sample can be found at D:\quickstarts.asp.net\QuickStartv20\howto\samples\Transactions\ScopeWithSQL
To build this sample, open the SDK command prompt and navigate to the above path. Build the sample using the build tool msbuild passing the solution file as the first parameter: msbuild mySample.sln. The compiled executable will be found in the sub directory \bin directory.]

If you want to manually create and pass the transaction to SQL, use the CommittableTransaction object. You can enlist SQL in the transaction by passing the transaction to the EnlistTransaction method on the SQLConnection object. You will need to call commit or rollback on the transaction and handle exceptions.

		
           Dim tx As New CommittableTransaction()

           Using myConnection As New SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind")
           		myConnection.Open()

           		'Give the transaction to SQL to enlist with
           		myConnection.EnlistTransaction(tx)
           End Using

Here is a full example:

[This sample can be found at D:\quickstarts.asp.net\QuickStartv20\howto\samples\Transactions\CommittableTxWithSQL
To build this sample, open the SDK command prompt and navigate to the above path. Build the sample using the build tool msbuild passing the solution file as the first parameter: msbuild mySample.sln. The compiled executable will be found in the sub directory \bin directory.]