Take the following code:
// Create outside TransactionScope
var c1 = new UserDataContext();
using (var scope = new TransactionScope())
{
c1.Users.ToList();
// This will promote to MSDTC
using (var c2 = new UserDataContext())
c2.Users.ToList();
scope.Complete();
}
To help solve this problem (cleanly) I added a SharedConnectionScope class and a UseExistingConnection call (shown below) - both take an existing DataContext.
// Create outside TransactionScope
var c1 = new UserDataContext();
using (var scope = new TransactionScope())
using (SharedDataContext.UseExistingConnection(c1))
{
c1.Users.ToList();
// This will share existing connection
using (var c2 = new UserDataContext())
c2.Users.ToList();
scope.Complete();
}
Caveat: It only works with one 'external' connection, but that's still better than none.
You can grab the updated code here.
4 comments:
Hello!
Thanks for your code. it s great and very helpfull.
By the way I have a problem, maybe you can help me.
Here is a sample of my code
using (TransactionScope ts = new TransactionScope())
using (SharedDataContext.UseExistingConnection(monContext))
{
--DoingStuff
ts.Complete();
}
and after i m using my datacontext again, and i have a message telling me that the "ConnectionString is not initialize"
I looked in the code and so that in the dispose of the linqtransaction you are disposing the connection. that s why i have my exception.
Have you ever encountered this problem? can i not dispose the connection on the dispose of the linqtransaction?
Thanks in advance for your answer and thanks again for your great code!
Best Regards,
Albéric
Hi Albéric!
I'm glad you found my code helpful!
Sorry for my delayed response, yes the dispose closes the connection of the datacontext.
The recommended approach of linq2sql is to have short lived datacontexts, so I designed the class with that in mind.
Also problems can occur when exceptions are thrown within a transaction scope which put the context in an inconsistent state.
However you could write an optional flag for UseExistingConnection to only close the connection if there is an error.
That should be a relatively small change (in SharedDataContext.cs and SharedConnectionScope.cs) to suit your purpose.
Hope this helps, and if you need any more clarification please ask!
Cheers,
Luke
Hi,
Thanks for the code, its a really nice approach. though I'm having problem with stored procedure queries, maybe you can help me with this.
code:
using (var tx = new TransactionScope())
{
using (var db1 = new DataContext1(connstring))
{
ISingleResult result3 = db1.MySprocGetList1();
}
using (var db2 = new DataContext1(connstring))
{
ISingleResult result4 = db2.MySprocGetList2();
}
//some insert/updates here
tx.Complete();
}
error:
There is already an open DataReader associated with this Command which must be closed first.
Many Thanks,
Tags
Hi Tags,
Thanks for your comment - sorry I'm not exactly sure why you'd be having this issue, but if you find out what's going on I'd be keen to know!
Cheers,
Luke
Post a Comment