TransactionScope and method call that uses the same connection

Question

I'm using TransactionScope to make a method that contains multiple sql statements transactional. Now i need to call a second method that also uses the same connection and i receive following exception at connection.Open():

Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool.

So this is the pseudo-code:

public static void Method1()
{
    using (TransactionScope scope = new TransactionScope())
    {
        bool success = true; // will be set to false in an omitted catch
        using (var connection = new SqlConnection(ConnectionString1))
        {
           // ...
           if(somethingHappened)
               Method2();
        }
        if(success)
            scope.Complete();
    }
}

public static void Method2()
{
    using (var connection = new SqlConnection(ConnectionString1))
    {
        connection.Open(); // BOOOM!
        // ...
    }
}

How to avoid this exception without repeating the code from Method2 in Method1?

http://stackoverflow.com/questions/7694/how-do-i-enable-msdtc-on-sql-server, check if the answer with 64 upvotes helps — mybirthname, Nov 21 '16 at 11:42
@Steve, This error on different connectionstrings is expected behavior — Fabio, Nov 21 '16 at 11:44
@mybirthname: if possible i want to avoid using MSDTC because it's unneeded overhead. If i understand it correctly DTC needs to be used only when more than one physical computer is going to be involved in a distributed transaction. This is not the case, it's the same database on the same server, actually it's the same connection string. — Tim Schmelter, Nov 21 '16 at 11:46
If more then one connections are open in same time it will automatically escalate to the DTC - but not sure about version of Sql Server — Fabio, Nov 21 '16 at 11:51
@Fabio: so what is your suggestion, to pass the connection to Method2? That's ugly because it's a public method. — Tim Schmelter, Nov 21 '16 at 11:52
I prefer to use own connection for every query - so my suggestion: execute command with connection1, then close it, then open connection2 with method2 — Fabio, Nov 21 '16 at 11:54
@fabio: ok, so first `Complete` the `TransactionScope` and then call `Method2`. But then i don't include it in my transaction. Since that is updating the database too it would be nice if it could be run in the same transaction. — Tim Schmelter, Nov 21 '16 at 11:57
All method/queries executed under same TransactionScope will be executed as one trasaction — Fabio, Nov 21 '16 at 11:58
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128620/discussion-between-fabio-and-tim-schmelter). — Fabio, Nov 21 '16 at 11:58

score 2 · Accepted Answer · answered Nov 21 '16 at 12:10

If more then one connection are open under same TransactionScope it will be automatically escalated to the DTC.

You need to close first connection before calling Method2.

public static void Method1()
{
    using (TransactionScope scope = new TransactionScope())
    {
        bool success = true; // will be set to false in an omitted catch

        bool isSomethingHappened
        using (var connection = new SqlConnection(ConnectionString1))
        {
           isSomethingHappened = // Execute query 1
        }

       if(somethingHappened)
           Method2();

        if(success)
            scope.Complete();
    }
}

Ah. The question was about the same connection *string*, not the same connection :/ — tymtam, Nov 21 '16 at 12:20

score 2 · Answer 2 · edited May 23 '17 at 10:30

2

Nested connections under the same transaction scope will promote to a distributed transaction.

From SQL server 2008 and above multiple (not nesting) connections under the same transaction scope will not promote to a distributed transaciton.

see this question for more information

edited May 23 '17 at 10:30

Community

1
1

answered Apr 05 '17 at 08:34

Oglam

41
3

tymtam · Answer 3 · 2016-11-21T12:09:20.543

0

I don't know the precise answer, but I would make the connection a member, and keep track if it's open.

Then in Method1 and Method2 I would get the connection via some GetConnection() which would open the connection on first use.

After reading the comments I would suggest a private DoMethod2 which takes in a connection object.

edited Nov 21 '16 at 12:09

answered Nov 21 '16 at 12:00

tymtam

31,798
8
86
126

TransactionScope and method call that uses the same connection

3 Answers3

Linked