Asynchronous SqlConnection - return something other than a Task

Question

I have a data access layer in a WinForms app, which queries a SQL Server database. It contains several DAO. In one particular case, the following method throws an exception: "BeginExecuteReader requires an open and available Connection. The connection's current state is connecting."

public List<Product> readAllProducts()
{
    List<Product> products = new List<Product>();
    string queryGetAllProducts = "SELECT * FROM Products;";

    using (SqlConnection conn = new SqlConnection(Configuration.CONNECTION_STRING))
    {
        SqlCommand cmd = new SqlCommand(queryGetAllProducts, conn);

        try
        {
            conn.OpenAsync();

            Task<SqlDataReader> readerTask = cmd.ExecuteReaderAsync();
            SqlDataReader reader = readerTask.Result;
            while (reader.Read())
            {
                 // read the data...
            }
        }
        catch (SqlException sqlEx)
        {
           throw;
        }
        finally 
        {
            if (conn != null)
                conn.Close();
        }
    }
}

In the above, I have tried wrapping the execution of the query in a lock statement, to no avail. The idea is to have a responsive UI while database operations are being done, something which has already been achieved for other methods in this class which have void return types, with the async-await pattern. How can concurrency be achieved when needing to return an object (or collection of objects, as is the case above)?

Answer 1

but it would have to await that as well (wouldn't it?), and so on up the chain

Yes, exactly that; await is contagious. So, you'd end up with:

public async Task<List<Product>> ReadAllProducts()
{
    List<Product> products = new List<Product>();
    string queryGetAllProducts = "SELECT * FROM Products;";

    using (SqlConnection conn = new SqlConnection(Configuration.CONNECTION_STRING))
    {
        // ...
    }
}

but noting that a lot more of the operations here are async-compatible - ReadAsync etc for example. Or to make it simple, just get Dapper to do the heavy lifting:

public async Task<List<Product>> ReadAllProducts()
{
    using (var conn = new SqlConnection(Configuration.CONNECTION_STRING))
    {
        var products = await conn.QueryAsync<Product>("SELECT * FROM Products;");
        return products.AsList();
    }
}

Your calling usage would then await this:

var data = await ReadAllProducts();
// do something with "data"