Calling QueryAsync in Dapper throws a InvalidOperationException

asp.net-web-api async-await dapper

Question

I was trying out the async support in dapper and when running a simple use case I ran into the follow issue. I have an ApiController that just triggers a log entries using dapper to create it in the database. My db connections are fine. When I run a synchronous version of the using the Query extension method from dapper, everything works fine. When I use the QueryAsync along with the async/await constructs, I get an InvalidOperationException. Here's what the code looks like

public class BackgroundController : ApiController
{
    [HttpGet]
    public async Task<HttpResponseMessage> Run(string op)
    {
        using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Backgrounder"].ConnectionString))
        {
            const string sql =
                "Insert Into SimpleLog (message, threadid, created) Values(@message, @threadid, @created); " +
                "SELECT CAST(SCOPE_IDENTITY() as int)";
            var results = await (connection.QueryAsync<int>(sql, new { message = "new log record", threadid = "1", created = DateTime.Now }));
            var id = results.SingleOrDefault();
            Debug.WriteLine("inserted log record id: " + id);
        }
        return this.Request.CreateResponse(HttpStatusCode.OK);
    }
}

Also, here is what the stacktrace look like:

at System.Data.SqlClient.SqlCommand.b_24(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at System.Runtime.CompilerServices.TaskAwaiter1.GetResult() at Dapper.SqlMapper.<QueryAsync>d__691.MoveNext() in c:\Dev\Dapper\Dapper NET45\SqlMapperAsync.cs:line 21 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task) at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult() at WebApiBackgrounder.Controllers.BackgroundController.d_0.MoveNext()

It seems to be failing when dapper makes a call to ExecuteReaderAsync. Does anyone how to address this?

EDIT

Here's the connection string

 <add name="Backgrounder" providerName="System.Data.SqlClient" connectionString="Data Source=LPW7X6530;Initial Catalog=Sandbox;Integrated Security=SSPI;" />

Accepted Answer

Based on the error message, it sounds like the connection is not open. As it happens, dapper normally tries to do this for you - so it is not unreasonable that you didn't call Open() first - I guess we missed that. For now: call Open() on the connection.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why