Return sql query results with deferred execution

Question

I have a method to execute an SQL statement and it returns an instance of my class ResultSet that contains the rows and columns returned. I only want to read the data row-by-row when the caller actually iterates over the results.

public class ResultSet {
    public IEnumerable<Row> Rows {
        get;
        private set;
    }
    public ResultSet(IEnumerable<Row> rows, IEnumerable<Column> columns) {
        Rows = rows;
        // columns and other code omitted
    }
}

For this, I tried passing an IEnumerable to the constructor of my class, but the problem is that the DbDataReader instance has already been disposed by the time you try to iterate over the Rows property of a ResultSet instance:

    public ResultSet Execute(string sql) {
        using (var command = Connection.CreateCommand()) {
            command.CommandText = sql;

            var reader = command.ExecuteReader();
            try {
                IEnumerable<Row> MyFunc()
                {
                    while (reader.Read())
                        yield return new Row(reader);
                }
                var columns = GetColums(...);
                return new ResultSet(MyFunc(), columns);
            } finally {
                reader.Dispose();
            }
        }
    }

I know I could pass the DbDataReader instance to my class and not dispose it in the Execute method but then I would have to make ResultSet disposable and I would like to avoid that, if possible. I'm not sure it's even possible what I'm trying to do?

I have looked at yield return statement inside a using() { } block Disposes before executing but it's not quite the same as it doesn't involve a disposable resources.

Create a custom `IEnumerable` that calls `ExecuteReader` when `GetEnumerator` is called, and no earlier. (Iterator methods can return `IEnumerator` as well, so this isn't as troublesome as it sounds.) Or, always an option, give this kind of manual ORM code a pass and use Dapper, which tends to fit these sorts of scenarios quite well. — Jeroen Mostert, Jul 23 '18 at 14:19
Or create a custom `IEnumerable` building custom `IEnumerator` that execute the query the first time `MoveNext` is called. — vc 74, Jul 23 '18 at 14:25
The reader already reads data row by row (unless it's a small set of records in which case, it will read them all in one shot, internally) so why do you need to use `yield`? (disclaimer: I dislike `yield`). — JuanR, Jul 23 '18 at 14:31
Either you defer the whole request (both rows and columns) or you will need 2 requests (one to eagerly load columns, one to lazily load rows). What do you prefer ? — Spotted, Jul 23 '18 at 15:24
@Spotted You're right, that makes sense now that I think about it. I think I will just opt to return the DbDataReader instance and have an extension methods for GetRows and GetColumns on that. — user3700562, Jul 23 '18 at 15:45
Just make sure you figure out who needs to dispose of the reader when no longer used. — JuanR, Jul 23 '18 at 16:15
@user3700562 I would however to discourage you to return a `DbDataReader` from any method for the aforementionned problem and also because this leads to bad code (strong coupling, leaky abstraction). — Spotted, Jul 24 '18 at 06:28

score 0 · Answer 1 · answered Jul 23 '18 at 14:47

To illustrate my comment:

public class Row
{
    public Row(IDataReader reader)
    { }
};

public class RowEnumerator : IEnumerator<Row>
{
    public RowEnumerator(IDbConnection connection, string SQL)
    {
        _command = connection.CreateCommand();
        _command.CommandText = SQL;

        _reader = _command.ExecuteReader();
    }
    private readonly IDbCommand _command;
    private readonly IDataReader _reader;

    public Row Current => new Row(_reader);

    object IEnumerator.Current => Current;

    public bool MoveNext() => _reader.Read();

    public void Reset() => throw new NotImplementedException();

    public void Dispose()
    {
        _reader.Dispose();
        _command.Dispose();
    }
}

public class RowEnumerable : IEnumerable<Row>
{
    public RowEnumerable(IDbConnection connection, string SQL)
    {
        _connection = connection;
        _SQL = SQL;
    }
    private readonly IDbConnection _connection;
    private readonly string _SQL;

    public IEnumerator<Row> GetEnumerator() => new RowEnumerator(_connection, _SQL);

    IEnumerator IEnumerable.GetEnumerator() => GetEnumerator();
}

The query is executed when a RowEnumerator is created, i.e. when RowEnumerable.GetEnumerator is called.

Is the enumerable is used in a foreach loop, the enumerator, and therefore the command and reader, will be disposed automatically. Otherwise you have to dispose it manually.

Thanks, you're right that's one way of doing it. In my case I think it's easiest just return the DbDataReader instance and have a couple of extension methods for GetRows and GetColumns on that. — user3700562, Jul 23 '18 at 15:47

Return sql query results with deferred execution

1 Answers1