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 DAL's 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.

Answer 2

Implement it in a similar idea on how TransactionContext works.

I started writing how to do this, but I think an example makes it clearer:

public class MyConnectionManager : IDisposable
{
    [ThreadStatic] // static per thread
    private static SqlConnection con;

    public static SqlConnection Connection
    {
        get
        {
            if (con == null)
            {
                con = new SqlConnection();
                con.Open();
            }
            return con;
        }
    }

    public void Dispose()
    {
        if (con != null)
        {
            con.Close();
        }
    }
}

public class Program
{
    public void Run()
    {
        using(new MyConnectionManager())
        {
            MakeCall();
            MakeCall();
        } // Disposal happens here
    }

    public void MakeCall()
    {
        // The property can be accessed from anywhere
        SqlCommand cmd = new SqlCommand("SELECT 1", MyConnectionManager.Connection);
    }
}

This will cause any calls within the scope of the using statement to use the same connection, as long as they all run on the same thread. You can throw a transaction context in there as well.

Caveat: In an ASP.NET WebPages scenario the thread can change between connection and page_load. See this article for more info. Also the thread is re-used. However if you are developing a DAL that is shared among applications, you may not be able to use the HttpContext, as mentioned, as there is none.