如何使用dapper和c＃检索多个sys_refcursor？

Question

我想使用Dapper从Oracle DB中检索多个SYS_REFCURSOR。 我的PL/SQL代码下面

procedure GetData2 (p_result out sys_refcursor, p_result2 out sys_refcursor)
 is
 begin
   open p_result for
        select '1' col1, '2' col2 from dual
        union all
        select '11' col1, '22' col2 from dual;
        
   open p_result2 for
        select '3' col1, '4' col2 from dual;
 end;

，我的C＃代码在此C＃代码下方

using OracleConnection connection = new OracleConnection();
connection.ConnectionString = "";
connection.Open();
    
    
OracleDynamicParameters parameters = new OracleDynamicParameters();
parameters.Add(name: "p_result", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
parameters.Add(name: "p_result2", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
    
var reader = connection.QueryMultiple("TEST_PACKAGE.GetData2", param: parameters, commandType: CommandType.StoredProcedure);
    
IEnumerable<MyClasss> dt = reader.Read<MyClasss>();
    
foreach (var item in dt)
{
    Console.WriteLine(item.col1);
}

仅给我P_Result表结果。 如何使用dapper检索两个sys_refcursor结果？

Answer 1

您必须调用一种方法，以转移到数据读取器中的下一个结果。该方法称为.nextresult（）。
尝试以下代码：

using OracleConnection connection = new OracleConnection();
connection.ConnectionString = "";
connection.Open();
    
    
OracleDynamicParameters parameters = new OracleDynamicParameters();
parameters.Add(name: "p_result", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
parameters.Add(name: "p_result2", dbType: OracleMappingType.RefCursor, direction: ParameterDirection.Output);
    
var reader = connection.QueryMultiple("TEST_PACKAGE.GetData2", param: parameters, commandType: CommandType.StoredProcedure);
    
IEnumerable<MyClasss> dt = reader.Read<MyClasss>();
    
foreach (var item in dt)
{
    Console.WriteLine(item.col1);
}
reader.NextResult();

IEnumerable<MyClasss> dt2 = reader.Read<MyClasss>();
    
foreach (var item in dt2)
{
    Console.WriteLine(item.col1);
}