Transactionscope Helper That Exhausts Connection Pool Without Fail - Help?

A while back I asked a question about TransactionScope escalating to MSDTC when I wasn't expecting it to. (Previous question) What it boiled down to was, in SQL2005, in order to us

Solution 1:

The expected TransactionScope/SqlConnection pattern is, according to MSDN:

using(TransactionScope scope = ...) {
  using (SqlConnection conn = ...) {
    conn.Open();
    SqlCommand.Execute(...);
    SqlCommand.Execute(...);
  }
  scope.Complete();
}

So in the MSDN example the conenction is disposed inside the scope, before the scope is complete. Your code though is different, it disposes the connection after the scope is complete. I'm not an expert in matters of TransactionScope and its interaction with the SqlConnection (I know some things, but your question goes pretty deep) and I can't find any specifications what is the correct pattern. But I'd suggest you revisit your code and dispose the singleton connection before the outermost scope is complete, similarly to the MSDN sample.

Also, I hope you do realize your code will fall apart the moment a second thread comes to play into your application.

Solution 2:

Is this code legal?

using(TransactionScope scope = ..)
{
    using (SqlConnection conn = ..)
    using (SqlCommand command = ..)
    {
        conn.Open();

        SqlCommand.Execute(..);
    }

    using (SqlConnection conn = ..) // the same connection string
    using (SqlCommand command = ..)
    {
        conn.Open();

        SqlCommand.Execute(..);
    }

    scope.Complete();
}