ADO.NET Entity Framework Transaction

Any data-driven application needs Transaction and to ensure that the unit of work is either together successful or fail. Transaction plays a very important role. Assume that we are working with SQL Server. Now SQL Server has it’s own Transaction mechanism which could be used in Stored Procedures. However, not every time a Transaction is just about adding or deleting values to multiple database tables. It could also get involved into writing to a local system file or registry along with database manipulation. So challenge comes when you have both of them together and want to ensure if a registry access is denied then database commit has to be rolled back. At times you need to add value to database first to get the Identity field value and then right to registry. So if you do not save value to a database the you won’t get the identity field value. But the challenge is how to revert that committed change from data.

Windows Distributed Transaction Coordinator (DTC) comes very handy in those cases. However DTC is quite expensive and needs to be up and running on both database and application machines.

ADO.NET EF uses Transaction by default. For SQL Server it uses SqlTransaction. So need to bother about it if you are working on single table and referential integrity issues are occurring. 

Using TransactionScope class

To use TransactionScope class you need to keep two things in mind

1. You need to add System.Transactions reference to your project
2. Make sure the Windows service “Distributed Transaction Coordinator” is up and running.

Now below code saves one entry to one database and then reads the newly generated EmpId and saves to another database

1. Emp emp = new Emp() { FirstName = "First Name", LastName = "Last Name" };
2.  
3. using (TransactionScope ts = new TransactionScope())
4. {
5.     using (TestDBEntities ctx = new TestDBEntities())
6.     {
7.         ctx.Emps.AddObject(emp);
8.         try
9.         {
10.             ctx.SaveChanges(System.Data.Objects.SaveOptions.None);
11.  
12.             TestDB_TransactionEntities ctxN = new TestDB_TransactionEntities();                        
13.             Emp_Transaction emp2 = new Emp_Transaction() { FullName = emp.FirstName + " " + emp.LastName + "-" + emp.EmpId};
14.             ctxN.Emp_Transaction.AddObject(emp2);
15.             ctxN.SaveChanges();
16.                         
17.             ts.Complete(); //Commiting
18.  
19.             ctx.AcceptAllChanges();
20.             ctxN.AcceptAllChanges();
21.             ctxN.Dispose();
22.         }
23.         catch (Exception ex)
24.         {
25.             Console.WriteLine(ex.InnerException);
26.         }
27.     }                
28. }

Now if you observe the Sql Profile you will find few interesting information

SQLTransaction gets promoted to DTC and then DTC takes the charge.

Namoskar!!!