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();
        }
    }
}

Explain

Does this approach still work for you? – Sectionalize

Yes we are still using the above code in production although we haven't updated Dapper in some time. – Explain

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.

Schmeltzer

Thanks Sam, I've posted my solution above. – Explain

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

Trepidation

what's your take on this article as I've always wrapped my queries in a transaction up to now - ayende.com/blog/3775/… – Explain

@Ben: frankly, complete bollocks. Someone doesn't understand transactions locking, concurrency, and isolation levels. I discussed your question with some c# colleagues and we'll all in agreement. – Trepidation

@Ben fyi, my pattern opens the connection first time you need it ... we ensure it is closed at the end of the session. In an ideal world you may want to close the connection a bit earlier, you have the option if you call dispose db. I agree with @Trepidation about transactions, don't start a transaction unless you need it. Similarly ... don't open a db connection just to display static content. – Schmeltzer

I've added my solution. I've opted not to use transactions for my read queries. I am only opening a connection on demand (not on every request) but am then sharing the connection for the remainder of the request. The connection is disposed of at the end of the request. I've +1'd but chosen Sams answer as it's more specific to my question ref Dapper and what SO do. – Explain