Why I get a connection timeout error when using async / await with Dapper?

c# connection-timeout dapper sql

Question

We're running into an issue were we execute a large number of queries at once (large being 100-200) using Dapper async and end up getting timeout exceptions. I think what is happening is that Dapper is doing await ... [connection].OpenAsync(...) (seen here) which is allowing other asynchronous code to also open connections, then by the time the code gets back to the original opened connection to start he query the sql timeout has already elapsed. My question there a way or practice to prevent this. One solution we've considered is just increasing the sql timeout. Another has been to reduce the number of object's we're querying from the DB at once. However, it just feels like we're not doing it right and wanted to reach out and see if there was a better way. Thanks in advance and sorry if this is a duplicate, I couldn't find any other describing a solution to this problem.

Here is the code executing the query:

public async Task<bool> ThingExistsAsync(int? somethingId, int somethingTypeId)
{
    using (var conn = connectionFactory.GetEddsPerformanceConnection())
    {
        return await conn.QueryFirstOrDefaultAsync<int>(Resources.Event_ReadCountBySomethingTypeAndId,
                new { somethingId, somethingTypeId }) > 0;
    }
}

Calling code:

Task.WhenAll(listOfThings.Select(t => ThingExistsAsync(t.Id, t.Type)));

Here is the exception:

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__174_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.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.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<QueryRowAsync>d__24`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Company.Product.Data.Repositories.ThingRepository.<ThingExistsAsync>d__17.MoveNext()
...
ClientConnectionId:059604ea-1123-4d8d-ba00-071ed3a0b962
Error Number:-2,State:0,Class:11
1
1
3/4/2018 1:24:46 AM

Popular Answer

I ran into the same issue. My SqlConnection object had a connection timeout specified but the dapper query didn't honor it so I passed in my connection's timeout value but of course you could pass in anything you'd like.

Here's what I did to solve my issue:

var result = await conn.QueryAsync(sqlQueryText, new { param = "paramValue" }, commandTimeout: conn.ConnectionTimeout);
2
3/23/2018 4:08:00 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow