Best practices re sharing IDbConnection or connection string/factory in your .Net code

Question

I'm wondering what would be the best prectice regarding mainataining connections to the database in .Net application (ADO.NET but I guess the practice should be the same for any data layer). Should I create a database connection and propagate it throughout my application, or would it be better to just pass connection strings/factories and create a connection ad-hoc, when it is needed.

As I understand perfomance hit is not signifcant with pooling and it allows me to recover from broken connections quite easily (just a new connection will be created) but then again a connection object is a nice, relatively high-level abstraction and creating a new connection for every operation (not SQL command, but application operation) generates additional, duplicated code and feels like a waste of time/resources(?).

What do you think about these 2 cases, what are their cons/pros and which approach are you using in your real-life applications?

Thanks

Answer 1

I found myself needing to pass around a connection object so I could allow several business objects to save themselves to the database inside a single transaction.

If each business object had to create its own SQLConnection to the database, the transaction would escalate to a distributed transaction and I wanted to avoid that.

I did not like having to pass the SQLConnection object as a parameter to save an object, so I created a ConnectionManager that handles creating the SQLConnection object for me, tracking the use of the SQLConnection object, and disconnecting the SQLConnection object when not in use.

Here is some code as an example of the ConnectionManager:

public class ConnectionManager: IDisposable
{
    private ConnectionManager instance;

    [ThreadStatic]
    private static object lockObject; 
    private static Object LockObject
    {
        get
        {
            if (lockObject == null)
                lockObject = new object();
            return lockObject;
        }
    }

    [ThreadStatic]
    private static Dictionary<string, ConnectionManager> managers;
    private static Dictionary<string, ConnectionManager> Managers
    {
        get
        {
            if (managers == null)
                managers = new Dictionary<string, ConnectionManager>();
            return managers;
        }
    }

    private SqlConnection connection = null;
    private int referenceCount;
    private string name;


    public static ConnectionManager GetManager(string connectionName)
    {
        lock (LockObject)
        {
            ConnectionManager mgr;
            if (Managers.ContainsKey(connectionName))
            {
                mgr = Managers[connectionName];
            }
            else
            {
                mgr = new ConnectionManager(connectionName);
                Managers.Add(connectionName, mgr);
            }

            mgr.AddRef();
            return mgr;
        }
    }

    private ConnectionManager(string connectionName)
    {
        name = connectionName;
        connection = new SqlConnection(GetConnectionString(connectionName));
        connection.Open();
    }

    private string GetConnectionString(string connectionName)
    {
        string conString = Configuration.ConnectionString;
        return conString; 
    }

    public SqlConnection Connection
    {
        get { return connection; }
    }

    private void AddRef()
    {
        referenceCount += 1;
    }

    private void DeRef()
    {
        lock (LockObject)
        {
            referenceCount -= 1;
            if (referenceCount == 0)
            {
                connection.Dispose();
                Managers.Remove(name);
            }
        }
    }

#region IDisposable Members

    public void Dispose()
    {
        Dispose(true);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (disposing)
        {
            DeRef();
        }
    }

    ~ConnectionManager()
    {
        Dispose(false);
    }

#endregion

}

Here is how I would use it from a business object:

public void Save()
{   
    using (ConnectionManager mrg = ConnectionManager.GetManager("SQLConnectionString")
    {
        using (SQLCommand cmd = new SQLCommand)
        {
            cmd.connection = mgr.Connection
            // More ADO Code Here
        }

        _childObject.Save(); //this child object follows the same pattern with a using ConnectionManager.
    }
}

I save a business object and all of its children are saved as well using the same connection object. When the scope falls away from original parent, the using statement closes the connection.

This is a pattern I learned from Rocky Lhotka in his CSLA framework.

Keith