TransactionScope and SQL Server Compact

Question

SQL Server Compact doesn't support distributed transactions. So if there are more than one connection inside TransactionScope - the exception is thrown. Is there any way to setup ADO.NET provider to use one connection for the same connection string?

I understand I can use usual transactions through connection.BeginTransaction but TransactionScope is preferable for me.

UPDATE.
Sorry, I didn't mention I work with Entity Framework, so I have no control on SQL Command. I may just pass connection string. And by some reason several connections objects created for one connection string inside TransactionScope.

Can you provide some code? You can manually set the connection for your commands — VMAtm, Jul 14 '11 at 08:34

VMAtm · Accepted Answer · 2011-07-14T10:15:45.257

2

Updated answer (code sample from here):

using (var context = new MyContext())
{
    using (var txscope = new TransactionScope())
    {
        context.Connection.Open();
        // do query 1
        // do query 2
    }
}

Update

Another solution, as you said, is to create a connection object and use it in constructors for the Object Context.

More information about when Entities opens new connection.

edited Jul 14 '11 at 10:15

answered Jul 14 '11 at 08:41

VMAtm

27,943
17
79
125

But the @Idsa used that in his question. And it worked, as you can see from his own answer! – VMAtm Jul 14 '11 at 10:03
Don't know. Upvoted. Your solution will work for one ObjectContext. But I need one connection through several ObjectContext as I posted below. But I will mark your answer. – SiberianGuy Jul 14 '11 at 10:10
4

@leppie, SQLCE supports TransactionScope. It doesn't support distributed transactions – SiberianGuy Jul 14 '11 at 10:11
@Idsa Added your silution also. – VMAtm Jul 14 '11 at 10:16
My experience is that SQLCE does NOT support TransactionScope. Really easy to test yourself and prove me wrong. Just insert 1 row in one table and insert another row in another table and throw an error without committing the scope. – kuklei Feb 18 '18 at 15:07
As stated above, SQLCE does not not support distributed transactions, not the `TransactionScope` – VMAtm Feb 18 '18 at 15:22
1

I stand corrected. There is a limitation with SQLCE that the connection should be opened within the transactionscope. I had the connection already opened that is why my test was not working. Here the technet article for further reference https://technet.microsoft.com/en-us/library/bb896149(v=sql.110).aspx – kuklei Feb 18 '18 at 15:29

score 1 · Answer 2 · edited Jul 14 '11 at 10:35

1

My error was that I passed connection string to ObjectContext. If I pass connection object, only one connection is used.

edited Jul 14 '11 at 10:35

VMAtm

27,943
17
79
125

answered Jul 14 '11 at 09:49

SiberianGuy

24,674
56
152
266

TransactionScope and SQL Server Compact

2 Answers2

Linked