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

    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 08, 2015 3:26 PM

Answers

All replies