PetaPoco - Multiple result set support

Question

My work recently starting using PetaPoco and although fantastic I missed the feature from Dapper which allowed multiple result grids from a single query to be processed into pocos.

As a result I wrote my own implementation for PetaPoco - shown below - but has anyone written their own and care to share it?

I thought there might be others out there who have missed this feature.

Answer 1

Edit (2016-06-27): As of version 5.1.181 of PetaPoco (currently a beta at the time of writing) this feature is now available in the PetaPoco NuGet Package

Info

I used the Dapper source as inspiration to implement this feature, so any similarities are due to that.

This makes changes directly to the PetaPoco.cs file, which you probably shouldn't do judging from the comment at the start!

This code has been tested but not thoroughly, please exercise appropriate caution!

Usage

private string _sql = @"SELECT * FROM WebOrders w INNER JOIN Address a ON a.Id = w.DeliveryAddressId WHERE OrderId = @0
                        SELECT * FROM WebOrderLines WHERE OrderId = @0 AND CustomerId = @1
                        SELECT * FROM ContactDetails WHERE CustomerId = @1";

private readonly Database _db = new Database("Shop");

var result = new WebOrder();

using (var multi = _db.QueryMultiple(_sql, 12345, 67890))
{
    result = multi.Read<WebOrder, Address, WebOrder>((w, a) => { w.Address = a; return w; }).Single();
    result.OrderLines = multi.Read<WebOrderLines>().ToList();
    result.ContactDetails = multi.Read<ContactDetails>().ToList();
}

return result;

Changes - PetaPoco (Core) v.5.0.1

Inside the class PetaPoco.Database add:

    #region operation: Multi-Result Set
    /// <summary>
    /// Perform a multi-results set query
    /// </summary>
    /// <param name="sql">An SQL builder object representing the query and it's arguments</param>
    /// <returns>A GridReader to be queried</returns>
    public GridReader QueryMultiple(Sql sql)
    {
        return QueryMultiple(sql.SQL, sql.Arguments);
    }

    /// <summary>
    /// Perform a multi-results set query
    /// </summary>
    /// <param name="sql">The SQL query to be executed</param>
    /// <param name="args">Arguments to any embedded parameters in the SQL</param>
    /// <returns>A GridReader to be queried</returns>
    public GridReader QueryMultiple(string sql, params object[] args)
    {
        OpenSharedConnection();

        GridReader result = null;

        var cmd = CreateCommand(_sharedConnection, sql, args);

        try
        {
            var reader = cmd.ExecuteReader();
            result = new GridReader(this, cmd, reader);
        }
        catch (Exception x)
        {
            if (OnException(x))
                throw;
        }
        return result;
    }
    #endregion

At the root level (namespace: PetaPoco) add:

#region Multi-Results Set GridReader
public class GridReader : IDisposable
{
    private IDataReader _reader;
    private IDbCommand _command;
    private readonly Database _db;

    /// <summary>
    /// The control structure for a multi-result set query
    /// </summary>
    /// <param name="database"></param>
    /// <param name="command"></param>
    /// <param name="reader"></param>
    internal GridReader(Database database, IDbCommand command, IDataReader reader)
    {
        _db = database;
        _command = command;
        _reader = reader;
    }

    #region public Read<T> methods

    /// <summary>
    /// Reads from a GridReader, returning the results as an IEnumerable collection
    /// </summary>
    /// <typeparam name="T">The Type representing a row in the result set</typeparam>
    /// <returns>An enumerable collection of result records</returns>
    public IEnumerable<T> Read<T>()
    {
        return SinglePocoFromIDataReader<T>(_gridIndex);
    }
    /// <summary>
    /// Perform a multi-poco read from a GridReader
    /// </summary>
    /// <typeparam name="T1">The first POCO type</typeparam>
    /// <typeparam name="T2">The second POCO type</typeparam>
    /// <returns>A collection of POCO's as an IEnumerable</returns>
    public IEnumerable<T1> Read<T1, T2>()
    {
        return MultiPocoFromIDataReader<T1>(_gridIndex, new Type[] {typeof (T1), typeof (T2)}, null);
    }
    /// <summary>
    /// Perform a multi-poco read from a GridReader
    /// </summary>
    /// <typeparam name="T1">The first POCO type</typeparam>
    /// <typeparam name="T2">The second POCO type</typeparam>
    /// <typeparam name="T3">The third POCO type</typeparam>
    /// <returns>A collection of POCO's as an IEnumerable</returns>
    public IEnumerable<T1> Read<T1, T2, T3>()
    {
        return MultiPocoFromIDataReader<T1>(_gridIndex, new Type[] {typeof (T1), typeof (T2), typeof (T3)}, null);
    }
    /// <summary>
    /// Perform a multi-poco read from a GridReader
    /// </summary>
    /// <typeparam name="T1">The first POCO type</typeparam>
    /// <typeparam name="T2">The second POCO type</typeparam>
    /// <typeparam name="T3">The third POCO type</typeparam>
    /// <typeparam name="T4">The forth POCO type</typeparam>
    /// <returns>A collection of POCO's as an IEnumerable</returns>
    public IEnumerable<T1> Read<T1, T2, T3, T4>()
    {
        return MultiPocoFromIDataReader<T1>(_gridIndex,
                                            new Type[] {typeof (T1), typeof (T2), typeof (T3), typeof (T4)}, null);
    }
    /// <summary>
    /// Perform a multi-poco query
    /// </summary>
    /// <typeparam name="T1">The first POCO type</typeparam>
    /// <typeparam name="T2">The second POCO type</typeparam>
    /// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
    /// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
    /// <returns>A collection of POCO's as an IEnumerable</returns>
    public IEnumerable<TRet> Read<T1, T2, TRet>(Func<T1, T2, TRet> cb)
    {
        return MultiPocoFromIDataReader<TRet>(_gridIndex, new Type[] {typeof (T1), typeof (T2)}, cb);
    }
    /// <summary>
    /// Perform a multi-poco query
    /// </summary>
    /// <typeparam name="T1">The first POCO type</typeparam>
    /// <typeparam name="T2">The second POCO type</typeparam>
    /// <typeparam name="T3">The third POCO type</typeparam>
    /// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
    /// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
    /// <returns>A collection of POCO's as an IEnumerable</returns>
    public IEnumerable<TRet> Read<T1, T2, T3, TRet>(Func<T1, T2, T3, TRet> cb)
    {
        return MultiPocoFromIDataReader<TRet>(_gridIndex, new Type[] {typeof (T1), typeof (T2), typeof (T3)}, cb);
    }
    /// <summary>
    /// Perform a multi-poco query
    /// </summary>
    /// <typeparam name="T1">The first POCO type</typeparam>
    /// <typeparam name="T2">The second POCO type</typeparam>
    /// <typeparam name="T3">The third POCO type</typeparam>
    /// <typeparam name="T4">The forth POCO type</typeparam>
    /// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
    /// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
    /// <returns>A collection of POCO's as an IEnumerable</returns>
    public IEnumerable<TRet> Read<T1, T2, T3, T4, TRet>(Func<T1, T2, T3, T4, TRet> cb)
    {
        return MultiPocoFromIDataReader<TRet>(_gridIndex,
                                              new Type[] {typeof (T1), typeof (T2), typeof (T3), typeof (T4)}, cb);
    }

    #endregion

    #region PocoFromIDataReader

    /// <summary>
    /// Read data to a single poco
    /// </summary>
    /// <typeparam name="T">The type representing a row in the result set</typeparam>
    /// <param name="index">Reader row to be read from the underlying IDataReader</param>
    /// <returns></returns>
    private IEnumerable<T> SinglePocoFromIDataReader<T>(int index)
    {
        if (_reader == null)
            throw new ObjectDisposedException(GetType().FullName, "The data reader has been disposed");
        if (_consumed)
            throw new InvalidOperationException(
                "Query results must be consumed in the correct order, and each result can only be consumed once");
        _consumed = true;

        var pd = PocoData.ForType(typeof (T));
        try
        {
            while (index == _gridIndex)
            {
                var factory =
                    pd.GetFactory(_command.CommandText, _command.Connection.ConnectionString, 0, _reader.FieldCount,
                                  _reader) as Func<IDataReader, T>;

                while (true)
                {
                    T poco;
                    try
                    {
                        if (!_reader.Read())
                            yield break;
                        poco = factory(_reader);
                    }
                    catch (Exception x)
                    {
                        if (_db.OnException(x))
                            throw;
                        yield break;
                    }

                    yield return poco;
                }
            }
        }
        finally // finally so that First etc progresses things even when multiple rows
        {
            if (index == _gridIndex)
            {
                NextResult();
            }
        }
    }

    /// <summary>
    /// Read data to multiple pocos
    /// </summary>
    /// <typeparam name="TRet">The type of objects in the returned IEnumerable</typeparam>
    /// <param name="index">Reader row to be read from the underlying IDataReader</param>
    /// <param name="types">An array of Types representing the POCO types of the returned result set.</param>
    /// <param name="cb">A callback function to connect the POCO instances, or null to automatically guess the relationships</param>
    /// <returns>A collection of POCO's as an IEnumerable</returns>
    private IEnumerable<TRet> MultiPocoFromIDataReader<TRet>(int index, Type[] types, object cb)
    {
        if (_reader == null)
            throw new ObjectDisposedException(GetType().FullName, "The data reader has been disposed");
        if (_consumed)
            throw new InvalidOperationException(
                "Query results must be consumed in the correct order, and each result can only be consumed once");
        _consumed = true;

        try
        {
            var cmd = _command;
            var r = _reader;

            var factory = MultiPocoFactory.GetFactory<TRet>(types, cmd.Connection.ConnectionString, cmd.CommandText,
                                                            r);
            if (cb == null)
                cb = MultiPocoFactory.GetAutoMapper(types.ToArray());
            bool bNeedTerminator = false;

            while (true)
            {
                TRet poco;
                try
                {
                    if (!r.Read())
                        break;
                    poco = factory(r, cb);
                }
                catch (Exception x)
                {
                    if (_db.OnException(x))
                        throw;
                    yield break;
                }

                if (poco != null)
                    yield return poco;
                else
                    bNeedTerminator = true;
            }
            if (bNeedTerminator)
            {
                var poco = (TRet) (cb as Delegate).DynamicInvoke(new object[types.Length]);
                if (poco != null)
                    yield return poco;
                else
                    yield break;
            }
        }
        finally
        {
            if (index == _gridIndex)
            {
                NextResult();
            }
        }
    }

    #endregion

    #region DataReader Management

    private int _gridIndex;
    private bool _consumed;

    /// <summary>
    /// Advance the IDataReader to the NextResult, if available
    /// </summary>
    private void NextResult()
    {
        if (!_reader.NextResult()) return;
        _gridIndex++;
        _consumed = false;
    }

    /// <summary>
    /// Dispose the grid, closing and disposing both the underlying reader, command and shared connection
    /// </summary>
    public void Dispose()
    {
        if (_reader != null)
        {
            if (!_reader.IsClosed && _command != null) _command.Cancel();
            _reader.Dispose();
            _reader = null;
        }

        if (_command != null)
        {
            _command.Dispose();
            _command = null;
        }
        _db.CloseSharedConnection();
    }

    #endregion
}
#endregion

I have submitted this change to the PetaPoco v5 branch but thought people might get some benefit if I posted it here.