Session-Per-Request with SqlConnection / System.Transactions

Question

I've just started using Dapper for a project, having mostly used ORMs like NHibernate and EF for the past few years.

Typically in our web applications we implement session per request, beginning a transaction at the start of the request and committing it at the end.

Should we do something similar when working directly with SqlConnection / System.Transactions?

How does StackOverflow do it?

Solution

Taking the advice of both @gbn and @Sam Safron I'm not using transactions. In my case I'm only doing read queries so it seems there is no real requirement to use transactions (contrary to what I've been told about implicit transactions).

I create a lightweight session interface so that I can use a connection per request. This is quite beneficial to me as with Dapper I often need to create a few different queries to build up an object and would rather share the same connection.

The work of scoping the connection per request and disposing it is done by my IoC container (StructureMap):

public interface ISession : IDisposable {
    IDbConnection Connection { get; }
}

public class DbSession : ISession {

    private static readonly object @lock = new object();
    private readonly ILogger logger;
    private readonly string connectionString;
    private IDbConnection cn;

    public DbSession(string connectionString, ILogger logger) {
        this.connectionString = connectionString;
        this.logger = logger;
    }

    public IDbConnection Connection { get { return GetConnection(); } }

    private IDbConnection GetConnection() {
        if (cn == null) {
            lock (@lock) {
                if (cn == null) {
                    logger.Debug("Creating Connection");
                    cn = new SqlConnection(connectionString);
                    cn.Open();
                    logger.Debug("Opened Connection");
                }
            }
        }

        return cn;
    }

    public void Dispose() {
        if (cn != null) {
            logger.Debug("Disposing connection (current state '{0}')", cn.State);
            cn.Dispose();
        }
    }
}

Answer 1

This is what we do:

We define a static called DB on an object called Current

public static DBContext DB
{
    var result = GetContextItem<T>(itemKey);

    if (result == null)
    {
        result = InstantiateDB();
        SetContextItem(itemKey, result);
    }

    return result;
}

public static T GetContextItem<T>(string itemKey, bool strict = true)
{

#if DEBUG // HttpContext is null for unit test calls, which are only done in DEBUG
    if (Context == null)
    {
        var result = CallContext.GetData(itemKey);
        return result != null ? (T)result : default(T);
    }
    else
    {
#endif
        var ctx = HttpContext.Current;
        if (ctx == null)
        {
            if (strict) throw new InvalidOperationException("GetContextItem without a context");
            return default(T);
        }
        else
        {
            var result = ctx.Items[itemKey];
            return result != null ? (T)result : default(T);
        }
#if DEBUG
    }
#endif
}

public static void SetContextItem(string itemKey, object item)
{
#if DEBUG // HttpContext is null for unit test calls, which are only done in DEBUG
    if (Context == null)
    {
        CallContext.SetData(itemKey, item);
    }
    else
    {
#endif
        HttpContext.Current.Items[itemKey] = item;

#if DEBUG
    }
#endif
}

In our case InstantiateDB returns an L2S context, however in your case it could be an open SQLConnection or whatever.

On our application object we ensure that our connection is closed at the end of the request.

   protected void Application_EndRequest(object sender, EventArgs e)
   {
        Current.DisposeDB(); // closes connection, clears context 
   }

Then anywhere in your code where you need access to the db you simple call Current.DB and stuff automatically works. This is also unit test friendly due to all the #if DEBUG stuff.

---

We do not start any transactions per session, if we did and had updates at the beginning of our session, we would get serious locking issues, as the locks would not be released till the end.

Answer 2

You'd only start a SQL Server Transaction when you need to with something like TransactionScope when you call the database with a "write" call.

See a random example in this recent question: Why is a nested transaction committed even if TransactionScope.Complete() is never called?

You would not open a connection and start a transaction per http request. Only on demand. I'm having difficulty understanding why some folk advocate opening a database transaction per session: sheer idiocy when you look at what a database transaction is

Note: I'm not against the pattern per se. I am against unnecessary, too long, client-side database transactions that invoke MSDTC