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

• Anonymous
  February 24, 2006
  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?

• Anonymous
  February 24, 2006
  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.

• Anonymous
  February 26, 2006
  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.
  
  

• Anonymous
  February 27, 2006
  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.

• Anonymous
  March 03, 2006
  The comment has been removed

• Anonymous
  March 08, 2006
  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!
  

• Anonymous
  March 09, 2006
  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.
  

• Anonymous
  March 20, 2006
  In two previous posts, I told how great the TransactionScope of the System.Transactions namespace is....

• Anonymous
  April 08, 2006
  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

• Anonymous
  April 10, 2006
  PingBack from http://xlib.wordpress.com/2006/04/10/connectiontransaction-scope/

• Anonymous
  May 02, 2006
  Hi Alazel,
  
  Nested connection scope is merged in this code?

• Anonymous
  June 10, 2006
  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...
  

• Anonymous
  June 21, 2006
  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 ?
  

• Anonymous
  March 26, 2008
  The ADO.NET Entity Framework (EF) allows you to map stored procedures to functions that return typed

• Anonymous
  April 16, 2008
  PingBack from http://blog.tonysneed.com/?p=63

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

• Anonymous
  December 21, 2008
  PingBack from http://sql-tidbits.lincoln.se/2008/12/21/how-to-keep-a-transaction-from-being-escalated-to-thedtc/

• Anonymous
  May 31, 2009
  PingBack from http://outdoorceilingfansite.info/story.php?id=5159

• Anonymous
  June 09, 2009
  You can reuse the connection on an ObjectContext to create a store command as follows: using (MyContext