Task when all, connection is closing

Question

I'm trying to execute multiple SqlDataReaders using Task.WhenAll. But when the tasks are awaited I get

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

Creation of tasks:

        List<Task<SqlDataReader>> _listTasksDataReader = new List<Task<SqlDataReader>>();
        _listTasksDataReader.Add(GetSqlDataReader1(10));
        _listTasksDataReader.Add(GetSqlDataReader2(10));
        SqlDataReader[] _dataReaders = await Task.WhenAll(_listTasksDataReader);

My "SqlDataReader" methods:

    public Task<SqlDataReader> GetSqlDataReader1(int recordCount)
    {
        using (var sqlCon = new SqlConnection(ConnectionString))
        {
            sqlCon.Open();
            using (var command = new SqlCommand("sp_GetData", sqlCon))
            {
                command.Parameters.Clear();
                command.Parameters.Add(new SqlParameter("@recordCount", recordCount));
                command.CommandType = System.Data.CommandType.StoredProcedure;
                return command.ExecuteReaderAsync();
            }
        }
    }

Shouldn't the database connections be opened when the Task.WhenAll is executed or am I missing something?

Answer 1

It is possible to pass a CommandBehavior.CloseConnection to the ExecuteReaderAsync. Then the connection will remain open until the returned datareader object is closed: see MSDN here and here. In that case, the SqlConnection does not need to be in a using statement.

Like this:

public Task<SqlDataReader> GetSqlDataReader1(int recordCount)
{
    var sqlCon = new SqlConnection(ConnectionString);
    sqlCon.Open();

    using (var command = new SqlCommand("sp_GetData", sqlCon))
    {
        command.Parameters.Clear();
        command.Parameters.Add(new SqlParameter("@recordCount", recordCount));
        command.CommandType = System.Data.CommandType.StoredProcedure;
        return command.ExecuteReaderAsync(CommandBehavior.CloseConnection);
    }
}

Answer 2

am I missing something?

You're trying to get a SqlDataReader that doesn't have an underlying connection? I don't think that will work well. What happens as you read from the reader? The connection is already closed.

So, you probably just need to read the actual data before closing the connection:

public async Task<List<T>> GetData1(int recordCount)
{
    using (var sqlCon = new SqlConnection(ConnectionString))
    {
        sqlCon.Open();
        using (var command = new SqlCommand("sp_GetData", sqlCon))
        {
            command.Parameters.Clear();
            command.Parameters.Add(new SqlParameter("@recordCount", recordCount));
            command.CommandType = System.Data.CommandType.StoredProcedure;

            var result = new List<T>();
            var reader = await command.ExecuteReaderAsync();
            // TODO: use `reader` to populate `result`
            return result;
        }
    }
}