TransactionScope () in Azure Sql

Question

Does Sql Azure support using TransactionScope () when performing inserts? Below is a code snippet of what I'm trying to do.

using (var tx = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted })) { using (var db = MyDataContext.GetDataContext()) { try { MyObject myObject = new MyObject() { SomeString = "Monday" }; db.MyObjects.InsertOnSubmit(myObject); db.SubmitChanges(); tx.Complete(); } catch (Exception e) { } } } 

Answer 1

I understand that it works when the transaction scope is bound to only one connection. Often, because its best practice to open communication late and close early. a situation may arise where the scope covers two compounds. These scripts are not supported in sql azure.

An example of where it might not work; taking the example yr; Assuming MyDataContext.GetDataContext () returns a new instance of the connection.

  using (var tx = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions() { IsolationLevel = IsolationLevel.ReadCommitted } )) { try{ DoSomething(); //a method with using (var db = MyDataContext.GetDataContext()) DoSomethingElse(); //another method with using (var db = MyDataContext.GetDataContext()) tx.Complete(); } catch { //Exception handler } } 

These links should give you some pointers as well

• Transactions in Azure Sql

Answer 2

Quickly update distributed transactions with Azure SQL Database: A couple of days ago, we introduced distributed transaction support in Azure SQL Database. The function that highlights this feature is called elastic database transactions. It focuses on scripting using distributed transactional .NET APIs such as TransactionScope. These APIs start working with Azure SQL Database after installing the new version 4.6.1 of the .NET platform. You can find more information on how to get started here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-transactions-overview/ .

Please, try!