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

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

ADO.NET SQL Server provider does the connection pooling itself. You can control the pool size by MinPoolSize and MaxPoolSize in the connection string.

You really shouldn't be handling this problem yourself, as there are countless tools out there that can do it for you.

If you really want to do it yourself, then look into the Unit of Work pattern where you can manage connection/transaction lifecycle. You certainly don't want to try to navigate the messy waters where there are connections being opened/closed in different places.

If you decide to let your components directly open db connections then it is likely that the connection lifecycle will be too fine-grained and result in many open/closed connections for a single user operation.