A question about ConnectionFactory

Nov 10, 2009 at 5:52 PM

 

Hello! 
When reviewing the code, the first question I run into: why do you use ConnectionFactory? 
As far as I remember the basics, ADO.NET (SqlClient) supports connection pooling. This means that there already exists the factory class for connection management. I used to use everywhere in my DAL classes the following snippet:
using (SqlConnection conn = new SqlConnection(_connStr))
{
  // some logic goes here
} // the connection released back to the pool at this point
Such a strategy "catch - use - release" allows to serve 100 clients with 10 connections in the pool.
Your ConnectionFactory (singleton) catches the connection and keeps it until the application ends. This way "catch - keep" allows to serve 10 clients with 10 connections in the pool.

Hello! 

When reviewing the code, the first question I run into: why do you use ConnectionFactory? 

As far as I remember the basics, ADO.NET (SqlClient) supports connection pooling. This means that there already exists the factory class for connection management. I used to use everywhere in my DAL classes the following snippet:

using (SqlConnection conn = new SqlConnection(_connStr))

{

  // some logic goes here

} // the connection released back to the pool at this point

Such a strategy "catch - use - release" allows to serve 100 clients with 10 connections in the pool.

Your ConnectionFactory (singleton) catches the connection and keeps it until the application ends. This way "catch - keep" allows to serve 10 clients with 10 connections in the pool.

 

Coordinator
Nov 10, 2009 at 6:03 PM

Hey ilog,

I use a ConnectionFactory because not all Ado.Net providers use connection pooling.  More importantly, none of the providers I was using when I started this project use Connection Pooling, nor will they for another decade or so. For the same reason I did not add stored procedure support until much later.  Stored Procs are (still) not supported in the databases I am using.  But I added support for stored procs once it was requested.

Examples of these are SQL Server CE, SQLite, and UltraLight. All of these are designed to run on mobile devices, and connection pooling is too expensive for them in that context.  So I don't have connection pooling there, and creating new connections all the time is expensive.

Now, for SQL Server, while I still use the Connection Factory, it does not use the same connection object (see ConnectionFactory.GetConnection).  A new connection object is created each time you create a command.  The connection object is only kept alive for the life of the FluentCommand instance.

So for that database I am relying on connection pooling.  As a side benefit, you also get a centralized connection creator out of it.  The downside is it is more work when you are working with multiple databases.

Nov 10, 2009 at 7:20 PM

Chris, 

Thanks for the explanations. Sorry, but I see neither _command.Connection.Close() in Dispose() method, nor CommandBehavior.CloseConnection in ExecuteReader(). So, I am not sure that connection is closed when command is destroyed.

BTW, Microsoft recommends using CommandBehavior.CloseConnection when you return a SqlDataReader from a function.

Coordinator
Nov 10, 2009 at 9:07 PM

No problem. I always appreciate a chance to look back and make sure I did something correctly.  Also, I did have some issues trying to support multiple databases.  Each provider is subtly different, which can be problematic.

Obviously, in SQL CE and SQLite, I don't want to close the connect after each command (do to lack of connection pooling again), but that is not the database we are talking about here.

What I have done in with my commands, is that in all the methods that execute the command, the command object is wrapped in a 'using' statement, which is supposed to close out the connection.  What I might have missed was that flag you listed above.  I'm going to have to double check how the SqlCommand destructor works.

Feb 2, 2011 at 12:21 PM

The main problem wit the ConnectionFactory is not closing it, but applying it without rewriting it (and add the source to my code)...

So here is a really lazy singleton ConnectionFactory implementation allowing a per thread (or per Session) connection storing/retriewing... There's also a way to get and create a non-standard connection, but I didnt checked it out, if it could be somehow usefull for this project without making greater changes to the source...

Instead of calling: 

ConnectionFactory.GetConnection();
you have to call ConnectionFactory.Instance.DefaultConnection and you will have to set Once the DefaultConnection string on the ConnectionFactory.
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Remoting.Messaging;
using System.Web;

namespace FluentAdo.SqlServer
{
    /// <summary>
    /// A ConnectionFactory for a per thread or per session IDbConnection management.
    /// </summary>
    public class ConnectionFactory
    {

        #region Thread-safe, lazy Singleton

        /// <summary>
        /// This is a thread-safe, lazy singleton.  See http://www.yoda.arachsys.com/csharp/singleton.html
        /// for more details about its implementation.
        /// </summary>
        public static ConnectionFactory Instance
        {
            get
            {
                return Nested.Instance;
            }
        }

        /// <summary>
        /// Assists with ensuring thread-safe, lazy singleton
        /// </summary>
        private class Nested
        {
            static Nested() { }
            internal static readonly ConnectionFactory Instance = new ConnectionFactory();
        }

        #endregion

        private const string COLLECTION_KEY = "$$ConnectionFactory.DictionaryCollection$$";
        private const string DEFAULT_COLL_ITEM = "$$ConnectionFactory.DefaultItemPerThreadOrSession$$";

        /// <summary>
        /// Connection string for the underlying connection's.
        /// </summary>
        public string DefaultConnectionString { get; set; }

        protected Hashtable GetFactoryDictionary
        {
            get
            {
                if (IsInWebContext())
                {
                    if (HttpContext.Current.Items[COLLECTION_KEY] == null) HttpContext.Current.Items[COLLECTION_KEY] = new Hashtable();
                    return (Hashtable)HttpContext.Current.Items[COLLECTION_KEY];
                }
                else
                {
                    if (CallContext.GetData(COLLECTION_KEY) == null) CallContext.SetData(COLLECTION_KEY, new Hashtable());
                    return (Hashtable)CallContext.GetData(COLLECTION_KEY);
                }
            }
        }

        protected static bool IsInWebContext()
        {
            return HttpContext.Current != null;
        }

        protected IDbConnection GetNewConnectionAndStoreWithKey(string key, string connectionString)
        {
            var idbConn = new SqlConnection(connectionString);
            GetFactoryDictionary.Add(DEFAULT_COLL_ITEM, idbConn);
            return idbConn;
        }

        protected IDbConnection this[string key]
        {
            get
            {
                if (GetFactoryDictionary.ContainsKey(key))
                {
                    return (IDbConnection)GetFactoryDictionary[key];
                }
                else
                {
                    return GetNewConnectionAndStoreWithKey(key, DefaultConnectionString);
                }
            }
        }

        public IDbConnection DefaultConnection
        {
            get
            {
                return this[DEFAULT_COLL_ITEM];
            }
        }

        public IDbConnection CreateConnection(string key, string connectionString)
        {
            return GetNewConnectionAndStoreWithKey(key, connectionString);
        }

        public IDbConnection GetConnection(string key)
        {
            return (IDbConnection)GetFactoryDictionary[key];
        }
    }
}