Old Problem – New Context

[Here is an old post I found that I had never published. I think it's because I could not get the formatting of the code to look "right". This time I published from Word and the formatting was still not preserved - rats! Also, the article is not as complete as I would like it but I wanted to get the code into the blogosphere.]

[There are a few technical things wrong with this post. The first is that using Thread Local Storage is risk (at best) in an ASP.NET application. See http://www.hanselman.com/blog/CommentView.aspx?guid=320. The second is that I would prefer to use a different pattern than the one below; something a little more elegant. Lastly, after using LINQ much more I don't know that this code is even a best practice; I need to noodle on this more.]

I keep thinking that there are like a dozen or so abstract problems that I solve and everything else is just another derivation from those. This is one of those dozen problems – what is context and why do we need it so often?

We have been using a lot of LINQ lately. While using LINQ to SQL I ran into a problem a few weeks ago and created a little hack to work around it. Well we are starting to roll LINQ out to the rest of the developers we need more than a hack for this issue. What is the issue, well it's the same we have had in the last 4 ADO stacks - how do I reuse a connection when I have a transaction open. The only time I have not had to work around this issue was in MTS/MTX and when we used the Microsoft Enterprise Library (EntLib)

The way MTX and EntLib get around this is that they are a "layer" in between me (you) and the underlying infrastructure. I remember Bernie's old saying - "Interesting things can happen when you can get in between things". Another way of saying that layers or interception are something that are a key part of any design.

So here is what I want the client/consuming code to look like.

const
string CN = "Server=localhost;Database=AdventureWorks2008;Trusted_Connection=true";

using (var aTrx = new TransactionScope())

{

var aCtx = new AdventureWorksDataContext(CN);

var x = from p in aCtx.Persons

where p.FirstName.StartsWith("A")

select p;

var y = x.First();

var aCtx2 = new AdventureWorksDataContext(CN);

var x2 = from p in aCtx2.Persons

where p.FirstName.StartsWith("A")

select p;

var y2 = x2.First();

}

So I wrote a little class that caches the connections so that they can be reused across LINQ statements. If we don't do it this way then we are invoking the Distributed Transaction Coordinator (DTC). We only want to do this when the statements need to be in the same transaction.

public
static
class
TransactionalDataContext

{

[ThreadStatic]

private
static Hashtable mConnections;

public
static IDbConnection DataContext(string pCnStr)

{

var aTrx = Transaction.Current;

if (aTrx == null)

{

return CreateConnection(pCnStr);

}

if (mConnections == null)

{

mConnections = new Hashtable();

}

if (mConnections.ContainsKey(pCnStr))

{

return (IDbConnection)mConnections[pCnStr];

}

var aCn = CreateConnection(pCnStr);

mConnections[pCnStr] = aCn;

aTrx.TransactionCompleted += TransactionCompleted;

return aCn;

}

private
static IDbConnection CreateConnection(string pCnStr)

{

// cn str is name value pairs with '=' and ';' separating each pair

// get each of the pairs

var aCnStrPairs = pCnStr.Split(new[] { ';' });

// find the pair with 'Provider'

var aProvPairEnum = aCnStrPairs.Where(i => string.Compare(i, "Provider", true) == 0);

IDbConnection aCn;

if (aProvPairEnum.Count() == 0)

{

// no provider specified so assume MS SQL Server

aCn = new SqlConnection(pCnStr);

}

else

{

// get the provider name

var aProvPair = aProvPairEnum.First();

var aProvNvp = aProvPair.Split(new[] { '=' });

// create a connection

var aFactory = System.Data.Common.DbProviderFactories.GetFactory(aProvNvp[1]);

aCn = aFactory.CreateConnection();

aCn.ConnectionString = pCnStr;

}

// we open the cn so that LINQ does not - this will also signal to LINQ not to close the cn

aCn.Open();

return aCn;

}

// handler for when the trx is complete

private
static
void TransactionCompleted(object sender, TransactionEventArgs e)

{

try

{

// dispose of each connection

foreach (DictionaryEntry aEntry in mConnections)

{

try

{

((IDbConnection)aEntry.Value).Dispose();

}

catch { }

}

}

finally

{

// reset the collection, in case doing another on this thread

mConnections.Clear;

}

}

}