How to Return and Consume an IAsyncEnumerable with SqlDataReader

Question

Please see the below two methods. The first returns an IAsyncEnumerable. The second tries to consume it.

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Threading;
using System.Threading.Tasks;

public static class SqlUtility
{
    public static async IAsyncEnumerable<IDataRecord> GetRecordsAsync(
        string connectionString, SqlParameter[] parameters, string commandText,
        [EnumeratorCancellation]CancellationToken cancellationToken)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
            using (SqlCommand command = new SqlCommand(commandText, connection))
            {
                command.Parameters.AddRange(parameters);
                using (var reader = await command.ExecuteReaderAsync()
                    .ConfigureAwait(false))
                {
                    while (await reader.ReadAsync().ConfigureAwait(false))
                    {
                        yield return reader;
                    }
                }
            }
        }
    }

    public static async Task Example()
    {
        const string connectionString =
            "Server=localhost;Database=[Redacted];Integrated Security=true";
        SqlParameter[] parameters = new SqlParameter[]
        {
            new SqlParameter("VideoID", SqlDbType.Int) { Value = 1000 }
        };
        const string commandText = "select * from Video where VideoID=@VideoID";
        IAsyncEnumerable<IDataRecord> records = GetRecordsAsync(connectionString,
            parameters, commandText, CancellationToken.None);
        IDataRecord firstRecord = await records.FirstAsync().ConfigureAwait(false);
        object videoID = firstRecord["VideoID"]; //Should be 1000.
        // Instead, I get this exception:
        // "Invalid attempt to call MetaData when reader is closed."
    }
}

When the code tries to read the resultant IDataReader (at object videoID = firstRecord["VideoID"];), I get this exception:

Invalid attempt to call MetaData when reader is closed.

This is because SqlDataReader is disposed. Can someone supply a recommended method for enumerating SqlDataReader in an asynchronous way so that each resultant record is available to the calling method? Thank you.

score 9 · Accepted Answer · answered Nov 12 '19 at 19:56

In this scenario, LINQ is not your friend, as FirstAsync is going to close the iterator before it returns the result, which isn't what ADO.NET expects; basically: don't use LINQ here, or at least: not in this way. You might be able to use something like Select to perform the projection while the sequence is still open, or it may be easier to just offload all the work here to a tool like Dapper. Or, to do it manually:

await foreach (var record in records)
{
    // TODO: process record
    // (perhaps "break"), because you only want the first
}

I see now. Thank you. Your answer filled in the bit of knowledge I was missing in the most concise way. I used what you told me to cache the record data into a dictionary before the disposal of the reader. — user1325179, Nov 13 '19 at 15:14

Gabriel Luci · Answer 2 · 2019-11-12T22:29:59.973

You can avoid this by not returning an object that depends on the connection still being open. For example, if you only need the VideoID, then just return that (I'm assuming it's an int):

public static async IAsyncEnumerable<int> GetRecordsAsync(string connectionString, SqlParameter[] parameters, string commandText, [EnumeratorCancellation]CancellationToken cancellationToken)
{
    ...
                    yield return reader["VideoID"];
    ...
}

Or project into your own class:

public class MyRecord {
    public int VideoId { get; set; }
}

public static async IAsyncEnumerable<MyRecord> GetRecordsAsync(string connectionString, SqlParameter[] parameters, string commandText, [EnumeratorCancellation]CancellationToken cancellationToken)
{
    ...
                    yield return new MyRecord {
                        VideoId = reader["VideoID"]
                    }
    ...
}

Or do what Marc suggested and use a foreach and break after the first one, which would look like this in your case:

IAsyncEnumerable<IDataRecord> records = GetRecordsAsync(connectionString, parameters, commandText, CancellationToken.None);
object videoID;
await foreach (var record in records)
{
    videoID = record["VideoID"];
    break;
}

score 3 · Answer 3 · answered Nov 12 '19 at 22:36

3

When you expose an open DataReader, the reponsibility of closing it along with the underlying Connection belongs now to the caller, so you should not dispose anything. Instead you should use the DbCommand.ExecuteReaderAsync overload that accepts a CommandBehavior argument, and pass the CommandBehavior.CloseConnection value:

When the command is executed, the associated Connection object is closed when the associated DataReader object is closed.

Then you can just hope that the caller will play by the rules and call the DataReader.Close method promptly, and will not let the connection open until the object is garbage collected. For this reason exposing an open DataReader should be considered an extreme performance optimization technique, that should be used sparingly.

Btw you would have the same problem if you returned an IEnumerable<IDataRecord> instead of an IAsyncEnumerable<IDataRecord>.

answered Nov 12 '19 at 22:36

Theodor Zoulias

34,835
7
69
104

While I didn't end up doing this, I think this is an interesting solution. Do you know when the command gets disposed? Does it just get disposed normally while reader and connection stay open? – user1325179 Nov 13 '19 at 15:16
If you don't close or dispose a connection, it will be closed when the garbage collector will recycle the object. The exact timing is undefined, since the GC process is non-deterministic. – Theodor Zoulias Nov 13 '19 at 16:54
Thank you. I understand that. I was asking about the command. The connection will be closed when the reader is closed. I'm wondering about the command. Thanks again. – user1325179 Nov 13 '19 at 17:39
It is the same with all disposable built-in objects. If you don't dispose them explicitly, they are disposed when they are recycled. – Theodor Zoulias Nov 13 '19 at 18:11
OK. I was wondering if the command would be disposed along with the connection when the reader is disposed. Maybe I can trying disposing of the command normally with a `using` statement within `GetRecordsAsync`. I'm not sure if this will trip up the reader since it will be used after the command is disposed. Maybe I'll experiment with it some day. Thank you. – user1325179 Nov 13 '19 at 18:26
Ah, now I understand your question. From what I understand the only critical resource that needs to be released ASAP is the `SqlConnection`, so that it can be returned to the connection pool and serve other requests. AFAIK the `SqlCommand` holds no other critical resources, so disposing it after releasing the connection is not important. – Theodor Zoulias Nov 13 '19 at 18:39

score 2 · Answer 4 · answered Oct 23 '20 at 20:58

To add to the other answers, you could make your utility method generic and add a projection delegate ,Func<IDataRecord, T> projection, as a parameter like this:

public static async IAsyncEnumerable<T> GetRecordsAsync<T>(
    string connectionString, SqlParameter[] parameters, string commandText,
    Func<IDataRecord, T> projection, // Parameter here
    [EnumeratorCancellation] CancellationToken cancellationToken)
{
    ...
                    yield return projection(reader); // Projected here
    ...
}

And then pass in a lambda or reference a method group such as this when calling:

public static object GetVideoId(IDataRecord dataRecord)
    => dataRecord["VideoID"];

such:

GetRecordsAsync(connectionString, parameters, commandText, GetVideoId, CancellationToken.None);

Nice idea. But this does not stop someone from projecting the `IDataRecord` to itself (`GetRecordsAsync(..., x => x, ...`), resulting to the same problem. Exposing `IDataRecord`s is inherently troublesome. At least your suggestion offers a way out for consumers that know what they are doing and play by the rules. — Theodor Zoulias, Oct 23 '20 at 21:19

score 2 · Answer 5 · answered Dec 31 '21 at 16:10

Way out here at the tail end of 2021 I had this exact question. I couldn't find a complete example, so I just messed around with what I could find until I got something to work.

Here is my code-complete, albeit simple (so you can expand it later) example, along with some comments detailing some of the pits I stepped into along the way:

// This function turns each "DataRow" into an object of Type T and yields
// it. You could alternately yield the reader itself for each row.
// In this example, assume sqlCommandText and connectionString exist.
public async IAsyncEnumerable<T> ReadAsync<T>( Func<SqlDataReader, T> func )
{
    // we need a connection that will last as long as the reader is open,
    // alternately you could pass in an open connection.
    using SqlConnection connection = new SqlConnection( connectionString );
    using SqlCommand cmd = new SqlCommand( sqlCommandText, connection );

    await connection.OpenAsync();
    var reader = await cmd.ExecuteReaderAsync();
    while( await reader.ReadAsync() )
    {
        yield return func( reader );
    }
}

Then in any other part of your (async) code you can call your function in an await foreach loop:

private static async Task CallIAsyncEnumerable()
{
    await foreach( var category in ReadAsync( ReaderToCategory ) )
    {
        // do something with your category; save it in a list, write it to disk,
        // make an HTTP call ... the whole world is yours!
    }
}

// an example delegate, which I'm passing into ReadAsync
private static Category ReaderToCategory( SqlDataReader reader )
{
    return new Category()
    {
        Code = ( string )reader[ "Code" ],
        Group = ( string )reader[ "Group" ]
    };
}

A couple other things I found: you can't yield from within a try, but you can stuff everything up to (and including) cmd.ExecuteReaderAsync() into a try, or a separate method that returns the DataReader. Or you can wrap the await foreach in a try block; I think the issue is in yielding to a caller outside the try (which makes sense, after you think about it).

If you use another method to generate the reader, pass the connection into that method, so you can control its lifetime. If your method creates the connection, executes the command, and returns the SqlDataReader, the connection will close (if you used a 'using`) before you can read from the reader. Again, this makes perfect sense if you think about it, but it tripped me up for a few minutes.

Good luck, and I hope this helps someone else in the future!

score 0 · Answer 6 · answered Mar 27 '23 at 15:13

I recommend something like this:

public async IAsyncEnumerable<Customer> GetByCity(string city)
{
   const string sql = "SELECT * FROM Customers WHERE City = @City";
    
   using var command = connection.CreateCommand();
   command.CommandText = sql;
   command.Parameters.AddWithValue("@City", city);
    
   if (connection.State == ConnectionState.Closed)
     await connection.OpenAsync();
    
   using SqlDataReader reader = await command.ExecuteReaderAsync();
    
    while (await reader.ReadAsync())
    {
        yield return Map(reader);
    }
}
    
private static Customer Map(SqlDataReader reader) => new Customer
{
   FirstName = (string) reader["FirstName"],
   LastName  = (string) reader["LastName"]
}

await foreach (var customer in customerRepository.GetByCity("Warsaw"))
{
   // ...
}

How to Return and Consume an IAsyncEnumerable with SqlDataReader

6 Answers6

Linked