Concept Of Transaction Scope (Local and Distributed Transaction)

1. What is Transaction
   
   Transaction is nothing but a logical unit of work consisting of one or more than one activities which need to succeed or fail all activities to maintain the integrity of information of business, work consistency and separation of work from each other to avoid conflict of information and durability of work with atomicity means guaranteed success of all activities otherwise guaranteed failure of all, no partial success or partial failure (refer ACID various article available on internet).
   
   We are not going to learn too much theory about Transaction, Transaction Scope, Local and Distributed since there are already tones of article available on the internet.
   
   
2. Types of Transaction
   
   a. Business Transaction
   
   Business Transaction means activities involved between Customer, Sales Person, and Stakeholder, Vendor etc. in order to carry out day to day business of Company or a set of activities involved to achieve long running business deals.
   
   b. Database Transaction
   
   Database Transaction is nothing but a set of actions involved to manipulate the database or databases. Database Transaction can be classified as Non-Distributed Transaction (Local Transaction) and Distributed Transaction.
   
   • Non-Distributed Transaction (Local Transaction) - Where application only deals with the single database and perform all data manipulation action on same database.
   
   • Distributed Transaction- Where application deals with various databases and perform all data manipulation action on multiple database. To apply Distributed Transaction we have to start DTC (Distributed Transaction Coordinator) service from Services panel.
   
   Note- An application can have both types of Transactions.
   
   
3. History
   
   Before introduction of .Net Framework, classic Visual Basic was widely used to develop RAD and Enterprise Level Application and successfully used by small to enterprise level industry, where COM and COM+ technology was playing role to implement distributed technology, which was generally called COM era.
   
   Classic Visual Basic contains various Data Access Components called DAO, RDO and ADODB and each one used to provide programming interface to control Non-Distributed Transaction (Local Transaction).
   
   COM+ was/is one of the robust technology to implement Transaction Scope which maintain the theory of ACID which is used to control Distributed Transaction by wrapping series of action of related operations together in a COM+ transaction that either completely succeeds or completely fails.
   
   
4. When to use
   
   When Application deals several DML statement under single event either on single or multiple databases.
   
   
5. Various ways of implementing Transaction Scope
   
   There are various ways to implement Transaction Scope for either Non- Distributed or Distributed Transaction and they depend upon various Data Access technologies.
   
   In this article we will cover implementation of Transaction Scope with the help of various features and frameworks given by Microsoft as Data Access Components which comes under .Net Framework.
   However before diving into Transaction Scope, let us see what are all major components available under .Net framework to Access the Data source?
   
   These are main Data Access Components in .Net Framework now some of available as open source.
   
   1. ADO .Net
   2. Entity Framework
   3. LINQ to SQL
   
   Note-LINQ technology is similar to Entity Framework so we are not covering its code implementation.
   
   
6. Transaction Implementation using ADO .Net
   
   ADO .Net allow us to control the Local Transaction using given Data Access methodology like SQL Client, OLEDB, ODBC and Oracle Client.
   
   • Local Transaction
   

   //LOCAL TRANSACTION
          //USING TRANSACTION OBJECT OF SAME DATA PROVIDER
          internal void FirstMethod()
          {
              string connectionString = ConfigurationManager.ConnectionStrings["DatabaseOne"].ToString();
              SqlTransaction transaction = null;
   
              using (var conn = new SqlConnection(connectionString))
              {
                  conn.Open();
   
                  try
                  {
                      transaction = conn.BeginTransaction();
   
                      var cmd = new SqlCommand("Insert Into Savings (AccountID,Amount) Values (@AccountID,@Amount)", conn, transaction);
                      cmd.Parameters.Add(new SqlParameter() { ParameterName = "@AccountID", DbType = System.Data.DbType.Int32, Value = 11100001 });
                      cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Amount", DbType = System.Data.DbType.Int32, Value = 10000 });
   
                      cmd.ExecuteNonQuery();
                      transaction.Commit();
                  }
                  catch (Exception)
                  {
                      if (transaction != null) { transaction.Rollback(); }
                  }
              }
          }

   Local Transaction can also be implemented using Transaction Scope Class:
   

   //LOCAL TRANSACTION
           //USING TRANSACTION SCOPE OBJECT
           internal void SecondMethod()
           {
               string connectionString = ConfigurationManager.ConnectionStrings["DatabaseOne"].ToString();
   
               using (TransactionScope scope = new TransactionScope())
               {
                   using (var conn = new SqlConnection(connectionString))
                   {
                       var cmd = new SqlCommand("Insert Into Savings (AccountID,Amount) Values (@AccountID,@Amount)", conn);
                       cmd.Parameters.Add(new SqlParameter() { ParameterName = "@AccountID", DbType = System.Data.DbType.Int32, Value = 11100001 });
                       cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Amount", DbType = System.Data.DbType.Int32, Value = 10000 });
   
                       cmd.ExecuteNonQuery();
                   }
                   scope.Complete();
               }
           }

   • Distributed Transaction
   
   .Net framework gives Transaction Scope class to deal with the multiple Transaction applied on multiple database.
   

   //DISTRIBUTED TRANSACTION
          //USING TRANSACTION SCOPE OBJECT
          internal void FirstMethod()
          {
              string connectionString = ConfigurationManager.ConnectionStrings["DatabaseOne"].ToString();
              string connectionString1 = ConfigurationManager.ConnectionStrings["DatabaseTwo"].ToString();
   
              using (TransactionScope scope = new TransactionScope())
              {
                  using (var conn = new SqlConnection(connectionString))
                  {
                      conn.Open();
                      var cmd = new SqlCommand("Insert Into Savings (AccountID,Amount) Values (@AccountID,@Amount)", conn);
                      cmd.Parameters.Add(new SqlParameter() { ParameterName = "@AccountID", DbType = System.Data.DbType.Int32, Value = 11100001 });
                      cmd.Parameters.Add(new SqlParameter() { ParameterName = "@Amount", DbType = System.Data.DbType.Int32, Value = -10000 });
                      cmd.ExecuteNonQuery();
                      using (var conn1 = new SqlConnection(connectionString1))
                      {
                          conn1.Open();
                          var cmd1 = new SqlCommand("Insert Into Savings (AccountID,Amount) Values (@AccountID,@Amount)", conn1);
                          cmd1.Parameters.Add(new SqlParameter() { ParameterName = "@AccountID", DbType = System.Data.DbType.Int32, Value = 44400001 });
                          cmd1.Parameters.Add(new SqlParameter() { ParameterName = "@Amount", DbType = System.Data.DbType.Int32, Value = 10000 });
                          cmd1.ExecuteNonQuery();
                      }
                  }
                  scope.Complete();
              }
          }
      }

7. Transaction Implementation using Entity Framework
   
   As we know Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects means don’t need to work on SQL queries instead developer has to focus on classes and properties which maps the physical structure of database.
   
   • Local Transaction
   

   //LOCAL TRANSACTION
          //USING TRANSACTION OBJECT OF SAME DATA PROVIDER
          internal void FirstMethod()
          {
              DatabaseOne db = new DatabaseOne();
              System.Data.Entity.DbContextTransaction transaction = null;
   
              try
              {
                  transaction = db.Database.BeginTransaction();
   
                  db.Savings.Add(new EntityOne.Saving() { AccountID = 11100001, Amount = 1000 });
                  db.SaveChanges();
                  transaction.Commit();
              }
              catch (Exception)
              {
                  transaction.Rollback();
              }
          }

   Local Transaction can also be implemented using Transaction Scope Class:
   

   //LOCAL TRANSACTION
          //USING TRANSACTION SCOPE OBJECT
          internal void SecondMethod()
          {
              using (TransactionScope scope = new TransactionScope())
              {
                  DatabaseOne db = new DatabaseOne();
   
                  db.Savings.Add(new EntityOne.Saving() { AccountID = 11100001, Amount = 1000 });
                  db.SaveChanges();
   
                  scope.Complete();
              }
    }

   • Distributed Transaction
   

   //DISTRIBUTED TRANSACTION
         //USING TRANSACTION SCOPE OBJECT
         void FirstMethod()
         {
             using (TransactionScope scope = new TransactionScope())
             {
                 DatabaseOne dbOne = new DatabaseOne();
   
                 dbOne.Savings.Add(new EntityOne.Saving() { AccountID = 11100001, Amount = -1000 });
                 dbOne.SaveChanges();
   
                 DatabaseTwo dbTwo = new DatabaseTwo();
   
                 dbTwo.Savings.Add(new EntityTwo.Saving() { AccountID = 44400001, Amount = 1000 });
                 dbTwo.SaveChanges();
   
                 scope.Complete();
             }
   }

   Hope the given explanation and code block might clear any developer doubts. Enjoy coding with C# or VB.

Read more articles on Transaction:

• Transaction And Isolation Levels In WCF
• A Transaction Scope Or Nested Transaction