Map Dapper query multiple to strongly typed object?

Question

I have the following sproc:

CREATE PROCEDURE [dbo].[dapper_test_sproc] 

AS 
    BEGIN
        SET NOCOUNT ON ;

        DECLARE @Status INT = 1
        DECLARE @Msg VARCHAR(256) = 'Hello World'

        DECLARE @StatusLines TABLE
        (
          rule_code VARCHAR(20),
          rule_name VARCHAR(30),
          [status] INT,
          rule_error_msg VARCHAR(256)
        )

        INSERT INTO @StatusLines
        VALUES ('TEST1', 'TEST2', 1, 'TEST3')

        SELECT  @Status,
                @Msg

        SELECT  rule_code,
                rule_name,
                status,
                rule_error_msg
        FROM    @StatusLines
        FOR     XML RAW
    END

At the end of the sproc you can see two result sets.

I have used the following code in LinqPad to successfully query the data but I am struggling to get individual values out of the data. I do not understand the return types.

using var connection = this.Connection;

var sql = "dapper_test_sproc";

var p = new DynamicParameters();

var grid = await connection
    .QueryMultipleAsync(
        new CommandDefinition(
            sql,
            p,
            commandType: CommandType.StoredProcedure,
            cancellationToken: this.QueryCancelToken));

LINQPad.Extensions.Dump(grid.Read());
LINQPad.Extensions.Dump(grid.Read());

LinqPad shows the following information:

For example, calling grid.Read() does not return the full @Status and @Msg from the first result set. But I found out that grid.ReadSingle() does. And I have no idea why.

grid.ReadSingle()["Status"] throws an error Cannot apply indexing with [] to an expression of type 'object' okay so its an object, but what kind of object? And why cant I access any properties?

I now have working code, but the code is horribly ugly. The casting, and accessing elements at indexes seems highly error prone. Is there a better way to do this?

public async Task dapper_test_sproc()
{

    using var connection = this.Connection;

    var sql = "dapper_test_sproc";

    var p = new DynamicParameters();

    var grid = await connection
        .QueryMultipleAsync(
            new CommandDefinition(
                sql,
                p,
                commandType: CommandType.StoredProcedure,
                cancellationToken: this.QueryCancelToken));

    var firstResultSet = await grid.ReadSingleAsync();
    var secondResultSet = await grid.ReadSingleAsync();

    var firstResultSetDict = (IDictionary<String,Object>)firstResultSet;
    var statusBit = (int)firstResultSetDict.ElementAt(0).Value;
    var message = (string)firstResultSetDict.ElementAt(1).Value;
        
    var secondResultSetDict = (IDictionary<String,Object>)secondResultSet;
    var statusLinesXML = (string)secondResultSetDict.Single().Value;
    
    var passwordValidationStatusMessage = new PasswordValidationStatusMessage
    {
        Error = !Convert.ToBoolean(statusBit),
        Message = message
    };

    XmlSerializer serializer = new XmlSerializer(typeof(StatusLinesEnvelope));
    using var reader = new StringReader($"<root>{statusLinesXML}</root>");
    var statusLines = (StatusLinesEnvelope)serializer.Deserialize(reader);

    LINQPad.Extensions.Dump(passwordValidationStatusMessage);
    LINQPad.Extensions.Dump(statusLines);
}

[XmlRoot(ElementName = "row", Namespace = "")]
public class StatusLines
{
    [XmlAttribute(AttributeName = "rule_code")]
    public string RuleCode { get; set; }

    [XmlAttribute(AttributeName = "rule_name")]
    public string RuleName { get; set; }

    [XmlAttribute(AttributeName = "status")]
    public bool Status { get; set; }

    [XmlAttribute(AttributeName = "rule_error_msg")]
    public string RuleErrorMsg { get; set; }
}

[XmlRoot(ElementName = "root")]
public class StatusLinesEnvelope
{
    [XmlElement(ElementName = "row")]
    public List<StatusLines> Row { get; set; }
}

public class PasswordValidationStatusMessage
{
    public bool Error { get; set; }
    public string Message  { get; set; }
}

Answer 1

I do a little change to your Stored and added column names.

ALTER PROCEDURE [dbo].[dapper_test_sproc] 

AS 
    BEGIN
        SET NOCOUNT ON ;

        DECLARE @Status INT = 1
        DECLARE @Msg VARCHAR(256) = 'Hello World'

        DECLARE @StatusLines TABLE
        (
          rule_code VARCHAR(20),
          rule_name VARCHAR(30),
          [status] INT,
          rule_error_msg VARCHAR(256)
        )

        INSERT INTO @StatusLines
        VALUES ('TEST1', 'TEST2', 1, 'TEST3')

        -- Add column names
        SELECT  @Status [Status],
                @Msg [Msg]

        -- Add column names
        select (
            SELECT  rule_code,
                    rule_name,
                    status,
                    rule_error_msg
            FROM    @StatusLines
            FOR     XML RAW
        ) as XmlValue
    END

Now the code

using System.Data.SqlClient;
using Dapper;

var c = "connection_string";
var sql = "EXEC [dbo].[dapper_test_sproc]";

using (var connection = new SqlConnection(c))
{
    var res = await connection.QueryMultipleAsync(sql);
    var first = res.Read<First>().ToList();
    var second = res.Read<Second>().First();
    
    first.Dump();
    second.Dump();
}

class First
{
    public int Status {get; set;}
    
    public string Msg {get; set;}
}

class Second 
{
    public string XmlValue {get; set;}
}

Update without ColumnName

Without a column name, you must get the values using the index positioning (also who uses the sproc), naming columns is always a good idea.

using (var connection = new SqlConnection(c))
{
    var res = await connection.QueryMultipleAsync(sql);
    var first = res.Read();
    var second = res.Read();

    var data1 = first.ToList().Select(p => (IDictionary<string, object>)p)
                .Select(o => 
                    new First { 
                        Status = (int)o.Values.ToArray()[0], 
                        Msg = (string)o.Values.ToArray()[1] 
                    }
                );

        
    var data2 = second.ToList().Select(p => (IDictionary<string, object>)p)
                .Select(o => 
                    new Second { 
                        XmlValue = (string)o.Values.ToArray()[0] 
                    }
                );

    data1.Dump();
    data2.Dump();
}