locked
How to get data from oracle stored procedure with two output (refCursor and varchar2) parameters using dapper RRS feed

  • Question

  • Hi,

    I am using Dapper in my application

    I have a oracle stored procedure with two output parameters refCursor and varchar2 

    I am using QueryMultiple to get this refCursor output value to list object and varchar2 output value to string.

    Public Function GetData(ByVal empId As String) As IList(Of Employee)
    
            Dim employeeList As IList(Of Employee) = Nothing
            Dim sql As String = "GetEmployeeData"
            Using conn As New OracleConnection(Connection)
                Dim parameters As New OracleDynamicParameters()
                parameters.Add("Emp_Id", value:=empId , dbType:=OracleDbType.Varchar2, direction:=ParameterDirection.Input)
                parameters.Add("Error_Message", value:=Nothing, dbType:=OracleDbType.Varchar2, direction:=ParameterDirection.Output)
                parameters.Add("Emp_Data", value:=Nothing, dbType:=OracleDbType.RefCursor, direction:=ParameterDirection.Output)
                conn.Open()
    			
                Using multiResults = conn.QueryMultiple(sql, parameters, commandType:=CommandType.StoredProcedure)
                    Dim errorMessage = multiResults.Read(Of String)().FirstOrDefault
                    employeeList = multiResults.Read(Of Employee).ToList
                End Using
    
            End Using
            Return employeeList
    
    End Function

    but after reading first parameter if we try to read second parameter I am getting error.

    if I write two QueryMultiple then i am able to read

          Using multiResults = conn.QueryMultiple(sql, parameters, commandType:=CommandType.StoredProcedure)
               Dim defaultData = multiResults.Read(Of String)().FirstOrDefault
          End Using
    
          Using multiResults = conn.QueryMultiple(sql, parameters, commandType:=CommandType.StoredProcedure)
               employeeList = multiResults.Read(Of Employee).ToList
          End Using

    but in this code i am using two db calls 

    how to get the data with one querymultiple 

    please help.....

    thanks in advance.....

    • Moved by Youjun Tang Monday, September 21, 2015 6:34 AM
    Tuesday, September 8, 2015 3:26 PM

Answers

All replies

  • I don't see anything in the VB side that would cause a problem.  It looks like you'll need to ask this on the support page for Dapper, as the issue appears to be specific to that framework.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Tuesday, September 8, 2015 10:20 PM
  • You might go to post you issue at here: https://github.com/StackExchange/dapper-dot-net/issues

    • Proposed as answer by Just Karl Wednesday, September 23, 2015 10:42 PM
    • Marked as answer by Dave PatrickMVP Sunday, September 27, 2015 2:24 PM
    Wednesday, September 16, 2015 10:15 AM