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.

You need to do `multi.Read` twice. https://dapper-tutorial.net/querymultiple — Chetan, Apr 25 '19 at 12:30
They're reading using model, I want to read those result sets dynamically in a list because my result set contains column like "Doctor ID" and I can't bind "Doctor ID" into my model directly because the column name contains space so I want to read it in a dynamic list. — Shreyas Pednekar, Apr 25 '19 at 12:50
You can not avoid space in the column name? Did you try doing `multi.Read().AsList();` twice? How will you access property with space in dynamic object? — Chetan, Apr 25 '19 at 12:52
I don't have permission to do changes in database so I can't avoid it. and I tried `multi.Read().AsList();` twice it is giving an error in first `multi.Read().AsList();`. and I can loop through dynamic list using IDictionary and read column value into variable like this `row["Doctor ID"];` — Shreyas Pednekar, Apr 25 '19 at 13:01

score 2 · Accepted Answer · answered Apr 25 '19 at 13:55

2

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();

            ...
        }

answered Apr 25 '19 at 13:55

Daniel Lorenz

4,178
1
32
39

It worked, but now it is giving me "TTC Error" while binding the result sets to lists, if both result sets contains data. – Shreyas Pednekar Apr 30 '19 at 04:27
I think you need to enable multiple active result sets in the connection string. – Daniel Lorenz May 01 '19 at 13:41
How to do it in oracle connection string using .net core? – Shreyas Pednekar May 02 '19 at 05:05
Because I tried setting `"CAD": "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.50)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DEVDB)));User Id=CADILA;Password=CADILA123;Connection Lifetime=60000;Max Pool Size=50;Min Pool Size=0;MultipleActiveResultSets=true;",` but it is giving me an error `'MultipleActiveResultSets' is an invalid connection string attribute` – Shreyas Pednekar May 02 '19 at 05:07
if you are using oracle, you have to take a different approach: https://stackoverflow.com/questions/18772781/using-dapper-querymultiple-in-oracle – Daniel Lorenz May 02 '19 at 12:41

Read multiple result sets using query multiple

1 Answers1