Support for Async queries

[GoogleCodeExporter]

This is not a defect, but I don't know how to change the classification of this 
issue report.

I have just started to build an asynchronous ASP.NET Web Api (REST) service.
However, the async nature of the Web Api does not make much sense if the 
back-end (the I/O part) does not have an asynchronous API.

Since I like the approach Dapper takes to OR mapping, it would be nice if 
Dapper had an asynchronous API (for .NET 4.5).

Please regard this as a feature request, not a bug report.

Original issue reported on code.google.com by karl.wac...@gmail.com on 13 Dec 2012 at 8:15

[GoogleCodeExporter]

It seems Dapper now has Async support, but I cannot find "QueryMultipleAsync".
Is this one still missing?

Original comment by kwac...@gmail.com on 12 Aug 2013 at 3:14

[GoogleCodeExporter]

I'm not able to find async versions of QueryMultiple or Execute in 1.13. I'm 
not sure if I'm just doing it wrong, or if those were missed in the latest 
update.

Original comment by dodgep...@gmail.com on 19 Aug 2013 at 6:43

[GoogleCodeExporter]

Seems QueryMultipleAsync might be just some simple changes to the synchronous 
version:

        /// <summary>
        /// Query Async Multiple
        /// </summary>
        /// <param name="cnn"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static async Task<GridReader> QueryMultipleAsync(this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
          var identity = new Identity(sql, commandType, cnn, typeof(GridReader), (object)param == null ? null : ((object)param).GetType(), null);
          var info = GetCacheInfo(identity);

          DbCommand cmd = null;
          IDataReader reader = null;
          var wasClosed = cnn.State == ConnectionState.Closed;
          var commandBehavior = wasClosed ? CommandBehavior.CloseConnection : CommandBehavior.Default;
          try {
            if (wasClosed) cnn.Open();
            cmd = SetupCommand(cnn, transaction, sql, info.ParamReader, param, commandTimeout, commandType);
            reader = await cmd.ExecuteReaderAsync(commandBehavior);

            var result = new GridReader(cmd, reader, identity);
            wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader
            // with the CloseConnection flag, so the reader will deal with the connection; we
            // still need something in the "finally" to ensure that broken SQL still results
            // in the connection closing itself
            return result;
          }
          catch {
            if (reader != null) {
              if (!reader.IsClosed) try { cmd.Cancel(); }
                catch { /* don't spoil the existing exception */ }
              reader.Dispose();
            }
            if (cmd != null) cmd.Dispose();
            if (wasClosed) cnn.Close();
            throw;
          }
        }
    }

Original comment by kwac...@gmail.com on 26 Nov 2013 at 6:04

[GoogleCodeExporter]

Seems ExecuteAsync is missing as well. 
This might work:

        static async Task<int> ExecuteCommandAsync(IDbConnection cnn, IDbTransaction transaction, string sql, Action<IDbCommand, object> paramReader, object obj, int? commandTimeout, CommandType? commandType) {
          DbCommand cmd = null;
          bool wasClosed = cnn.State == ConnectionState.Closed;
          try {
            cmd = (DbCommand)SetupCommand(cnn, transaction, sql, paramReader, obj, commandTimeout, commandType);
            if (wasClosed) cnn.Open();
            return await cmd.ExecuteNonQueryAsync();
          }
          finally {
            if (wasClosed) cnn.Close();
            if (cmd != null) cmd.Dispose();
          }
        }

        /// <summary>
        /// Execute parameterized SQL  
        /// </summary>
        /// <returns>Number of rows affected</returns>
        public static async Task<int> ExecuteAsync(this IDbConnection cnn, string sql, dynamic param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
          IEnumerable multiExec = (object)param as IEnumerable;
          Identity identity;
          CacheInfo info = null;
          if (multiExec != null && !(multiExec is string)) {
            bool isFirst = true;
            int total = 0;
            using (var cmd = (DbCommand)SetupCommand(cnn, transaction, sql, null, null, commandTimeout, commandType)) {

              string masterSql = null;
              var cmdTasks = new List<Task<int>>();
              foreach (var obj in multiExec) {
                if (isFirst) {
                  masterSql = cmd.CommandText;
                  isFirst = false;
                  identity = new Identity(sql, cmd.CommandType, cnn, null, obj.GetType(), null);
                  info = GetCacheInfo(identity);
                }
                else {
                  cmd.CommandText = masterSql; // because we do magic replaces on "in" etc
                  cmd.Parameters.Clear(); // current code is Add-tastic
                }
                info.ParamReader(cmd, obj);
                cmdTasks.Add(cmd.ExecuteNonQueryAsync());
              }
              foreach (var cmdTask in cmdTasks)
                total += await cmdTask;
            }
            return total;
          }

          // nice and simple
          if ((object)param != null) {
            identity = new Identity(sql, commandType, cnn, null, (object)param == null ? null : ((object)param).GetType(), null);
            info = GetCacheInfo(identity);
          }
          return await ExecuteCommandAsync(cnn, transaction, sql, (object)param == null ? null : info.ParamReader, (object)param, commandTimeout, commandType);
        }

Original comment by kwac...@gmail.com on 27 Nov 2013 at 5:01

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Just as an FYI, I added pull request #105 for this:
https://github.com/SamSaffron/dapper-dot-net/pull/105

Original comment by kwac...@gmail.com on 2 Feb 2014 at 3:46

[GoogleCodeExporter]

None of the calls Dapper awaits in the current release use 
.ConfigureAwait(false).  This is a serious problem that the caller cannot 
control.  Not using ConfigureAwait(false) will cause deadlocks for any ASP.Net 
consumer that blocks the request thread (and sometimes this is not within their 
control as many libraries are not yet async).

The reason is that the default is actually ConfigureAwait(true), which forces 
the request's original thread to resume the continuation rather than any 
available thread.  ConfigureAwait(true) is also slightly slower, as it must 
restore the current request data to the thread.  Since Dapper doesn't use any 
ASP.Net request data, this is both a waste of resources and a potential way to 
deadlock request after request (especially for less experience users who get 
excited about async and half-implement it.

For more info for more info see: 
http://blog.stephencleary.com/2012/07/dont-block-on-async-code.html

Original comment by spicydon...@gmail.com on 29 Apr 2014 at 11:49