Friday, December 7, 2007

MSDTC + Linq to Sql Update

When I was writing my previous article, I was keenly aware that it had it's limitations. One in particular was using an already open DataContext.

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();
}
Since it was created outside the TransactionScope I have no way of automatically sharing these connections.

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:

c0rle0ne said...

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

Luke Marshall said...

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

Tags said...

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

Luke Marshall said...

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