Connection Management ASP.net

Question

How do you manage your database connections in your ASP.Net application?

My understanding tells me the "best" way is to open a connection, make a query, close the connection - and do that multiple times because connection pooling makes the cost negligable.

The problem comes when I have a DAL where each method looks after its own connection. E.G.

User x = DAL.GetUserDetails();
Customer y = DAL.GetCustomer();

This is fine until we start talking about TransactionScope

using (TransactionScope t....
{
DAL.UpdateCustomer(y);
DAL.UpdateUser(x);
t.Complete();
}

ASP.Net wants to now use DTC because (I'm guessing) there are multiple connections involved.

Someone is going to say "Cache the connection somwhere" but I need to explicitly destroy the connection because of the way I am managing security (execute as / revert) and I don't want to have to make a call on every page to do that because someone will forget to make the call. I could also pass the connection into each method but thats not ideal because the page has to manage a connection.

Am I making sense or have I missed something fundamental here?

Answer 1

I was reading somewhere and I don't remember where that Microsoft was going to address that when you have two connections to the same DB that they would not escalate to DTC which would make this problem go away.

Until then what we did was to develop our TransactionScope, our DALs would then ask the TS for a new connection, and when we disposed the TS it would close the connection.

The connections were stored in LogicalCallContext, although I would look at using HTTP Context instead. I left the company before the app went live but from what I've heard they haven't had any issues.

so you'd have

using (CustomTS.New())
{
 CustomerDal.Update()
 userDal.Update()
}

CustomTS had a static method that would get the current transaction and connection.