Dapper async query inside using statement requires an open and available Connection

Question

I'm using Dapper in my project. My project consist 3 layers. API, Business and Repository layers.

I want user async query of dapper.

Below is code in each layer.

At Repository layer

public Task<int> ChangeStatus(DriverStatus driverStatus)
        {
            using (IDbConnection connection = DapperConnection)
            {
                var updateQuery = @"UPDATE [dbo].[DriverLocation] Set [Latitude]=@Latitude, [Longitude]=@Longitude, [IsOnline]=@IsOnline Where [DriverId]=@DriverId";
                return connection.ExecuteAsync(updateQuery, new
                {
                    Latitude = driverStatus.Latitude,
                    Longitude = driverStatus.Longitude,
                    IsOnline = driverStatus.IsOnline,
                    DriverId = driverStatus.DriverId
                });
            }
        }

Now My Business layer method calls above repository method .

public Task<int> ChangeStatus(DriverStatus driverStatus)
        {
            try
            {
                //Some Code here.
               return driverRepository.ChangeStatus(driverStatus);
            }
            catch (Exception ex)
            {
                Logger.Error(ex);
                return Task.FromResult<int>(0);
            }
        }

Now API method call the business layer method.

public async Task<IHttpActionResult> ChangeStatus(DriverStatus driverStatus)
        {
            ApiResponse apiResponse = new ApiResponse();
            var isUpdated = await driverBl.ChangeStatus(driverStatus);
            if(isUpdated > 0)
            {
                apiResponse.Message = "Driver status changed successfully.";
                return ResponseMessage(Request.CreateResponse(HttpStatusCode.OK, apiResponse));
            }
            else
            {
                apiResponse.Message = "Driver status could not be changed.";
                return ResponseMessage(Request.CreateResponse(HttpStatusCode.BadRequest, apiResponse));
            }

        }

As I have used await keyword only inside my API method, so It might be possible that my connection object got disposed before returning result, So it throw me below error.

BeginExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

How can use async method of dapper using using statement?

You should always be using `async` all the way down through your calls. — maccettura, Aug 22 '17 at 19:40
@maccettura : Then I'll have to await keyword at all 3 places API, Business and Service. Right? — Amit Kumar, Aug 22 '17 at 19:41
@maccettura : sorry but I'm not expert in async concept. But If use await keyword at each layer then don't it will start working as synchronous method, Then I think I won't get benefit of async method? correct me If I miss understood the concept. — Amit Kumar, Aug 22 '17 at 19:44
You should really read more tutorials on async/await because you have some pretty large misunderstandings of it. — maccettura, Aug 22 '17 at 19:44
When you **omit** the async/await keywords you are making it _synchronous_. Adding those keywords (among other things) makes it _asynchronous_ — maccettura, Aug 22 '17 at 19:47
@maccettura : Sure I'll read it in more details. But for now I want to know that, is there any disadvantage of await keyword at each layer. I read this answer at stackoverflow. It's better use await at lower level. For your reference https://stackoverflow.com/a/15503860/2465787 — Amit Kumar, Aug 22 '17 at 19:49
@JonSkeet is way more knowledgeable than I am, but just reading his answer I see _"This will be slightly more efficient.."_. I'm not sure your specific scenario is the same either (since you have a `using` statement) — maccettura, Aug 22 '17 at 19:57
@maccettura: yes. that's why I'm want to make it sure, I'm going in right direction or not. earlier I've used await at each layer , but after reading his answer, I removed the async/await . now started facing problem . — Amit Kumar, Aug 22 '17 at 20:02

score 0 · Answer 1 · answered Aug 22 '17 at 19:43

With async/await you need to ensure that you are using it all throughout your call stack.

Your repo layer should look like this:

public async Task<int> ChangeStatus(DriverStatus driverStatus)
{
    using (IDbConnection connection = DapperConnection)
    {
        var updateQuery = @"UPDATE [dbo].[DriverLocation] Set [Latitude]=@Latitude, [Longitude]=@Longitude, [IsOnline]=@IsOnline Where [DriverId]=@DriverId";
        return await connection.ExecuteAsync(updateQuery, new
        {
            Latitude = driverStatus.Latitude,
            Longitude = driverStatus.Longitude,
            IsOnline = driverStatus.IsOnline,
            DriverId = driverStatus.DriverId
        });
    }
}

And your BLL layer should look like this:

public async Task<int> ChangeStatus(DriverStatus driverStatus)
{
    try
    {
        //Some Code here.
        return await driverRepository.ChangeStatus(driverStatus);
    }
    catch (Exception ex)
    {
        Logger.Error(ex);
        return 0;
    }
}

YuvShap · Answer 2 · 2017-08-22T20:55:59.337

Your problem is that you omitted async and await usage in your repository layer, and when this is combined with a using statement it will cause unpredicted behaviour.

When you call connection.ExecuteAsync you started a task that is dependent on the connection, so the connection must stay open and shouldn't be disposed as long as the task is not done. In your case you returned immediately after starting the task and the connection is being disposed right away (since you have a using statement which will dispose the connection as soon as the connection will get out of scope), all of this happening before the task has been done.

In order to prevent the connection from being dispose until the task is done you will need to "wait in async way" to the operation using async and await keywords and only after the await return the result from the method.

public async Task<int> ChangeStatus(DriverStatus driverStatus)
{
    using (IDbConnection connection = DapperConnection)
    {
        var updateQuery = @"UPDATE [dbo].[DriverLocation] Set [Latitude]=@Latitude, [Longitude]=@Longitude, [IsOnline]=@IsOnline Where [DriverId]=@DriverId";
        return await connection.ExecuteAsync(updateQuery, new
        {
            Latitude = driverStatus.Latitude,
            Longitude = driverStatus.Longitude,
            IsOnline = driverStatus.IsOnline,
            DriverId = driverStatus.DriverId
        });
    }
}

More info on that subject in Eliding Async and Await Stephen Cleary's blog article.

You have one more mistake, in your business layer your try catch block is not working as you expect, in order to catch exception from an async method you have to block or await it:

public async Task<int> ChangeStatus(DriverStatus driverStatus)
{
    try
    {
       //Some Code here.
       return await driverRepository.ChangeStatus(driverStatus);
    }
    catch (Exception ex)
    {
        Logger.Error(ex);
        return 0;
    }
}

And one last thing which is not related, is it really a good idea to swallow the exceptions at business layer and make a misrepresentation to the upper levels?

Dapper async query inside using statement requires an open and available Connection

2 Answers2