Difference between connection.OpenAsync and connection.Open when using Dapper QueryAsync method

Question

What is the difference between using connection.OpenAsync() and connection.Open() when using dapper's QueryAsync method.

Async:

public async Task<IList<Product>> GetListAsync()
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT Id, Name ");
            sql.AppendLine("FROM Product ");

            await connection.OpenAsync();
            var tickets = await connection.QueryAsync<Ticket>(sql.ToString());
            return tickets.ToList();
        }
    }

Not Async:

public async Task<IList<Product>> GetListAsync()
{
    using (var connection = new SqlConnection(_connectionString))
    {
        StringBuilder sql = new StringBuilder();
        sql.AppendLine("SELECT Id, Name ");
        sql.AppendLine("FROM Product ");

        connection.Open();
        var tickets = await connection.QueryAsync<Ticket>(sql.ToString());
        return tickets.ToList();
    }
}

Peres

Answer 1

OpenAsync() will return immdiately from the method (GetListAsync) to the method that called it so the thread can be free to do other things in the meantime. For example, lets say it takes one second to open the connection (just for example), then the thread can do something else. The opening of the connection will not be done by the thread that calls it.

Once the connection is opened, the exécution will go to the next line which is:

var tickets = await connection.QueryAsync<Ticket>(sql.ToString());

Note: Please note in some cases if IsCompleted returns true then it will be done synchronously.

Christenechristening

so there is an advantage to using OpenAsync rather than keeping it on the main thread and possibly blocking right? – Peres

@blakerivell Yes but it all depends. Basically the thread is not kept busy and its free to do other things such as react to button clicks or show a progress bar etc. So it depends on how you use the free time. If you are not doing anything in the free time then it is not advantageous. In fact it is bad because context switching takes time and if you do nothing during the free time, then you switched context for no reason. – Christenechristening

Will using Async methods from a Web API controller all the way down to the repository benefit me if 10 separate users click a button that calls an API method like GetUserList at the same time from the UI? I would assume yes because each request can be handles on separate threads. Otherwise each use would have to wait for the previous user to get their response correct? – Peres

@blakerivell No not correct. In web api scenario the iis server has a lot of threads already so users will not have to wait (unless it runs out of threads which can happen). You still benefit because the thread will return to the thread pool and iis can see that thread is free and use the thread. Whenever connection opens then work will resume and go to next line. So yes in web api it is advantageous. It helps because the thread is not just waiting around and smoking a cigarette but going back to thread pool for more possible work. – Christenechristening

Got it so what I said about a single thread is incorrect, but Async definitely makes this better right? – Peres

@blakerivell correct. Imagine a hypothetical set up wherein iis only has 1 thread named T1 and the Open takes 1 minute. Then each incoming request will take at least 1 min and a subsequent request arrives but T1 is waiting for Open so that subsequent request will be queued. With OpenAsync T1 will be able to serve the subsequent request and possibly even more requests instead of just waiting for OpenAsync. Please note that it will still take 1 min to complete each request but many requests can be done in parallel. – Christenechristening

Answer 2

According to source code for Open and OpenAsync for DbConnection,

abstract public void Open();

public Task OpenAsync() {
    return OpenAsync(CancellationToken.None);
}

public virtual Task OpenAsync(CancellationToken cancellationToken) {
    TaskCompletionSource<object> taskCompletionSource = new TaskCompletionSource<object>();

    if (cancellationToken.IsCancellationRequested) {
        taskCompletionSource.SetCanceled();
    }
    else {
        try {
            Open();
            taskCompletionSource.SetResult(null);
        }
        catch (Exception e) {
            taskCompletionSource.SetException(e);
        }
    }

    return taskCompletionSource.Task;
}

OpenAsync is just to allow for async code to be awaited while not blocking the thread.

Now with Dapper you do not necessarily have to open the connection as internally the method would open the connection if it is closed.

Bashemath

I see so your saying that dapper's Query or QueryAsync functions will open the connection if it isn't already open? – Peres

@BlakeRivell take a look at the Dapper source code github.com/StackExchange/Dapper/blob/… – Bashemath