How to keep SqlConnection open in an using block for an asynchronous query within the block?

Question

I am trying to make an asynchronous query in an using block with a SqlConnection variable.

This is my code:

using (var conn = GetNewConnection())
{
    var query = "DELETE FROM blah blah blah WHERE blah blah;"

    conn.Open();

    using (var cmd = new NpgsqlCommand(query, conn))
    {
        cmd.Parameters.AddRange(new[]
                {
                    new NpgsqlParameter("@a", A),
                    new NpgsqlParameter("@b", B)
                });
        cmd.ExecuteNonQueryAsync();
    }
}

As seen above, the last line of the using block is an asynchronous task. In this situation, will the connection be open until the asynchronous query is complete? Why or why not?

What ought to be done to ensure the completion of an asynchronous task? What is a better way of doing it?

Answer 1

Encapsulate the entire code block in an async function, taking advantage of the async API and await any asynchronous function calls within.

For example

public async Task ExecuteMyNonQueryAsync() {    
    using (var conn = GetNewConnection()) {
        var query = "DELETE FROM blah blah blah WHERE blah blah;"
        await conn.OpenAsync();
        using (var cmd = new NpgsqlCommand(query, conn)) {
            cmd.Parameters.AddRange(new[] {
                new NpgsqlParameter("@a", A),
                new NpgsqlParameter("@b", B)
            });
            await cmd.ExecuteNonQueryAsync();
        }
    }
}

The entire function can be run on a separate thread either using Task.Run

Task.Run(() => ExecuteMyNonQueryAsync());

or in an async event handler

public async void onSomeEvent(object sender, EventArgs args) {
    await ExecuteMyNonQueryAsync();
}

Reference Async/Await - Best Practices in Asynchronous Programming