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?