What is the proper way of selecting multiple scalars within the same “using” statement with Dapper?

I have wrote the following method, which I hoped to make the method run faster by executing the tasks in parallel:

public static async Task<ExampleData> GetExampleDataAsync()
    {       
        try
        {
            var data = new ExampleData();
            
            using (SqlConnection connection = new SqlConnection(_connectionString)
            {
                //Do them in parallel
                var Task1 = connection.ExecuteScalarAsync<int>
                    ($"SELECT COUNT(*) FROM dbo.ExampleTable)");

                var Task2 = connection.ExecuteScalarAsync<int>
                    ($"Select Count(*) from dbo.ExampleTable where(row1 is null)");

                var Task3 = connection.ExecuteScalarAsync<double>
                    ($"Select SUM(row2) from dbo.ExampleTable");

                await Task.WhenAll(Task1, Task2, Task3);

                data.prop1 = await Task1;
                data.prop2 = await Task2;
                data.prop3 = await Task3;
            }

            return data;
        }
        catch (Exception)
        {
            throw;
        }         
    }

Unfortunately, I receive the following exception:

System.InvalidOperationException: ‘BeginExecuteReader requires an open and available Connection. The connection’s current state is connecting.’

How should I implement it correctly?

Answer

First things first, executing many commands in parallel against a database will not necessarily be faster, because you put more load on the server and make it try to serve everything at once, which can just cause each query to block the other.

It’s also very difficult to do so on a single connection (unless you use MARS, itself inadvisable), so you would need multiple connections.

In any case, you shouldn’t do multiple queries here at all. You can get all the information in one shot with one query, and the performance will be the same as executing just one of them. This is because they all select from the same table.

Note also that the catch block is redundant, and that you can use Dapper to retrieve multiple columns directly into your object.

public static async Task<ExampleData> GetExampleDataAsync()
{       
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        return await connection.ExecuteScalarAsync<ExampleData>(@"
SELECT
    prop1 = COUNT(*),
    prop2 = COUNT(CASE WHEN row1 IS NULL THEN 1 END),
    prop3 = SUM(row2)
FROM dbo.ExampleTable;
        ");
    }
}