QueryMultiple Result Set Order Changed

Question

I am executing a stored procedure using QueryMultiple to return multiple sets of data.

var gridReader = db.QueryMultiple("sp", 
                                  parameters, 
                                  commandType: CommandType.StoredProcedure);

I can very easily get each set given I know the order they will come back in.

SELECT * FROM dbo.Set1;
SELECT * FROM dbo.Set2;
SELECT * FROM dbo.Set3;

var set1 = gridReader.Read<Set1>();
var set2 = gridReader.Read<Set2>();
var set3 = gridReader.Read<Set3>();

However, I am in a situation where the order they will come back in may change. Another developer could decide to change the order for whatever reason. The stored procedure now becomes this:

SELECT * FROM dbo.Set1;
SELECT * FROM dbo.Set3;
SELECT * FROM dbo.Set2;

How can I handle this?

My initial attempt was to iterate each grid, checking the column names. This seemed to work well at first, but I wasn't able to figure out how to then project the grid into a class, besides manually setting each field. The main reason I'm using Dapper is so it can do this for me.

while (true)
{
    var grid = gridReader.Read();
    IDictionary<string, object> row = grid.FirstOrDefault();

    if (row == null)
        break;

    if (row.Keys.Contains("Set1_UniqueColumnName"))
    {
        // Need something like grid.Read<Set1>();
    }
    else if (row.Keys.Contains("Set2_UniqueColumnName")) { }
    else if (row.Keys.Contains("Set3_UniqueColumnName")) { }
}

My second idea was to read each grid into a class, check the unique fields of the class for nulls/default values, and trying the next class if the test failed. This obviously won't work though. .Read() will return the next grid of results. This solution would require me to be able to read the same grid over and over.

Answer 1

Davmos's answer pointed me in the right direction. Needed to use a combination of ADO.NET and Dapper. Essentially use ADO.NET to retrieve and iterate through the data, but use Dapper to parse the rows into my objects. Note the use of FieldCount in the while loop in case a result set actually does return 0 rows. We want it to move on to the next result set, not break out of the loop.

Set1 set1 = null;
var set2 = new List<Set2>();
Set3 set3 = null;

using (var command = new SqlCommand("sp", conn))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddRange(parameters);
    command.Connection.Open();

    using (var reader = command.ExecuteReader())
    {
        while (reader.FieldCount > 0)
        {
            var set1Parser = reader.GetRowParser<Set1>();
            var set2Parser = reader.GetRowParser<Set2>();
            var set3Parser = reader.GetRowParser<Set3>();

            var isSet1 = HasColumn(reader, "Set1_UniqueColumnName");
            var isSet2 = HasColumn(reader, "Set2_UniqueColumnName");
            var isSet3 = HasColumn(reader, "Set3_UniqueColumnName");

            while (reader.Read())
            {
                if (isSet1)
                {
                    set1 = set1Parser(reader);
                }
                else if (isSet2)
                {
                    set2.Add(set2Parser(reader));
                }
                else if (isSet3)
                {
                    set3 = set3Parser(reader);
                }
            }

            reader.NextResult();
        }
    }
}

public static bool HasColumn(IDataReader reader, string columnName)
{
    for (var i = 0; i < reader.FieldCount; i++)
    {
        if (reader.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
        {
            return true;
        }
    }

    return false;
}