none
TransactionScope kills Connection Pooling!? RRS feed

  • Question

  • I am moving this over from the the Transactions Programming Forum because it is more of a question for this Data Access forum. It was created by "iwwi".

     

    Hi All,

     

    I have a code like this:

     

    string connectionString = "Server=devserver;Database=mydb;Trusted_Connection=yes";

    SqlConnection connection1;

    SqlCommand cmd = new SqlCommand("INSERT INTO [mydb].[dbo].[TT]([CreatedOn],[Value])VALUES(" + DateTime.Now.ToShortDateString() +", '1')");

    using (TransactionScope scope1 = new TransactionScope())

    {

    connection1 = new SqlConnection(connectionString);

    connection1.Open();

    cmd.Connection = connection1;

    cmd.ExecuteNonQuery();

    SqlConnection connection11 = new SqlConnection(connectionString);

    connection11.Open();

    }

     

    This creates esclation to DTC!!! why? it is the same connection string. This creates to us a huge problem. we have a large data access layer where each method opens connection does something and closes it. now, if the business layer uses a transaction scope and calls within it to two data access methods. it creates esclation to the DTC even though it is the exact same connection string.

     

    Any ideas?

     

    p.s. even if closing connection1 before opening connection11 (which is the case in out DAL because each function separately opens and closes the connection) whould make sense to create esclation, but in the example above both connections are opened at the same time.

     

    Kindest regards,

    Joe

     

    Wednesday, November 28, 2007 7:18 PM

All replies

  •  

    Are you using a SQL 2000 backend? We've seen some cases where this happens with this backend but not later versions of SQL Server.
    Thursday, November 29, 2007 12:06 AM