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 (TransactionScope ts = new TransactionScope()) { using (SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind")) { SqlCommand myCommand = new SqlCommand(); myConnection.Open(); myCommand.Connection = myConnection; //Perform SQL work using myConnection. } ts.Complete(); } } catch(System.Transactions.TransactionException ex) { Console.WriteLine(ex); } catch { Console.WriteLine("Cannot complete transaction"); throw; } 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:

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. CommittableTransaction tx = new CommittableTransaction(); using (SqlConnection myConnection = new SqlConnection("server=(local)\\SQLExpress;Integrated Security=SSPI;database=northwind")) { myConnection.Open(); //Give the transaction to SQL to enlist with myConnection.EnlistTransaction(tx); } 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: