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

c# connection-timeout dapper sql

Question

We're seeing a problem where we use Dapper async to perform a big number of queries simultaneously (large being 100–200) and end up receiving timeout issues. What's going on, in my opinion, is that Dapper is actingawait ... [connection].OpenAsync(...) By the time the code returns to the first connection it created to begin the query, the sql timeout has already passed. This is caused by the (viewed here) instruction, which permits other asynchronous programs to make connections as well. I want to know whether there is a method or routine to stop this. We've thought about just raising the sql timeout as a solution. Another was to limit the amount of objects we were retrieving at once from the database. But it just seems like we're not going about it correctly, so I wanted to get in touch to see if there was another method. I apologize if this is a duplication and appreciate your help in finding a solution to this issue.

The code that runs the query is as follows:

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;
    }
}

dialing system

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

Here is a case in point:

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 had the same problem. I gave in my connection's timeout value since my SqlConnection object had a connection timeout set but the dapper query ignored it. Of course, you could send in whatever you'd want.

Here is what I did to fix my problem:

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