CODEWITHSUNDEEP
transactionscopeazure-sql-database
Rick Make
Rick Make

Reputation: 521

TransactionScope() in Sql Azure

Does Sql Azure support using TransactionScope() when performing inserts? Below is a code snippet of what I am 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)
                    {
                    }
                 }
             }

Upvotes: 2

Views: 5769

Answers (2)

Torsten Grabs
Torsten Grabs

Reputation: 481

Quick update on distributed transactions with Azure SQL Database: A couple of days ago, we introduced support for distributed transactions in Azure SQL Database. The feature that lights up the capability is called elastic database transactions. It focuses on scenarios using the .NET distributed transaction APIs such as TransactionScope. These APIs start working against Azure SQL Database once you installed the new 4.6.1 release of the .NET framework. You can find more information about how to get started here: https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-transactions-overview/.

Please give it a try!

Upvotes: 3

Dilip Krishnan
Dilip Krishnan

Reputation: 5487

My understanding is that it works in the case when the transaction scope is tied to only one connection. Often times because its a best practice to open a connection late and close early. there might be situation where the scope spans two connections. Those scenarios are not supported in sql azure.

An example of where it might not work is; taking yr example; Assuming MyDataContext.GetDataContext() returns a new instance of a 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 aswell

Upvotes: 3

Related Questions