Transaction In .NET

-- Table1  
CREATE TABLE tblProject    
(    
   ProjectID int PRIMARY KEY,    
   Name varchar(50) NULL    
);    
    
--Table2  
CREATE TABLE tblProjectMember    
(    
   MemberID int,    
   ProjectID int foreign Key references Project(ProjectID)    
);

string strConnString = "myconnectionstring"; // get it from Web.config file    
SqlTransaction objTrans = null;    
   
using (SqlConnection objConn = new SqlConnection(strConnString))    
{    
   objConn.Open();    
   objTrans = objConn.BeginTransaction();    
   SqlCommand objCmd1 = new SqlCommand("insert into tblProject values(1, 'TestProject')", objConn);    
   SqlCommand objCmd2 = new SqlCommand("insert into tblProjectMember(MemberID, ProjectID) values(2, 1)", objConn);    
   try    
   {    
      objCmd1.ExecuteNonQuery();    
      objCmd2.ExecuteNonQuery(); // Throws exception due to foreign key constraint   
      objTrans.Commit();    
   }    
   catch (Exception)    
   {    
      objTrans.Rollback();    
   }    
   finally    
   {    
      objConn.Close();    
   }    
}

<system.transactions>    
  <defaultSettings timeout="30"/>    
  <machineSettings maxTimeout="1200"/>    
</system.transactions> 

using (var txscope =new TransactionScope(TransactionScopeOption.RequiresNew))      
{      
   try      
   {      
      using (SqlConnection objConn = new SqlConnection(strConnString))      
      {      
            objConn.Open();      
            SqlCommand objCmd1 = new SqlCommand("insert into tblProject values(1, 'TestProject')", objConn);      
            SqlCommand objCmd2 = new SqlCommand("insert into tblProjectMember(MemberID, ProjectID) values(2, 1)", objConn);      
    
            objCmd1.ExecuteNonQuery();      
            objCmd2.ExecuteNonQuery(); // Throws exception due to foreign key constraint    
    
            //The Transaction will be completed      
            txscope.Complete();                 
      }           
   }      
   catch(Exception ex)      
   {      
      // Log error      
      txscope.Dispose();      
   }      
}

Implement transactions In EntityFramework

In EntityFramework(EF) we have different versions, so EF 6.0 provides two APIs which are not available in previous versions. So we will discuss how can we implement transactions in earlier versions of EF. We can achieve it through TransactionScope class. In the below code sample, it creates a TransactionScope class object and defines the EF code for adding an object to the Project table, ProjectMember table. Once everything is fine, it calls Complete() to commit the data. If an exception occurs it rollsback to the previous state.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))    
{    
    try    
    {    
        // In the Data Access Layer    
        using (DBContextEntitis entities = new DBContextEntitis(entitiesConnectionstring))    
        {    
            Project proj = new Project() { ProjectID = 1, Name = "Temp Project" };    
            context.Project.Add(proj);    
                
            ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };    
            context.ProjectMembers.Add(projMember);    
                
            //The Transaction will be completed    
            scope.Complete();    
        }    
    }    
    catch(Exception ex)    
    {    
        scope.Dispose();    
    }    
}

Transaction in EntityFramework 6.0

By default EntityFramework Inserts, Updates, or Deletes operations in a transaction, when you call SaveChanges() method of the context object. EF starts a new transaction for each operation and completes the transaction when the operation finishes. EntityFramework 6.0 provides two new methods.

Database.BeginTransaction()

It facilitates starting and completing transactions themselves within an existing DbContext object. It allows several operations to be combined within the same transaction and hence either all are committed or all are rolled back as one. You can specify more easily the isolation level for the transaction.

using (var context = new MyDBContext())    
{    
   using (System.Data.Entity.DbContextTransaction dbTran = context.Database.BeginTransaction( ))    
   {    
      try    
      {    
         Project proj = new Project() { ProjectID = 1, Name = "Temp Project" };    
         context.Project.Add(proj);   
   
         ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };    
         context.ProjectMembers.Add(projMember);   
   
         //saves all above operations within one transaction    
         context.SaveChanges();   
   
         //commit transaction    
         dbTran.Commit();    
      }    
      catch (Exception ex)    
      {    
         //Rollback transaction if exception occurs    
         dbTran.Rollback();    
      }    
   }    
}

In the above code, an exception will occur due to the wrong project ID of the ProjectMember object. Transaction object dbTran will rollback all operations that happened before. Now you won't get any record in the Project table as well ProjectMember table.

Database.UseTransaction()

It allows the DbContext to use a transaction that was started outside of the Entity Framework.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))    
{    
   try    
   {    
      // In the Data Access Layer    
      using (DBContextEntitis entities = new DBContextEntitis(entitiesConnectionstring))    
      {    
         Project proj = new Project() { ProjectID = 1, Name = "Temp Project" };    
         context.Project.Add(proj);    
         ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };    
         context.ProjectMembers.Add(projMember);    
         //The Transaction will be completed    
         scope.Complete();    
      }    
   }    
   catch(Exception ex)    
   {    
      scope.Dispose();    
   }    
} 

To implement UseTransaction, you need to add a constructor that will receive a Database connection and closing connection indicator. The contextOwnConnection informs the Entity Framework to not close the connection when it is done.

public class DBEntitiesContext : DbContext    
{    
   public EntitiesContext() : base("name=Entities")    
   {    
      Database.Log = Console.WriteLine;    
   }  
   
   public EntitiesContext(DbConnection existingConnection, bool contextOwnConnection)    
   : base(existingDBConnection, contextOwnConnection)    
   {    
   }    
}

In the below code snippet it creates a transaction object using the BeginTransaction() method. Inside the transaction object scope, it executes some SQL queries. Next, it creates EntityFramework and creates an object by passing two params, SqlConnection object and closing connection indicator. It calls UseTransaction() with passing transaction object which created before. Now entity-framework operations are appended to the previous transaction scope.

using(SqlConnection con = new SqlConnection("connectionString"))    
{    
   con.Open();   
   
   using(var transaction = con.BeginTransaction())    
   {    
      SqlCommand objCmd1 = new SqlCommand("insert into tblProject values(1, 'TestProject')", objConn);    
      objCmd1.ExecuteNonQuery();   
   
      //Pass this transaction to EF....    
      using (DBEntitiesContext context = new DBEntitiesContext(con, false))    
      {    
         context.Database.UseTransaction(transaction);    
         ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };    
         context.ProjectMembers.Add(projMember);   
   
         context.SaveChanges();    
      }    
   }    
}

Default transaction level in EF6 has changed to READ COMMITTED SNAPSHOT

Implement transactions In SQL Server

We implemented transactions in front-end code(C# code) but we can also define transactions in back-end code like the SQLServer database.

Transaction Control

There are the following commands used to control transactions.

• COMMIT To save changes.
• ROLLBACK To rollback the changes.
• SAVEPOINT Creates points within groups of transactions in which to ROLLBACK
• SET TRANSACTION Places a name on a transaction.

Types of Transactions

Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements run under the implicit transaction. If an error occurs within these statements individually, SQL Server will roll back the complete statement.

Explicit transactions are defined by programmers. In Explicit transaction, we include the DML statements that need to be executed as a unit. Since SELECT statements don't modify data, hence generally we don’t include Select statement in a transaction.

Isolation level

It controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. There are five types of Isolation levels.

• READ UNCOMMITTED
  If any table is updated(insert or update or delete) under a transaction and the same transaction is not completed that is not committed or rolled back then uncommitted values will display (Dirty Read) in the select query of "Read Uncommitted" isolation transaction sessions.
• READ COMMITTED
  In a select query, it takes only the committed values of the table. If any transaction is opened and incomplete on the table in other sessions then the select query will wait until no transactions are pending on the same table.
• REPEATABLE READ
  Select query data of the table that is used under the transaction of isolation level. "Repeatable Read" cannot be modified from any other sessions until the transaction is completed.
• SERIALIZABLE
  Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on the range lock.
• SNAPSHOT
  Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold a lock on the table during the transaction so the table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case any data modification occurs in other sessions, then existing transaction displays the old data from Tempdb. It is the highest level of Isolation but it affects performance.

In SQLServer it is READ COMMITTED. For more on the isolation level, visit here.

Now we create StoredProcedure(SP) which implements transactions. The SP executes two inserted SQL statements: one for the tblProject table another for is the tblProjectMember table. It keeps all SQL statements inside the BEGIN TRANSACTION block and then commits. If any SQL fails then it goes to the CATCH block and rollsback to the previous state of the database.

using(SqlConnection con = new SqlConnection("connectionString"))    
{    
   con.Open();   
   
   using(var transaction = con.BeginTransaction())    
   {    
      SqlCommand objCmd1 = new SqlCommand("insert into tblProject values(1, 'TestProject')", objConn);    
      objCmd1.ExecuteNonQuery();   
   
      //Pass this transaction to EF....    
      using (DBEntitiesContext context = new DBEntitiesContext(con, false))    
      {    
         context.Database.UseTransaction(transaction);    
         ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };    
         context.ProjectMembers.Add(projMember);   
   
         context.SaveChanges();    
      }    
   }    
} 

@@TRANCOUNT: Returns the number of BEGIN TRANSACTION statements that have occurred on the current transaction. The BEGIN TRANSACTION statement adds @@TRANCOUNT by 1. ROLLBACK TRANSACTION l @@TRANCOUNT to 0. COMMIT TRANSACTION or COMMIT WORK subtracts @@TRANCOUNT by 1.

Let's call the Stored procedure from code like below.

using (SqlConnection con = new SqlConnection(connectionString))    
{    
   using (SqlCommand cmd = new SqlCommand("spAddProject", con))    
   {    
      cmd.CommandType = CommandType.StoredProcedure;    
      cmd.Parameters.AddWithValue("@ProjectID", 1);    
      cmd.Parameters.AddWithValue("@MemberID", 1);    
      cmd.Parameters.AddWithValue("@Name", "Test Proejct");   
   
      con.Open();    
      cmd.ExecuteNonQuery();    
   }    
}

Distributed Transactions

A transaction that works with multiple data sources is called a distributed transaction. If a transaction fails then the affected data sources will be rolled back. In System.Transactions, MSDTC (Microsoft Distributed Transaction Coordinator) manages distributed transactions. It implements a two-phase commit protocol. A distributed transaction is much slower than a local transaction. The transaction object automatically escalates a local transaction to a distributed transaction when it understands that a distributed transaction is needed. The developer cannot do anything here.

Conclusion

We discussed what is transaction and how we can use it in .Net, SQLServer. We also discussed transactions in ADO.NET, Entity Framework. Choose as per your requirement.

Hope this helps.