C# Dapper: Getting multiple result sets from stored procedure?

Question

My SP returns data like below. When I use dapper QueryMultipleAsync, it seems to be picking only 2nd result set and when use queryAsync it picks only first result set. Please suggest. Thanks in advance.

col1    col2    col3
123      name   23.34

time    value   
25:17.0 123 
25:17.0 124 
25:17.0 543 
25:17.0 566 

col1    col2    col3
123     name1   23.34

time    value   
25:17.0 123 
25:17.0 124 
25:17.0 543 
25:17.0 566 

Answer 1

When you use QueryMulitpleAsync you can read result sets one by one. Here is an example that works for me:

[Test]
public async Task MultipleSpResultsWithDapper()
{
    // Act
    using (var conn = new SqlConnection("Data Source=YourDatabase"))
    {
        await conn.OpenAsync();
        var result = await conn.QueryMultipleAsync(
            "YourStoredProcedureName",
            new { param1 = 1, param2 = 2 }, 
            null, null, CommandType.StoredProcedure);

        // read as IEnumerable<dynamic>
        var table1 = await result.ReadAsync();
        var table2 = await result.ReadAsync();

        // read as typed IEnumerable
        var table3 = await result.ReadAsync<Table1>();
        var table4 = await result.ReadAsync<Table2>();

        //Assert
        Assert.IsNotEmpty(table1);
        Assert.IsNotEmpty(table2);
        Assert.IsNotEmpty(table3);
        Assert.IsNotEmpty(table4);
    }
}

Entity classes:

public class Table1
{
    public int col1 { get; set; }

    public string col2 { get; set; }

    public double col3 { get; set; }
}

public class Table2
{
    public string time { get; set; }

    public int value { get; set; }
}

Stored procedure declaration:

CREATE PROCEDURE [dbo].YourStoredProcedureName
(
    @param1 int, 
    @param2 int
)

Hope it helps.