NullifyNetwork

I was experiencing problems with SQL compact edition (complete .NET framework freezes) and found that not only is it not thread safe (fairly obvious) but it's doesn't like to even have multiple instances of itself on the same thread - it deadlocks a lot if it does.  I also found that if it runs out of instances then it unloads from memory and has to be reloaded again afterwards, so avoiding that can result in a many tens of thousands of percent performance improvement.

So here's a small class I wrote to track the connection per thread based on someone elses on the Internet (they were just creating one per thread, in this one it is creating one per thread and maintaining the lifespan of that connection to close it when the thread it was for has been closed).  I'll probably add per-connection-string pooling but figured that the important thing is the concept of the solution to the COM deadlocks and total freezing of the framework in this.

/// <summary>
/// Manages open connections on a per-thread basis
/// </summary>
public abstract class SqlCeConnectionPool
{
    private static Dictionary<int, IDbConnection> threadConnectionMap = new Dictionary<int, IDbConnection>();

    private static Dictionary<int, Thread> threadMap = new Dictionary<int, Thread>();

    /// <summary>
    /// The connection map
    /// </summary>
    public static Dictionary<int, IDbConnection> ThreadConnectionMap
    {
        get { return SqlCeConnectionPool.threadConnectionMap; }
    }

    /// <summary>
    /// Gets the connection string.
    /// </summary>
    /// <value>The connection string.</value>
    public static string ConnectionString
    {
        get;
        set;
    }

    /// <summary>
    /// Gets a connection for this thread, maintains one open one of each.
    /// </summary>
    /// <remarks>Don't do this with anything but SQL compact edition or you'll run out of connections - compact edition is not
    /// connection pooling friendly and unloads itself too often otherwise so that is why this class exists</remarks> 
        /// <returns>An open connection</returns>
    public static IDbConnection Connection
    {
        get
        {
            lock (threadConnectionMap)
            {
                //do some quick maintenance on existing connections (closing those that have no thread)
                List<int> removeItems = new List<int>();
                foreach (var kvp in threadConnectionMap)
                {
                    if (threadMap.ContainsKey(kvp.Key))
                    {
                        if (!threadMap[kvp.Key].IsAlive)
                        {
                            //close the connection
                            kvp.Value.Close();
                            removeItems.Add(kvp.Key);
                        }
                    }
                    else
                    {
                        kvp.Value.Close();
                        removeItems.Add(kvp.Key);
                    }
                }
                foreach (int i in removeItems)
                {
                    threadMap.Remove(i);
                    threadConnectionMap.Remove(i);
                }

                //now issue the appropriate connection for our current thread
                int threadId = Thread.CurrentThread.ManagedThreadId;

                IDbConnection connection = null;
                if (threadConnectionMap.ContainsKey(threadId))
                {
                    connection = threadConnectionMap[threadId];
                }
                else
                {
                    connection = new SqlCeConnection(ConnectionString);
                    connection.Open();
                    threadConnectionMap.Add(threadId, connection);
                    threadMap.Add(threadId, Thread.CurrentThread);
                }

                return connection;
            }
        }
    }
}