A ConnectionScope class. [Alazel Acheson]


I’ve heard a few comments from people who would like an easier way to manage connection lifetime & use across multiple methods. Most often, the problem is due to using a TransactionScope at an higher level, but opening and closing connections inside the methods – generally resulting in a distributed transaction unless you manually move a single connection around. For example:


void OuterMethod() {
    using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, opts)) {
        InnerMethod(“select * from testtable”);
        InnerMethod(“update testtable set col1 = N’new value'”);
        tx.Complete();
    }
}


static void InnerMethod(string sqlText) {
    using (SqlConnection conn = SqlConnection(connStr)) {
        conn.Open();
        SqlCommand cmd = conn.CreateCommand();
        cmd.ExecuteNonQuery();
    }
}



To avoid the distributed transaction, you would need to create the connection in the OuterMethod and pass it in as a parameter (somewhat tedious) or assign it to a member variable (somewhat risky, as you are then probably maintaining a reference to the connection beyond it’s intended lifetime).


I’ve implemented a simple scope class for db connections that can simplify the process (see the attached file).  Feel free to use this class directly or modify it as needed.


To use it, simply create a new DbConnectionScope in the OuterMethod and follow one of the two patterns for getting your connection to the inner scope:



  1. Create, open and place your connection into the scope prior to use with AddConnection (generally the OuterMethod), assigning it a key for identification. In the InnerMethod, pull it out using GetConnection and assign it to your command before executing.
  2. Use GetOpenConnection() in the InnerMethod and the scope will construct & open your connection as needed, using the connection string as the key.

The example, re-written using the second pattern, looks like this:


void OuterMethod() {


    using (TransactionScope tx = new TransactionScope(TransactionScopeOption.RequiresNew, opts)) {


        using (DbConnectionScope db = new DbConnectionScope()) {


            InnerMethod(“select * from testtable”);


            InnerMethod(“update testtable set col1 = N’new value'”);


            tx.Complete();


        }


    }


}


 


static void InnerMethod(string sqlText) {


    SqlCommand cmd = new SqlCommand();


    cmd.Connection = (SqlConnection) DbConnectionScope.Current.GetOpenConnection(SqlClientFactory.Instance, connStr);


    cmd.ExecuteNonQuery();


}


This class is only something you’d want to use only if you specifically want to re-use the same open connection – the connection pool does a much better job of handling connection re-use when your logic allows for the connection being reset, and you don’t need any particular state associated with it. You also need to keep in mind the problems that using the same connection can cause – for example, only one command executing at a time if MARS is not on.


Disclaimer: This posting is provided “AS IS” with no warranties, and confers no rights


 


Updated: Fixed a glaringly simple bug in the Dispose() method.


Updated: Missed one other bug, now fixed.

DbConnectionScope.cs

Comments (20)

  1. DonM says:

    This looks like a perfect answer to a problem I’m solving with our web site. I do have a question. The empty loop looks like a potentially infinite loop, for example, if _priorScope is disposed. Your code at line 124 is:

    DbConnectionScope prior = _priorScope;

                       while (null != prior && _priorScope.IsDisposed) {

                           // Intentionally empty loop

                       }

    I think it should be

    DbConnectionScope prior = _priorScope;

                       while (null != prior && prior.IsDisposed) {

    prior = prior._priorScope; // Try the next nested scope.

                       }

    Do you agree or is there something I’m missing?

  2. MSDNArchive says:

    You are absolutely correct, the loop shouldn’t be empty. Serves me right for anticipating a problem, then not actually testing my solution. 🙂

    Thanks for catching this — I’ll patch the class and update the attachment.

  3. DonM says:

    Using your ConnectionScope class as a starting point I’ve solved a bunch of problems for our web project.

    I’m building a typical web site using a single database and a common connection pool for all users. I have a DataAccess static class based on the MS Data Access Application Block published for .Net 1.1. It has a static connection string so all requests use the same connection string and connection pool.

    When saving a single business object, autocommit works fine and life is simple. When saving multiple business objects, I need to have them all work or all roll back. I don’t want to consume db connections; I want everything from a single request to use no more than one connection.

    I removed your dictionary of connections and have only a single connection reference (which can be null). In my DataAccess class, I check to see if I’m in a ConnectionScope. If I am, I try to get a connection from it. If it doesn’t have one, I create a connection and set the ConnectionScope’s Connection property with it so it’s available later to be resused. I use the connection for the requested query.

    When a new ConnectionScope is created, it checks to see if it is nested. If so, it copies the Connection reference from the outer scope so it will be reused without consuming another connection. Null does no harm.

    When a nested ConnectionScope is disposed, if it has a Connection and the new current ConnectionScope doesn’t have one, the disposing nested scope gives its connection to the new (outer) current scope, again so the same connection is reused. This handles the case of (1) Create scope1; (2) Create scope2; (3) Do Query3; (4) End scope2; (5) Do Query4; (6) End Scope1. Query4 will use the same connection created by Query3.

    When the last ConnectionScope is disposed (having no parent), it disposes the Connection it has (if it has one). This ensures the connection gets closed, returned to the pool, and can be reused for another request.

    Thanks for publishing ConnectionScope which showed me an elegant way of transactionfying my application without forcing the application code to deal with database connections or database transactions.

  4. MSDNArchive says:

    I’m glad to hear it’s useful to you!  That always makes my day. 🙂  It sounds like you’ve come up with a nice customization to match your particular needs.

    There were actually a couple of enhancements I have been thinking about, but didn’t implement due to time and trying to keep the idea mostly simple.  One was scope nesting options similar to System.Transactions.TransactionScopeOptions.  A "Requires" option would also handle the basics of your scenario, where the inner scope would not even hook itself into the chain upon detecting the presense of an outer scope.

  5. jherbst says:

    Thanks for this usefull class!

    I als need transactions over more then one business object and with TransactionScope() I found a way to simply implement it in .Net 2.0.

    With your class I thought I can reuse one connection over more then one dataoperations but it seems there are a few points we have to take care:

    -) The connection need to be created outside (!) the first transactionscope.

    As soon as you try to create the connection inside the transactionscope-statement ADO.Net tries to use MSDTC (and if you turn of MSDTC-Service you get a exception on result.Open()-statement)!

    In this following sample it is working without using MSDTC:

    DBDataAccessLayer da = DataAccessLayerHelper.GetDBLayer();

    using (DbConnectionScope dbScope = new DbConnectionScope()) {

    DbConnectionScope.Current.GetOpenConnection(da.DBProvider, da.ConnectionStringSettings.ConnectionString);

    using (TransactionScope scope = new TransactionScope()) {

    // some BLL operations

    scope.Complete();

    }

    }

    -) I did some change on your source-code do support nested DBConnectionScope-statements.

    With these changes I am able to reuse an open connection and it is guaranteed that there is only one connection open to the database inside the top spanning DB-scope:

    #region class fields

    [ThreadStatic()]

    private static DbConnectionScope __currentScope = null;      // Scope that is currently active on this thread

    private static Object __nullKey = new Object();   // used to allow null as a key

    // jherbst: moved to static because this component should reuse an open connections from stack

    private static Dictionary<object, DbConnection> _connections;   // set of connections contained by this scope.

    #endregion

    #region instance fields

    private DbConnectionScope _priorScope;    // previous scope in stack of scopes on this thread

    #endregion

    public DbConnectionScope() {

    // Devnote:  Order of initial assignment is important in cases of failure!

    //  _priorScope first makes sure we know who we need to restore

    //  _connections second, to make sure we no-op dispose until we’re as close to

    //      correct setup as possible

    //  __currentScope last, to make sure the thread static only holds validly set up objects

    _priorScope = __currentScope;

    // jherbst: only if _connections is null a new dictionary object should be created.

    if (_connections == null)

    _connections = new Dictionary<object, DbConnection>();

    __currentScope = this;

    }

    public void Dispose() {

    if (!IsDisposed) {

    // Firstly, remove ourselves from the stack (but, only if we are the one on the stack)

    //  Note: Thread-local _currentScope, and requirement that scopes not be disposed on other threads

    //      means we can get away with not locking.

    if (__currentScope == this) {

    // In case the user called dispose out of order, skip up the chain until we find

    //  an undisposed scope.

    DbConnectionScope prior = _priorScope;

    while (null != prior && _priorScope.IsDisposed) {

    prior = prior._priorScope;

    }

    __currentScope = prior;

    }

    // jherbst:

    // added check to see if __currentscope is null (the last scope on the stack)

    // if so, dispose all and close the open connections

    if (__currentScope == null) {

    // secondly, make sure our internal state is set to "Disposed"

    IDictionary<object, DbConnection> connections = _connections;

    _connections = null;

    // Lastly, clean up the connections we own

    foreach (DbConnection connection in connections.Values) {

    connection.Dispose();

    }

    }

    }

    }

    May it make sense to inlude some  kind of TransactionScopeOptions like you mention above to be able to open a new connection inside a DBScope.

  6. MSDNArchive says:

    One thing you need to watch out for with the changes you’ve made is that the class is no longer safe to use in a multi-threaded app.  By making the dictionary a static field, all threads using a DbConnectionScope within the appdomain will attempt to use the same instance of dictionary.  Dictionaries and SqlConnections are not thread safe for general use, so you can easily end up corrupting their state.  Even if you add a lock on the dictionary while accessing it, you end up having difficult-to-understand semantics about WHEN the connections are closed (__currentScope is still a thread static, so you have multiple stacks of scopes, each of which wants to close the connections when the last one in it’s stack is disposed).

    Remember to be wary when adding static fields to any code!

  7. jherbst says:

    Thanks for your comment, you are right!

    I just changed the DBConnectionscope class so that it will use a stack object to manage nested ConnectionScopes better (this stack object is stored ThreadStatic).

    The connection-object is now a instance variable again so there should be no issue with multithreaded access.

  8. In two previous posts, I told how great the TransactionScope of the System.Transactions namespace is….

  9. Hi Alazel,

    Thank you for a great class.  I am currently working on a similar class that will scope both Open Connection and Transaction at the same time – basically combine the two ‘using’ statements into one.  I have several questions about ADO.NET and DbConnectionScope:

    1.  What are the disadvantages of distributed transaction over multiple open connections versus one transaction over a single connection?  Is there a big performance hit?

    2.  I know .NET framework pools open connections, so does keeping an open connection still make an application more efficient?

    For example is there a big difference between:

    DbConnection cn1=CreateOpenCn();

    RunSomeQuery1(cn1);

    cn1.Close()

    DbConnection cn2=CreateOpenCn();

    RunSomeQuery2(cn2);

    cn2.Close();

    VS

    DbConnection cn=CreateOpenCn();

    RunSomeQuery1(cn);

    RunSomeQuery2(cn);

    cn.Close();

    3.  Is there a big difference between nesting multiple TransactionScopes vs re-using the same TransactionScope, assuming both use the same open connection.  Theoretically it should be pretty much the same.  I usually like to re-use whatever objects I already instantiated, but not sure if in this case it is worth the effort.

    4. In the new version of your DbConnectionScope class, in what cases you would use options ‘NewRequired’ and ‘Suppress’?   I tried to think of cases when I would not want to re-use already open connection, but could not think of any.  

    Thanks ahead for your time.

    Best Regards

  10. Jenser says:

    Hi Alazel,

    Nested connection scope is merged in this code?

  11. Remy says:

    I must say I’m extremely disappointed in this LTM limitation.  The instant I read about DTC promotion, I wondered whether the System.Transactions would be smart enough to create a subpool bound to the ‘lightweight’ transaction.  Apparently not.  This basically renders the LTM useless to anything but  the degenerate case of one object, where I might just as easily use a SqlTransaction.  Jeez…

  12. It seems that this class isn’t thread-safe or maybe I’m doing something wrong here:

    class Program

       {

           static void Main(string[] args)

           {

               using (DbConnectionScope conn = new DbConnectionScope())

               {

                   (new Thread(RunQuery)).Start();

                   (new Thread(RunQuery)).Start();

               }

           }

           private static void RunQuery()

           {

               string ConnectionString = ConfigurationManager.AppSettings["DBConnection"];

               SqlCommand cmd = new SqlCommand("Select * from E_Products");

               cmd.Connection = (SqlConnection)DbConnectionScope.Current.GetOpenConnection(SqlClientFactory.Instance, ConnectionString);

               DataTable table = new DataTable();

               DateTime start = DateTime.Now;

               using (SqlDataReader reader = cmd.ExecuteReader())

               {

                   table.BeginLoadData();

                   table.Load(reader);

                   table.EndLoadData();

               }

               TimeSpan span = DateTime.Now – start;

               Console.WriteLine("Loading time: " + span.Milliseconds);

               Console.WriteLine("Rows count: " + table.Rows.Count);

           }

       }

    Running this code will result with "NullReferenceExpcetion" on the GetOpenConnection line.

    Am I missing something here ?

  13. meek says:

    The ADO.NET Entity Framework (EF) allows you to map stored procedures to functions that return typed

  14. Florin Lazar says:

    Great news! The new updates added to System.Data and SQL Server 2008 finally allow multiple Open/Close

  15. VS2010学习 says:

    You can reuse the connection on an ObjectContext to create a store command as follows: using (MyContext