How to respect CommandTimeout when using Dapper.NET async API

Question

I noticed that using the asynchronous API in Dapper.NET the command timeout value I pass in the extension method is not respected. Then I came across the MSDN documentation of the SqlCommand.CommandTimeout and it seems that this is something it is not "supported".

The CommandTimeout property will be ignored during asynchronous method calls such as BeginExecuteReader.

I'm using the following methods in a base class.

public async Task<int> ExecuteAsync(string sql, object param = null,
            CommandType commandType = CommandType.Text, int? commandTimeout = null, IDbTransaction transaction = null)
{
    using (var connection = Connection)
    {
        Task<int> queryTask = connection.ExecuteAsync(sql, param, transaction, commandTimeout ?? CommandTimeoutDefault, commandType);
        int result = await queryTask.ConfigureAwait(false);
        connection.Close();
        connection.Dispose();
        return result;
    }
}

public async Task<IEnumerable<TEntity>> QueryAsync(string sql, object param = null, 
            CommandType commandType = CommandType.Text, int? commandTimeout = null, IDbTransaction transaction = null)
{
    using (var connection = Connection)
    {
        Task<IEnumerable<TEntity>> queryTask = connection.QueryAsync<TEntity>(sql, param, transaction, commandTimeout ?? CommandTimeoutDefault, commandType);
        IEnumerable<TEntity> data = await queryTask.ConfigureAwait(false);
        connection.Close();
        connection.Dispose();
        return data;
    }
}

Say CommandTimeoutDefault is 30, I can se that a request taking 50 seconds will still be evaluated.

Any thoughts how to disconnect and dispose the connection in a timeout interval using asynchronous Dapper.NET API?

Answer 1

It is vexing that SqlClient does deal with this itself! However, I wonder if you could do something involving:

• a cancellation token that you pass to dapper (it accepts them)
• a call to Task.Delay with your timeout
• a call to Task.WhenAny passing in the dapper task and the delay task
• check which came back from WhenAny - if it was the delay, then signal the cancellation and throw your own timeout
• ensure that any exceptions caused by the cancellation are handled cleanly

Slightly ugly, and maybe this is something that the library should encapsulate if DB providers aren't going to respect the timeout natively. There may also be additional steps required to cleanly cancel an in-flight command, in which case perhaps only the library can do this properly (as only the library has access to the command)