Read multiple result sets using query multiple

Question

My procedure is returning two result sets/tables, I want to read those two result sets into two different lists and bind that list.

I'm able to read one result set into a list but I tried various ways to read multiple result sets into the dynamic list but it is giving me "TTC Error Oracle".

How to read multiple result sets using QueryMultiple?

Code for reading single result set into dynamic list:

public ResponseModel GetDoctorDetails(CustomerRequest data)
{
    try
    {
        var p = new OracleDynamicParameters();
        p.Add("p_parameter1", data.PARAMETER1, dbType: OracleDbType.Int32, direction: ParameterDirection.Input, size: 8);
        p.Add("p_parameter2", data.PARAMETER2, dbType: OracleDbType.Int32, direction: ParameterDirection.Input, size: 8);
        p.Add("p_out_cursor", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output, size: 0);
        using (var multi = _oracleConnection.QueryMultiple("procedure_name", param: p, commandType: CommandType.StoredProcedure))
        {
            List<dynamic> list = multi.Read<dynamic>().AsList();

            return new ResponseModel { ResultSet = list, StatusCode = 1, StatusDescription = "Success" };
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

I tried following ways to read multiple result sets but it is giving me "TTC Error"

List<DataTable> list = new List<DataTable>();
list = multi.Read<DataTable>().AsList();

and

List<List<dynamic>> list = new List<List<dynamic>>();
list = multi.Read<List<dynamic>>().AsList();

Please help.

Answer 1

Looks like you have to do 2 things.

First, you need a way to map with the spaces coming back. For that, you have to create your own mapper for Dapper to read in. You can see how to do that here:

Dapper. Map to SQL Column with spaces in column names

As for multiple queries, you need to just call Read multiple times for each set:

 using (var multi = _oracleConnection.QueryMultiple("procedure_name", param: p, commandType: CommandType.StoredProcedure))
        {
            List<dynamic> list1 = multi.Read<dynamic>().AsList();
            List<dynamic> list2 = multi.Read<dynamic>().AsList();
            List<dynamic> list3 = multi.Read<dynamic>().AsList();

            ...
        }