IAsyncEnumerable ADO.NET Example

IAsyncEnumerable.cs

public static async IAsyncEnumerable<IEnumerable<object?>> GetResults(this SqlConnection connection, string query, [EnumeratorCancellation] CancellationToken token)
{
    if (string.IsNullOrWhiteSpace(query))
    {
        throw new ArgumentException(nameof(query));
    }
    var wasClosed = connection.State != ConnectionState.Open;
    try 
    {
        if (wasClosed)
        {
            await connection.OpenAsync(token);
        }

        using var command = new SqlCommand(query, connection);
        using var reader = await command.ExecuteReaderAsync(token);
        if (reader.HasRows)
        {
            while (!token.IsCancellationRequested && await reader.ReadAsync(token))
            {
                var row = new List<object?>(reader.FieldCount);
                for (var i = 0; i < reader.FieldCount; i++)
                {
                    var value = await reader.IsDBNullAsync(i) ? null : reader.GetValue(i);
                    row.Add(value);
                }
                yield return row;
            }
        }
    }
    finally
    {
        if(wasClosed)
        {
            connection?.Close();
        }
    }            
}