Async Dapper with async SQL connection management

Joe Sauve

11 Feb 2015 • 4 min read

Dapper

I just recently discovered the Dapper micro-ORM for doing data access in applications. I had been using Entity Framework (EF) 6 in the APIs of most of my apps. And don't get me wrong, EF is a joy to use, it really is! But it has some shortcomings that I simply cannot overlook. Namely, it doesn't provide a good way to specify SQL indexes in code, and it seems to be drastically outperformed by other more light-weight ORM (Object Relational Mapping) frameworks.

Dapper is actually the underlying ORM used in the widely-popular programming Q&A site StackOverflow.com. It essentially provides the convenience of mapping property names and values between your in-app data structures and the columns of your database tables. Nothing new here as far as ORMs go, Dapper just does it very simply and with very little overhead. The other great thing about Dapper is its database-agnosticism, supporting a wide array of different database vendors. This means that if you do it right, you could switch your underlying database without re-writing your data access code....in theory (and as long as all your SQL is ANSI-friendly).

So, I started taking Dapper for a spin. The biggest change with ditching EF has been needing to become much more familiar with SQL Server and T-SQL, something which I have had the pleasure of avoiding as much as possible until now. But, surprisingly, the more SQL I write, the more enjoyable it becomes. Probably because I'm motiviated by using/creating high-performance systems. It's becoming evident to me how much more performant this approach is than what I had been doing with EF.

But, just as I'm writing more code in SQL, I also find that I'm writing more C# code to access the SQL stored procedures I'm writing. Of course, Dapper makes this much easier, but I've started getting sick and tired of having to Open() and Close() my SQL connections every time I want to make a query.

NOTE: The following code assumes that:

You have a C# model class named Person.
You have a Person table in your DB that aligns with the Person model.
You have an exsiting SQL sproc in your DB named sp_Person_GetById with @Id UNIQUEIDENTIFIER as a parameter, and the sproc returns rows from the Person table.

Typically with Dapper, you do something like this:

public class PersonRepository 
{
	public Person GetPersonById(Guid Id) 
    {
		var connection = new SQLConnection("myConnectionString");
        
		try {
			connection.Open(); // synchronously open a connection to the database 
			
			var p = new DynamicParameters();
			p.Add("Id", Id, DbType.Guid);
			var people = c.Query<Person>(
            	sql: "sp_Person_GetById", 
                param: p, 
                commandType: CommandType.StoredProcedure);
			return people.FirstOrDefault();
		}
		catch {
			// handle exception
		}
		finally {
        	// Close the connection explicitly, to make sure it gets closed.
            // Otherwise, we might start leaking connections.
			connection.Close();
		}
	}
}

OR if you want to take advantage of the fact that SQLConnection implements the IDisposable interface:

public class PersonRepository 
{
	public Person GetPersonById(Guid Id) 
    {
		using (var connection = new SQLConnection("myConnectionString")) 
        {
    		try {
        		connection.Open(); // synchronously open a connection to the database
                
        		var p = new DynamicParameters();
				p.Add("Id", Id, DbType.Guid);
				var people = c.Query<Person>(
                	sql: "sp_Person_GetById", 
                    param: p, 
                    commandType: CommandType.StoredProcedure);
				return people.FirstOrDefault();
    		} catch {
        		// handle exception
    		}
		} // as our scope closes, so does the sql connection, by virtue of the IDisposable interface
	}
}

The pain in the ass of those two strategies is the constant opening and closing of connections, and all the extra lines of code that it involves. I just want to query my database! I don't want to futz around with connection management!

But with a small base class, like this:

public abstract class BaseRepository 
{
    private readonly string _ConnectionString;
    
    protected BaseRepository(string connectionString) 
    {
	    _ConnectionString = connectionString;
    }

    protected async Task<T> WithConnection<T>(Func<IDbConnection, Task<T>> getData) 
    {
    	try {
        	using (var connection = new SqlConnection(_ConnectionString)) {
				await connection.OpenAsync(); // Asynchronously open a connection to the database
				return await getData(connection); // Asynchronously execute getData, which has been passed in as a Func<IDBConnection, Task<T>>
			}
		}
		catch (TimeoutException ex) {
			throw new Exception(String.Format("{0}.WithConnection() experienced a SQL timeout", GetType().FullName), ex);
		}
		catch (SqlException ex) {
			throw new Exception(String.Format("{0}.WithConnection() experienced a SQL exception (not a timeout)", GetType().FullName), ex);
		}
	}
}

...you can instead do this:

public class PersonRepository : BaseRepository
{
	public PersonRepository(string connectionString): base (connectionString) { }

	public async Task<Person> GetPersonById(Guid Id)
	{
		return await WithConnection(async c => {
        
            // Here's all the same data access code,
            // albeit now it's async, and nicely wrapped
            // in this handy WithConnection() call.
			var p = new DynamicParameters();
			p.Add("Id", Id, DbType.Guid);
			var people = await c.QueryAsync<Person>(
            	sql: "sp_Person_GetById", 
                param: p, 
                commandType: CommandType.StoredProcedure);
			return people.FirstOrDefault();
            
		});
	}
}

Much more of a joy to use! Notice the glorious absence of try/catch/finally for managing the connection!

With this revised strategy, you get two things:

By using Func<IDbConnection, Task<T>> getData as the parameter of the WithConnection() method, you can allow the opening and closing of the connection to be managed and encapsulated in one place: in your base repository. This makes for more syntactically clean code in all the data-access methods of your main repository classes that are doing the actual queries.
By using the async/await features of .NET 4.5, you add the potential of drastically improving the performance of your data-access behaviors and, consequently, the performance of your entire app or service. Just make sure you're using async/await properly throughout your entire app stack (i.e. don't use asynchrous methods in a synchronous way). In other words, always await methods that are async.

The WithConnection() method essentially gets us an asynchronously opened SQL connection, which we can then use with Dapper's async methods. The beauty of this is that if you're using async further up the call-stack, like with .NET WebAPI controllers, your entire request pipeline is now asynchronous!...from the controllers all the way back to the DB. And that translates to some pretty significant performance improvements over doing things synchronously.

The only cautionary note is that, since it's all async, if your service or app starts getting hit too hard (this depends on your server architecture and resources), it may start returning HTTP 503 responses. So, you may need a throttler or a request queue or something. That's not something I've tackled yet, but maybe I'll do a future post about that.

You can find a gist with some source code here: https://gist.github.com/jsauve/ffa2f0dc534aee3a3f16

Dapper

Sign up for more like this.