SQL CE 3.5 with LINQ to SQL Revisited
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 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!
I am a software developer at Microsoft. My blog is http://blogs.msdn.com/matt
