Dapper and Dapper Async

11:02 AM Xun 1 Comments

Dapper 

Dapper is an object-relational mapping (ORM) solution for the Microsoft .NET platform. It is written for and used by stackoverflow. It has the signatures of a stackoverflow baby, hugely popular, no-bullshit/lightweight, effective and efficient, stellar performance.

We have been using Dapper for a couple of years. We love it, for its simplicity and performance. It is so simple, whether we do select, insert or update, whether we use stored procedures or ad-hoc sql.


using (var connection = new SqlConnection(myConnectionString))
            {
                connection.Open();
                const string sqlStatement = @"select client_id As Id, client_name As Name from dbo.client with(nolock) ";
                
                return connection.Query(sqlStatement);
            }

.NET async 

.net 4.5 came out about 2 years ago, and asynchronous programming with async and await is one of the big features. async/await allows you to define a Task that you can await on, and then continue execution once the work is done, all without blocking the calling thread unnecessarily. async and await operations took away the programmatic complexity from programmers, however, under the hood the compiler still does the heavy lifting of setting up a waiting context/state machine so a task can get back to exactly where it has begun.

 The overhead of async/await in NET 4.5 demonstrates that "Despite this async method being relatively simple, ANTS Performance Profiler shows that it’s caused over 900 framework methods to be run in order to initialize it and the work it does the first time that it’s run."


 It is agreed upon that async/await should be limited to only I/O heavy operations, web access, working with files, working with images.

How about database calls? Turned out database calls are by nature blocking. It blocks the calling thread even if you stamp your method as async and use the await keyword.

 To allow asynchronous database queries and other crud operation, dapper added a set of QueryAsync methods using .NET Framework 4.5's Task-Based Asynchronous Pattern. Under the hood, the asyn dapper methods uses connection.OpenAync, executeReaderAsync to allow connections to be opened and queried in asynchoronous mode.
      public static async Task> QueryAsync(this IDbConnection cnn, CommandDefinition command)
        {
            object param = command.Parameters;
            var identity = new Identity(command.CommandText, command.CommandType, cnn, typeof(T), param == null ? null : param.GetType(), null);
            var info = GetCacheInfo(identity, param);
            bool wasClosed = cnn.State == ConnectionState.Closed;
            using (var cmd = (DbCommand)command.SetupCommand(cnn, info.ParamReader))
            {
                try
                {
                    if (wasClosed) await ((DbConnection)cnn).OpenAsync().ConfigureAwait(false);
                    using (var reader = await cmd.ExecuteReaderAsync(command.CancellationToken).ConfigureAwait(false))
                    {
                        return ExecuteReader(reader, identity, info).ToList();
                    }
                }
                finally
                {
                    if (wasClosed) cnn.Close();
                }
            }

        }
Should you use asynchronous database calls?

 Just because you can do it does not mean you should do it, says RickAndMSFT at here.

 True.

 It does your more harm than good if you queryAsync a small query that takes a few millisecond, however for long running database calls you may consider using querySync feature to avoid bottleneck and gain some responsiveness.

 Check out some benchmark testing from this article Asynchronous Database Calls With Task-based Asynchronous Programming Model (TAP) in ASP.NET MVC 4.

1 comment:

  1. do you use CodeSmith 2.6 code generator templates or T4 templates with Dapper in real applications?

    ReplyDelete