Given the following contact:

/// <summary>
/// Thread Safety - This is NOT a thread safe connection
/// Exception Safety - After an exception is thrown, it should be disposed and not used afterward
/// Connection Pooling - It is expected that this will be part of a connection pool
/// </summary>
public class DistributedHashTableStorageClient

I decided that I needed to really didn’t want to pass the responsibility for that to the client, and that I wanted to handle that inside my library. Here is what I came up with:

public class DefaultConnectionPool
{
    private static readonly ILog log = LogManager.GetLogger(typeof (DefaultConnectionPool));
    readonly object locker = new object();

    private readonly Dictionary<NodeEndpoint, LinkedList<PooledDistributedHashTableStorageClientConnection>> pooledConnections =
        new Dictionary<NodeEndpoint, LinkedList<PooledDistributedHashTableStorageClientConnection>>();

    public IDistributedHashTableStorage Create(NodeEndpoint endpoint)
    {
        PooledDistributedHashTableStorageClientConnection storage = null;
        lock (locker)
        {
            LinkedList<PooledDistributedHashTableStorageClientConnection> value;
            if (pooledConnections.TryGetValue(endpoint, out value) && value.Count > 0)
            {
                storage = value.First.Value;
                value.RemoveFirst();
            }
        }
        if (storage != null)
        {
            if (storage.Connected == false)
            {
                log.DebugFormat("Found unconnected connection in the pool for {0}", endpoint.Sync);
                try
                {
                    storage.Dispose();
                }
                catch (Exception e)
                {
                    log.Debug("Error when disposing unconnected connection in the pool", e);
                }
            }
            else
            {
                return storage;
            }
        }
        log.DebugFormat("Creating new connection in the pool to {0}", endpoint.Sync);
        return new PooledDistributedHashTableStorageClientConnection(this, endpoint);
    }

    private void PutMeBack(PooledDistributedHashTableStorageClientConnection connection)
    {
        lock (locker)
        {
            LinkedList<PooledDistributedHashTableStorageClientConnection> value;
            if (pooledConnections.TryGetValue(connection.Endpoint, out value) == false)
            {
                pooledConnections[connection.Endpoint] = value = new LinkedList<PooledDistributedHashTableStorageClientConnection>();
            }
            value.AddLast(connection);
        }
        log.DebugFormat("Put connection for {0} back in the pool", connection.Endpoint.Sync);
    }

    class PooledDistributedHashTableStorageClientConnection : DistributedHashTableStorageClient
    {
        private readonly DefaultConnectionPool pool;

        public PooledDistributedHashTableStorageClientConnection(
            DefaultConnectionPool pool,
            NodeEndpoint endpoint) : base(endpoint)
        {
            this.pool = pool;
        }

        public bool Connected
        {
            get { return client.Connected; }
        }

        public override void Dispose()
        {
            if(Marshal.GetExceptionCode() != 0)//we are here because of some sort of error
            {
                log.Debug("There was an error during the usage of pooled client connection, will not return it to the pool (may be poisioned)");
                base.Dispose();
            }
            else if(Connected == false)
            {
                log.Debug("The connection was disconnected, will not return connection to the pool");
                base.Dispose();
            }
            else
            {
                pool.PutMeBack(this);
            }
        }
    }
}

I think that should pretty much cover everything I need.

Thoughts?

There are evil people in this world, and some of them want access to my database. Unfortunately, they are often part of that nasty integration team and they try to integrate directly into my database. I tried beating them with clubs and lobbing arguments about letting other people mess with my implementation details, but they have been persistent. That is when I reached out to a technological solution for the problem.

I want to emphasize that this is the nuclear option and  you want to really consider it before going that route.

We are going to use NHibernate to do that, naturally. Here is how:

Which results in the following schema:

create table [tbl_-1434067361] (
   [col_287061521] INT IDENTITY NOT NULL,
   [col_4699698] INT not null,
   [col_-1966747349] NVARCHAR(255) null,
   [col_-649855325] NVARCHAR(255) null,
   [col_-649855326] NVARCHAR(255) null,
   [col_775690683] NVARCHAR(255) null,
   [col_-2127361396] NVARCHAR(255) null,
   [col_-1334581412] NVARCHAR(255) null,
   primary key ([col_287061521])
)

To make sure that we follow procedure, we are even using the naming convention of the organization! Lovely, isn’t it? It is obvious that this is my people table, right?

All arguments against this schema can be answered using: “It is more secured”.

It might be the nuclear option, but it tend to work :-)

Nitpicker corner: No, I don’t suggest you would do this, that is why the code is an image. This is just an example of how to programmatically modify the configuration and mapping. I only used it once, during a demo to the integration people, to get them to leave my DB alone. If you push something like that for production you should probably be shot.