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 (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;
	}

Here is a full example:

[This sample can be found at M:\web\users\Sites\AspnetQuickStart\v2.0\QuickStart\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.

		
	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);
	}

Here is a full example:

[This sample can be found at M:\web\users\Sites\AspnetQuickStart\v2.0\QuickStart\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.]