locked
Transaction Scope and Sql Connection with Using Statement RRS feed

  • Question

  • Hi, 

    While referring msdn programming reference for "System.Tranactions.TransactionScope", I found the following example in http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

            using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection connection1 = new SqlConnection(connectString1))
                {
                     connection1.Open();
    
                      SqlCommand command1 = new SqlCommand(commandText1, connection1);
                    returnValue = command1.ExecuteNonQuery();
                    writer.WriteLine("Rows to be affected by command1: {0}", returnValue);
    
                    using (SqlConnection connection2 = new SqlConnection(connectString2))
                    {
                        connection2.Open();
    
                        returnValue = 0;
                        SqlCommand command2 = new SqlCommand(commandText2, connection2);
                        returnValue = command2.ExecuteNonQuery();
                        writer.WriteLine("Rows to be affected by command2: {0}", returnValue);
                    }
                }
    
                scope.Complete(); //In this place, the sql connection object is already disposed. Correct me if I am wrong
    
            }
    


    Though, the Using block for SqlConnection object is closed before "scope.Complete()" statement, the transaction is committed. How this is done. Since once the using block is closed, the SqlConnection object itself disposed, then how the scope.Complete() completes the transaction to the DB.

    Thanks and Regards,


    Vijay Pandurangan
    Wednesday, October 5, 2011 10:39 AM

Answers

  • If a SqlConnection still has an outstanding transaction when it is Disposed, it goes back into the connection pool, but into a special sub-pool for connections that still have transactions outstanding.

    When the transaction completes, the connection is put back into the "regular" pool.

    If a connection is requested from the pool inside the same transaction scope, it will pull the same connection from the "transacted pool" because of the matching transaction.

     

    Jim

     

    Friday, October 14, 2011 12:46 AM
    Moderator

All replies

  • first sqlconnection state is open firstly you close first connection state. then you make second connection
    Thursday, October 13, 2011 8:10 AM
  • If a SqlConnection still has an outstanding transaction when it is Disposed, it goes back into the connection pool, but into a special sub-pool for connections that still have transactions outstanding.

    When the transaction completes, the connection is put back into the "regular" pool.

    If a connection is requested from the pool inside the same transaction scope, it will pull the same connection from the "transacted pool" because of the matching transaction.

     

    Jim

     

    Friday, October 14, 2011 12:46 AM
    Moderator
  • Hi Jim,

    Thanks for the explanation. This really make sense. I wonder, why this is not explained anywhere in MSDN or Did I missed to refer some links.

    Thanks and Regards,


    Vijay Pandurangan
    Friday, October 14, 2011 5:23 AM
  • Vijay,

    A quick search of MSDN with "connection pooling" found this:

    http://msdn.microsoft.com/en-us/library/8xx3tyca(v=VS.80).aspx

    which states:

    Transaction Support

    Connections are drawn from the pool and assigned based on transaction context. Unless <tt>Enlist=false</tt> is specified in the connection string, the connection pool ensures that the connection is enlisted in the Current context. When a connection is closed and returned to the pool with an enlisted System.Transactions transaction, it is set aside in such a way that the next request for that connection pool with the same System.Transactions transaction will return the same connection. If there is no connection available for that transaction, the connection is automatically enlisted when it is opened.

    When a connection is closed, it is released back into the pool and into the appropriate subdivision based on its transaction context. Therefore, you can close the connection without generating an error, even though a distributed transaction is still pending. This allows you to commit or abort the distributed transaction at a later time.

     

    Jim

     

    Friday, October 14, 2011 5:40 PM
    Moderator