SQL Azure Transactions

Handling Transactions in SQL Azure

http://social.technet.microsoft.com/wiki/contents/articles/handling-transactions-in-sql-azure.aspx

Local Transactions

SQL Azure supports local transactions. These types of transactions are done with the Transact-SQL commands BEGIN TRANSACTION  , ROLLBACK TRANSACTION  , COMMIT TRANSACTION  . They work exactly the same as they do on SQL Server.

Distributed Transactions in SQL Azure

SQL Azure Database does not support distributed transactions, which are transactions that multiple transaction managers (multiple resources). For more information, see Distributed Transactions (ADO.NET)  . This means that SQL Azure doesn’t allow Microsoft Distributed Transaction Coordinator (MS DTC)to delegate distributed transaction handling. Because of this you can’t use ADO.NET or MSDTC to commit or rollback a single transaction that spans across multiple SQL Azure databases or a combination of SQL Azure and an on-premise SQL Server.

This doesn’t mean that SQL Azure doesn’t support transactions, it does. However,it only supports transactions that are not escalated to a resource manager such as MS DTC. An article entitled: Transaction Management Escalation  on MSDN can give you more information.

TransactionScope and SqlTransaction

The TransactionScope  class provides a simple way to mark a block of code as participating in a transaction, without requiring you to interact with the transaction itself. The TransactionScope class works with the Transaction Managerto determine how the transaction will be handled. If the transaction manager determines that the transaction should be escalated to a distributed transaction, using theTransactionScope class will cause a runtime exception when running commands against SQL Azure, since distributed transactions are not supported.

One way to write your code without using the TransactionScope class is to use SqlTransaction. TheSqlTransaction class doesn’t use the transaction manager, it wraps the commands within a local transaction that is committed when you call the Commit() method. You still can’t have a single transaction across multiple databases; however SqlTransaction class provides a clean way in C# to wrap the commands. If your code throws an exception,the using statement guarantees a call to IDispose which rolls back the transaction.

using (SqlConnection sqlConnection =    new SqlConnection(ConnectionString)){    sqlConnection.Open();    using (SqlTransaction sqlTransaction =        sqlConnection.BeginTransaction())    {        // Createthe SqlCommand object and execute the first command.        SqlCommand sqlCommand = new SqlCommand("sp_DoFirstPieceOfWork",            sqlConnection, sqlTransaction);        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;        sqlCommand.ExecuteNonQuery();        // Createthe SqlCommand object and execute the first command.        SqlCommand sqlCommand = new SqlCommand("sp_DoSecondPieceOfWork",            sqlConnection, sqlTransaction);        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;        sqlCommand.ExecuteNonQuery();        sqlTransaction.Commit();    }}