7

This is related to the Nested Database transactions in C#.

The objects in collection I want to take in transaction implement their own transactions using SqlConnection.BeginTransaction method.

After reading this post I am not sure if I can mix those too or not. I am using SQL Server 2005 and each object uses connection details from static configuration class.

Does anybody have experience with this ?

Here is the sample code:

using(TransactionScope scope = new TransactionScope())
{
  for (int i=0; i<=1000....) 
  {
    SqlConnection con = new SqlConnection()
    SqlCommand cmd = new SqlCommand("delete from ...", con); 

    try {
       con.Open(); 
       DbTransaction t = con.BeginTransaction();
       cmd.ExecuteNonQuery(); 
       ...
       cmd.CommandText = .... ;
       cmd.ExecuteNonQuery(); 
       t.Commit  ...
    } 
    catch {
       t.Rollback ...
    }
    con.Close()
  }
}

Thx

Community
  • 1
  • 1
majkinetor
  • 8,730
  • 9
  • 54
  • 72

1 Answers1

5

After spending hours to configure MSDTC to work on both machines, I finally got to the point where I could test the code.

It appears to work (with 1 database)

The above problem may be usefull to people that don't have access to the source code so can't get rid of "legacy" transactional system or if transactional parts of the code are not localised or, like in my case, you don't want to mess too much with the complex code that is proven to be stable in production environment but you need to introduce nested transactions...

Let me know if you have different experience.

majkinetor
  • 8,730
  • 9
  • 54
  • 72
  • 1
    In my case we have "unit" tests that are wrapped in TransactionScope so that on our development machines we can achieve complex database manipulations without thinking about it for each test and test class and have it all [theoretically] rolled back. At the same time, we don't necessarily want MS DTC usage on the production servers, and I wanted to wrap a few compound Entity Framework entity manipulations in a regular transaction so that the whole operation can rollback if any of it fails... Ideally the TransactionScope should continue to work, and the inner transaction should too... – bambams Jun 19 '15 at 19:35