A queue to optimize database connections to your SQL Server

The Problem

As it probably happend to a lot of us, I stumbled upon a database issue.

While doing load testing on my application, it happened I had to many concurrent calls from my users and neither of the classical approaches didn't work such as:

  • Having a Singleton/unique DataContext opened all the time that would do the operations to the database.
    Problem: when too many concurrent calls come at the server at the same time, the DataContext get confused and ends up triggering this exception:

The operation cannot be performed during a call to SubmitChanges

  • Open a DataContext for every client call.
    Problem: DataContext is way too slow to open and close every time.
  • Execute queries with a classic Singleton/unique SQLConnection.
    Problem: A single SQLConnection will not handle parallel calls and would throw the following exception:

executenonquery requires an open and available connection. The connection's current state is open.

  • Open a classic SQLConnection for every client call.
    Problem: You can easily reach the maximum amount of connections to your database

The Idea

The idea, suggested by a friend, Roland Grießer, was to create a queue of connections, a pile that would use an open and available connection when required, and, if all the connections are in use, create a new one in the pile, on demand. That sounds simple and clever, and would surely solve the problem somehow.

Connection Queue

As the connections need to be opened by successing client calls, the queue use available connection or creates a new one, optimizing the amount of simultaneous connections to the server

The Features

Ok this small development sounds easy to make, be here are some challenging features to include:

  • Generic types
    Any kind of type can be used. It can be System.Data.SqlClient.SqlConnection, System.Data.Linq.DataContext or even any other one.
  • Async
    The database related method should be awaitable (or not).

 

The Code

Enough talking, here is the code of this connection queue. It can be used by calling it this way

                // Use only one queue in you application
                var SingletonQueue = new DBConnectionQueue<DatabaseDataContext>("yourstandardSQLconnectionstring");

                // call the ExecuteAsyncDBMethod by passing a delegate created on the fly:
                await SingletonQueue.ExecuteAsyncDBMethod(new DBConnectionQueue<DatabaseDataContext>.DBMethod((DatabaseDataContext DB) =>

                {
                    var newClient = new Client()
                    {
                        name = "Phoebe Coeus",
                        representative = "Aurel",
                        clientsince = DateTime.Now,
                        website = "phoebecoeus.net"
                    };
                    DB.Clients.InsertOnSubmit(newClient);
                    DB.SubmitChanges();
                }));
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace PhoebeCoeus.Data.Helper
{

    /// <summary>
    /// A DB Connection queue to instanciate and use as a unique pile of connections to the database. Once instanciated use it by calling the ExecuteAsyncDBMethod method.
    /// </summary>
    /// <typeparam name="DBConnectionType">Your own connection to a Database. Must inherits IDisposable and have a constructor with a single string parameter (ConnectionString). This has benn made to work with System.Data.SqlClient.SqlConnection and System.Data.Linq.DataContext.
    /// </typeparam>

    public class DBConnectionQueue<DBConnectionType> 
        : IDisposable where DBConnectionType : IDisposable
    {
        /// <summary>
        /// Delegate used to run your command with the given connection
        /// </summary>
        /// <param name="RefDBConnection">reference to the open database connection</param>
        public delegate void DBMethod(DBConnectionType RefDBConnection);

        #region Private members
        /// <summary>
        /// Connection string used to Create a new instance of DataContext/SqlConnection
        /// </summary>
        private string _connectionstring { get; set; }
        /// <summary>
        /// Opened connections
        /// </summary>
        private List<DBConnectionType> _openconnections { get; set; }
        /// <summary>
        /// List of foo lockers used to lock access to DB connections
        /// </summary>
        private List<LockerWrapper<bool>> _lockers { get; set; }
        /// <summary>
        /// foo locker
        /// </summary>
        private volatile object _mainlocker = true;
        /// <summary>
        /// Maximum amount of open connections.
        /// </summary>
        private int? maxlength = null;
        /// <summary>
        /// Timeout duration of the DatabaseExecution Task to run in milliseconds.
        /// </summary>
        private int timeout = 20000;
        #endregion

        /// <summary>
        /// Creates a Connection queue with the given ConnectionString. No maximum amount of open connections.
        /// </summary>
        /// <param name="ConnectionString">your usual connection string</param>
        public DBConnectionQueue(string ConnectionString)
            : this(ConnectionString, null) { }

        /// <summary>
        /// Creates a Connection queue with the given ConnectionString and the given maximum amount of open connections.
        /// </summary>
        /// <param name="ConnectionString">your usual connection string</param>
        /// <param name="MaximumConnections">maximum amount of simultaneous open connections</param>
        public DBConnectionQueue(string ConnectionString, int? MaximumConnections)
        {
            this._connectionstring = ConnectionString;
            this.maxlength = MaximumConnections;
            _openconnections = new List<DBConnectionType>();
            _lockers = new List<Data.LockerWrapper<bool>>();
        }

        /// <summary>
        /// Get the first available open DB connection from the list, or creates (and open) a new one if no available.
        /// </summary>
        /// <returns>returns a DBConnectionSlot, with a reference to the available and open DB connection</returns>
        public DBConnectionSlot<DBConnectionType> GetConnectionSlot()
        {
            // use the main locker to ensure no concurrent calls.
            lock (_mainlocker)
            {
                for (var i = 0; i < _openconnections.Count; i++)
                {
                    // verify the nth locker. if false, the DB connection is not in use.
                    if (_lockers[i].Value == false)
                    {
                        // retrieve the matching locker...
                        LockerWrapper<bool> availablelocker = this._lockers[i];
                        //... and make it locked
                        availablelocker.Value = true;
                        DBConnectionType availableConnection = this._openconnections[i];
                        // returns a DBConnectionSlot instance, including a reference to your available DB connection
                        return new DBConnectionSlot<DBConnectionType>(ref availableConnection, ref availablelocker, i);
                    }
                }

                // not a single unlocked connexion available. 
                if (this.maxlength.HasValue && this._openconnections.Count >= this.maxlength.Value)
                    throw new Exception("Maximum amount of open connections has been reached");

                // Create a already locked (true) locker
                _lockers.Add(new Data.LockerWrapper<bool>(true)); 
                // Add a new instance of DB Connection.
                _openconnections.Add(GetDBConnectionInstance());

                var newlocker = this._lockers.Last();
                var newconnection = this._openconnections.Last();
                if (newconnection is System.Data.SqlClient.SqlConnection)
                    (newconnection as System.Data.SqlClient.SqlConnection).Open();
                // returns a DBConnectionSlot instance, including a reference to your available DB connection
                return new DBConnectionSlot<DBConnectionType>(ref newconnection, ref newlocker, this._lockers.Count - 1);
            }
        }

        /// <summary>
        /// Uses Reflections to instanciate a new [DBConnectionType] object. 
        /// Will look for a constructor with a single string parameter (connectionstring). 
        /// This matches System.Data.SqlClient.SqlConnection and System.Data.Linq.DataContext
        /// </summary>
        /// <returns>Returns a new instance of your [DBConnectionType] object</returns>
        private DBConnectionType GetDBConnectionInstance()
        {
            try
            {
                Type myType = typeof(DBConnectionType);
                Type[] constructorparamtypes = new Type[] { typeof(string) };
                // Get the public instance constructor that takes a single string parameter (connection string then).
                System.Reflection.ConstructorInfo constructorInfoObj = myType.GetConstructor(
                    System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public, null,
                    System.Reflection.CallingConventions.HasThis, constructorparamtypes, null);
                if (constructorInfoObj != null)
                {
                    // instanciate constructor.
                    object newDataContext = constructorInfoObj.Invoke(new string[] { this._connectionstring });
                    return (DBConnectionType)newDataContext;
                }
                else
                {
                    throw new Exception("The constructor of DataContextType is not available.");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Exception during the retreival of : DataContextType's constructor" + e.Message, e);
            }
        }

        /// <summary>
        /// Run your delegate DB Method in a parralel thread, with a new or available and open DB Connection.
        /// </summary>
        /// <param name="MethodToRun">Your method to execute, with a reference to the DB Connection</param>
        /// <returns>Return the task to be executed</returns>
        async public System.Threading.Tasks.Task ExecuteAsyncDBMethod(DBMethod MethodToRun)
        {
            // Get the first available open DB connection from the list, or creates a new one.
            using (var AvailableDBSlot = GetConnectionSlot())
            {
                try
                {
                    // await the DB Method given in parameter.
                    await ExecuteDBMethod(MethodToRun, AvailableDBSlot.DBConnection, this.timeout);
                }
                catch (Exception ex)
                {
                    // CATCH any exception.
                }
            }
        }

        /// <summary>
        /// private task to execute
        /// </summary>
        /// <param name="MethodToRun">Your method to execute, with a reference to an open DB Connection</param>
        /// <param name="RefDBConnection">Reference to the DB Connection</param>
        /// <param name="Timeout">Max duration (in milliseconds) to wait for the task.</param>
        /// <returns>returns a synchronous Task to run.</returns>
        private System.Threading.Tasks.Task ExecuteDBMethod(DBMethod MethodToRun, DBConnectionType RefDBConnection, int Timeout)
        {
            var asyncvoid = System.Threading.Tasks.Task.Factory.StartNew(() =>
            {
                MethodToRun(RefDBConnection);
            });
            asyncvoid.Wait(Timeout);
            return asyncvoid;
        }

        public void Dispose()
        {
            lock (_mainlocker)
            {
                for (var i = 0; i < _openconnections.Count; i++)
                {
                    (_openconnections[i] as IDisposable).Dispose();
                }
            }
        }
    }

    /// <summary>
    /// DBConnectionSlot is a shell container for your [DBConnectionType] object. Once Disposed, this DBConnectionSlot will not Dispose the DB Connection but will trigger the given locker to make it available to the DBConnectionQueue.
    /// </summary>
    /// <typeparam name="DBConnectionType">Your own connection to a Database. Must inherits IDisposable and have a constructor with a single string parameter (ConnectionString). This has benn made to work with System.Data.SqlClient.SqlConnection and System.Data.Linq.DataContext.
    /// </typeparam>
    public class DBConnectionSlot<DBConnectionType> : IDisposable
    {
        /// <summary>
        /// Reference to the DB Connection instance.
        /// </summary>
        public DBConnectionType DBConnection { get; private set; }
        /// <summary>
        /// reference to the locker. Using LockerWrapper class instead of bool, because bool is a struct and cannot be used by references.
        /// </summary>
        protected LockerWrapper<bool> locker { get; private set; }
        /// <summary>
        /// Deebug purposes, to know which connection is used.
        /// </summary>
        protected int index { get; private set; }

        /// <summary>
        /// Instanciate this Slot with references to the DBConnection and its locker (DBConnectionQueue).
        /// </summary>
        /// <param name="RefDBConnection">Reference to the open DB Connection</param>
        /// <param name="refLocker">Reference to the DB Connection's locker.</param>
        /// <param name="index">Number of this DB Connection in the queue</param>
        public DBConnectionSlot(ref DBConnectionType RefDBConnection, ref LockerWrapper<bool> refLocker, int index)
        {
            this.DBConnection = RefDBConnection;
            this.locker = refLocker;
            this.index = index;
        }

        public void Dispose()
        {
            // do not dispose the DB Connection, simply release the locker;
            this.locker.Value = false;
        }
    }

    // http://stackoverflow.com/questions/1434840/c-copy-one-bool-to-another-by-ref-not-val
    public class LockerWrapper<T> where T : struct
    {
        public T Value { get; set; }
        public LockerWrapper(T value) { this.Value = value; }
    }
}

		

Now what's next?

Here are the next steps this code should get:

  • Handle the maximum length.
    Once the maximum amout of simultaneous connections is reached, make the Task wait until one is available instead of throwing an Exception.
  • Give the dynamic type as a constructor.
    Here the DB Connection is instanciated through Reflexion, looking for a "ConnectionString constructor". It would be great to let the user give the constructor he wants as a DBConnectionQueue constructor parameter

Aurelien Jacquot

Leave a Reply

Your email address will not be published.