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.

Answer 1

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.