A ConnectionScope class. [Alazel Acheson]

Transa

Israel A — Wed, 15 Feb 2006 03:38:12 GMT

re: A ConnectionScope class. [Alazel Acheson]

DonM — Fri, 24 Feb 2006 20:42:35 GMT

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?

re: A ConnectionScope class. [Alazel Acheson]

alazela — Fri, 24 Feb 2006 22:52:29 GMT

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.

A Customization of ConnectionScope

DonM — Mon, 27 Feb 2006 00:05:06 GMT

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.

re: A ConnectionScope class. [Alazel Acheson]

alazela — Mon, 27 Feb 2006 19:34:33 GMT

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.

re: A ConnectionScope class. [Alazel Acheson]

jherbst — Sat, 04 Mar 2006 12:09:12 GMT

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.

re: A ConnectionScope class. [Alazel Acheson]

alazela — Thu, 09 Mar 2006 00:14:35 GMT

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!

re: A ConnectionScope class. [Alazel Acheson]

jherbst — Fri, 10 Mar 2006 15:05:20 GMT

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.

System.Transactions : Promotable Enlistment

Dennis van der Stelt — Tue, 21 Mar 2006 00:35:11 GMT

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

re: A ConnectionScope class. [Alazel Acheson]

Eric Popivker — Sat, 08 Apr 2006 20:52:58 GMT

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

RAD for N-Tier web apps in .NET » Blog Archive » Connection/Transaction scope

RAD for N-Tier web apps in .NET » Blog Archive » Connection/Transaction scope — Mon, 10 Apr 2006 19:57:37 GMT

PingBack from http://xlib.wordpress.com/2006/04/10/connectiontransaction-scope/

re: A ConnectionScope class. [Alazel Acheson]

Jenser — Tue, 02 May 2006 16:55:46 GMT

Hi Alazel,

Nested connection scope is merged in this code?

re: A ConnectionScope class. [Alazel Acheson]

Remy — Sat, 10 Jun 2006 19:37:17 GMT

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...

re: A ConnectionScope class. [Alazel Acheson]

Oren Ellenbogen — Thu, 22 Jun 2006 10:18:53 GMT

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 ?

ADO.Entity Framework: Stored Procedure Customization

meek — Wed, 26 Mar 2008 21:24:29 GMT

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

Tony and Zuzana’s World » System.Transactions and the DTC

Tony and Zuzana’s World » System.Transactions and the DTC — Wed, 16 Apr 2008 17:37:57 GMT

PingBack from http://blog.tonysneed.com/?p=63

Big Improvements in System.Data and SQL Server for Lightweight Transactions Support

Florin Lazar — Sat, 03 May 2008 02:07:45 GMT

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

SQL Tidbits » Blog Archive » How to keep a transaction from being escalated to the DTC

Mon, 22 Dec 2008 00:01:49 GMT

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

Data Access blog A ConnectionScope class Alazel Acheson | Outdoor Ceiling Fans

Data Access blog A ConnectionScope class Alazel Acheson | Outdoor Ceiling Fans — Sun, 31 May 2009 16:17:55 GMT

PingBack from http://outdoorceilingfansite.info/story.php?id=5159

Store commands

VS2010学习 — Wed, 10 Jun 2009 09:13:42 GMT

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