c# get current connection in TransactionScope transaction

Question

I want to make transactional a series of sp call (sql server 2005) in a .net 2.0 project surrounding some piece of business logic with a

using(TransactionScope...)

Unluckily, I inherited the DAL from another project and I don't want to make many changes there..the problem is that every method that calls a stored procedure opens a new connection.

So, my question is: is there a way to retrieve the connection used by current transaction i.e. from Transaction.Current??

Thank you

s.

UPDATE: Please tell me what it's wrong with this console application (vs2005, .net 2.0, Sql server 2005)

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Transactions;

namespace ConsoleApplication1
{
    public class Program
    {
        static void Main(string[] args)
        {
            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
            {
                Console.WriteLine("1");
                test();
                Console.WriteLine("2");
                test();
            }
            Console.WriteLine("END");
        }

        public static void test()
        {
            string connectionString = @"Persist Security Info=True;User ID=usr123;Password=123;Initial Catalog=db123;Data Source=myserver\myinstance;Connect Timeout=180;";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
            }
        }
    }
}

Answer 1

OK, a couple of things:

1. I doubt that TransactionScope even has the notion of a (Sql)Connection. The reason is that it does work with all sorts of transactional resources, whether that is a dabatabase, a message queuing system, or whatever. You may want to refer to the MSDN Docs for System.Transactions for more information. So, I guess your approach is doomed to fail to begin with.

2. In your example you're missing the "ts.Complete()" call on, thus your (distributed) transaction will always be rolled back when the using-Scope ends. Now, this has nothing to do with the problem you're describing, but it's worth pointing out nevertheless.

3. Your ambient transaction, the one resembled by the TransactionScope-instance, is propagated to a distributed transaction because you are using more than one connection inside of it. So essentially, the DTC on your system needs to talk with the DTC on the database server. For that to work both must be configured correctly.

To configure the DTC run the "Component Services" Management Console by executiong C:\Windows\System32\com\comexp.msc. In the treeview navigate to "Component Services\Computers\My Computer". In the context menu open the "Properties". In the properites dialog select the "MSDTC" tab, on it click the "Security Configuration..." button.

In the dialog make sure that the following options are selected:

• "Network DTC Access"
• "Allow Remote Clients"
• "Allow Inbound"
• "Allow Outbound"
• "Enable Transaction Internet Protocol (TIP) Transactions"
• "Enable XA Transactions"

(Note: Some of these might not actually be necessary, YMMV)

You may also want to to set to "No Authentication Required", depending on your local policies/requirements.

You need to do this on both systems: the one your app runs on, and the one with the database.

Answer 2

Ok, thank you all.. ..finally I ended writing something like the Microsoft.Practices.EnterpriseLibrary.Data.TransactionScopeConnections you can find in Enterprise Library (http://entlib.codeplex.com )..

Answer 3

Unless you're not using connection pooling for some reason, there's no good reason not to just new up a SqlConnection and go to town. Let the runtime handle the details of making that performant - that's what connection pooling is for. It sounds like the DAL is written correctly:

...
using (var conn = new SqlConnection("connection string"))
{
    using (var cmd = conn.CreateCommand())
    {
        conn.Open();
        //Do stuff with cmd
    }
}
....

Even if you're not using connection pooling (and you have a valid reason for not doing so), the best path is likely still going to be to new up a SqlConnection and go to town. You don't want to accidentally do something like close the connection the transaction scope is using, and that's assuming it actually has a SqlConnection for you to reference.