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 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!

This entry was posted in LINQ to SQL, SQL CE. Bookmark the permalink.

2 Responses to SQL CE 3.5 with LINQ to SQL Revisited

  1. Pingback: LINQ to SQL - SQL CE 3.5 with LINQ to SQL - Matthew Manela - Farblondzshet in Code

  2. aurimas86 says:

    nowadays ThreadLocal looks even neater

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>