A few days ago I made a post about using SQL CE 3.5 with LINQ to SQL. I described a way to use connection pooling with SQL CE. A gracious blog reader (Mike Brown) pointed out a way I could make my solution much simpler by using the [ThreadStatic] attribute. I never heard of this attribute but it is really nifty. You mark a field with it and then each thread that accesses that field will be accessing a unique instance of it!
Using this attribute my code for connection pooling went from this:
/// <summary> /// Manages open connections on a per-thread basis /// </summary> public class ConnectionPool { private Dictionary<int, IDbConnection> threadConnectionMap; /// <summary> /// Gets the connection string. /// </summary> /// <value>The connection string.</value> public string ConnectionString { get; private set; } /// <summary> /// Gets a connection. /// </summary> /// <returns>An open connection</returns> public IDbConnection Connection { get { lock (threadConnectionMap) { int threadId = Threading.Thread.CurrentThread.ManagedThreadId; IDbConnection connection = null; if (threadConnectionMap.ContainsKey(threadId)) { connection = threadConnectionMap[threadId]; } else { connection = new SqlCeConnection(ConnectionString); connection.Open(); threadConnectionMap.Add(threadId, connection); } return connection; } } } /// <summary> /// Initializes a new instance of the <see cref="ConnectionPool"/> class. /// </summary> /// <param name="connectionString">The connection string.</param> public ConnectionPool(string connectionString) { threadConnectionMap = new Dictionary<int, IDbConnection>(); ConnectionString = connectionString; }
to this:
/// <summary> /// Manages open connections on a per-thread basis /// </summary> public class ConnectionPool { [ThreadStatic] private static IDbConnection threadConnection; /// <summary> /// Gets the connection string. /// </summary> /// <value>The connection string.</value> public string ConnectionString { get; private set; } /// <summary> /// Gets a connection. /// </summary> /// <returns>An open connection</returns> public IDbConnection Connection { get { if (threadConnection == null) { threadConnection = new SqlCeConnection(ConnectionString); threadConnection.Open(); } return threadConnection; } } /// <summary> /// Initializes a new instance of the <see cref="ConnectionPool"/> class. /// </summary> /// <param name="connectionString">The connection string.</param> public ConnectionPool(string connectionString) { ConnectionString = connectionString; } }
Very nice and clean.
Thanks Mike!