Why do I need to manually open this .NET Core database connection when usually Dapper auto opens it?

Question

I'm trying to use async/await to insert a number of records into a database. If I don't manually open the connection, I get an Exception. If I add some code to open the connection, then everything works ok.

Here's the current code (which throws an Exception):

using (var connection = new SqlConnection(ConnectionString))
{
    var tasks = new List<Task>();
    soldUrls.ForEach(url => tasks.Add(InsertUrlAsync(connection, url))); 
    rentUrls.ForEach(url => tasks.Add(InsertUrlAsync(connection, url)));
    await Task.WhenAll(tasks);
}

...

private async Task InsertUrlAsync(IDbConnection connection, string url)
{
    const string query = "INSERT INTO ..";
    return await connection.ExecuteAsync(query, new { .. });
}

the exception:

Message: System.InvalidOperationException : Invalid operation. The connection is closed.

but when I change the code to the following, it works:

var tasks = new List<Task>();
await connection.OpenAsync();
soldUrls.ForEach(....) .. etc ... 

Similar SO questions:

• Task.WhenAll, connection is closing
• SqlConnection closes unexpectedly inside using statement

Answer 1

Dapper opens the connection for you; but it also closes it when work is done.

Now, you are running two independent async tasks using one single connection.

soldUrls.ForEach(url => tasks.Add(InsertUrlAsync(connection, url))); 
rentUrls.ForEach(url => tasks.Add(InsertUrlAsync(connection, url)));

Both the tasks are running simultaneously. When work of one task finish, it closes the connection. But other task is still running which does not have access to open connection anymore and hence the exception you mentioned in question.

As you said, if you open the connection yourself, Dapper does not close it and everything just works fine.

By the way, while using connection instance concurrently, there may be unexpected issues. Please refer to this question for more details.