Can I mix SqlConnection.BeginTransaction and TransactionScope ?

Question

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

Answer 1

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.