AsusT9 AsusT9 - 2 months ago 35
C# Question

Task when all, connection is closing

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

UPDATE: I'm going to leave this here, but I've just remembered that you're not allowed to combine yield and await... at least, not yet.


Remember that calling command.ExecuteReaderAsync(), even with the return keyword, doesn't stop execution of the method. That's the whole point of _Async() methods. So immediately after that function call, the code exits the using block. This has the effect of disposing your connection object before you ever have a chance to use it to read from your DataReader.

Try returning an Task<IEnumerable<IDataRecord>> instead:

public async Task<IEnumerable<IDataRecord>> GetSqlDataReader1(int recordCount)
{
    using (var sqlCon = new SqlConnection(ConnectionString))
    using (var command = new SqlCommand("sp_GetData", sqlCon))
    {
        command.Parameters.Add("@recordCount", SqlDbType.Int).Value = recordCount;
        command.CommandType = System.Data.CommandType.StoredProcedure;

        sqlCon.Open();               
        var rdr = await command.ExecuteReaderAsync();
        while (rdr.Read())
        {
             yield return rdr;
        }
    }
}

Note that there is a "gotcha" with this pattern. Each yield return uses the same object, and therefore some weird things can happen if you aren't careful. I recommend further changing this include code that puts the data from each record in the rdr object into it's own (strongly-typed) object instance:

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

        sqlCon.Open();                
        var rdr = await command.ExecuteReaderAsync();
        while (rdr.Read())
        {
             yield return new SomeObject() {Field1 = rdr[1], Field2 = rdr[2], etc};
        }
    }
}