asp.net-core-webapidappersys-refcursor

Unable to get any result set in .NET Core Web API while using Dapper


I having trouble to get any output in one of .NET Core Web API project. .NET SDK: Version: 7.0.400 Commit: 73bf45718d Below is one of service which connects to DB and supposed to return result but it is not giving output even though SP is working fine.

using EmployeeUsingDapper.Model;
using Dapper;
using Oracle.ManagedDataAccess.Client;
using Microsoft.Extensions.Configuration;
using System.Data;

namespace EmployeeUsingDapper.Services
{
    public class EmployeeListService : IEmployeeListService
    {
        private readonly IDatabaseService _databaseService;
        private readonly ILogger<EmployeeListService> _logger;

        public EmployeeListService(ILogger<EmployeeListService> logger, IDatabaseService databaseService)
        {
            _databaseService = databaseService;
            _logger = logger;
        }

        public async Task<(IEnumerable<Employee>, CallStatus?) > GetEmployeeList(InputParametersForSearch inputParameters)
        {
            try
            {
                using var connection = _databaseService.GetConnection();
                var parameters = new OracleDynamicParameters();

                //Input Parameters
                parameters.Add("iName", OracleDbType.Char, ParameterDirection.Input, inputParameters.UserName);
                parameters.Add("ino", OracleDbType.Int32, ParameterDirection.Input, inputParameters.no);
                parameters.Add("iPassport", OracleDbType.Char, ParameterDirection.Input, inputParameters.Passport);
                parameters.Add("iID", OracleDbType.Int32, ParameterDirection.Input, inputParameters.LOEID);
                parameters.Add("iLastName", OracleDbType.Char, ParameterDirection.Input, inputParameters.LastName);
                parameters.Add("iFirstName", OracleDbType.Char, ParameterDirection.Input, inputParameters.FirstName); 

                //Output Parameters
                parameters.Add("oCallStatus", oracleDbType: Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor, ParameterDirection.Output);
                parameters.Add("EMPLISTCURSOR", oracleDbType: Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor, ParameterDirection.Output);

                var result = await connection.QueryMultipleAsync("getEmployeeList", parameters, commandType: CommandType.StoredProcedure);

                var emps = await result.ReadAsync<Employee>().ConfigureAwait(false);
                var emplist = emps.ToList();
                var callStatus = await result.ReadFirstOrDefaultAsync<CallStatus>().ConfigureAwait(false);

                //Log the values
                _logger.LogInformation($"emps: {emplist.LongCount()}");
                _logger.LogInformation($"Call status: {callStatus?.Status}, message: {callStatus?.Message}, ErrorID: {callStatus?.ErrorID}");
                
                return (emplist, callStatus);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Error executing stored procedure.");
                return (null, null);
            }
        }
    }
}

Below is Employee class

public class Employee
{
    public int? NOM { get; set; }    
}

Below is my stored procedure. When i tested this SP, it works fine and returns result but in .NEt Core it return null

PROCEDURE getEmployeeList (iName           IN     VARCHAR2,
                           ino             IN     NUMBER,
                           iPassport       IN     VARCHAR2,
                           iID             IN     NUMBER,
                           iLastName       IN     VARCHAR2,
                           iFirstName      IN     VARCHAR2,
                           oCallStatus        OUT SYS_REFCURSOR,
                           EMPLISTCURSOR      OUT SYS_REFCURSOR)
IS
    vInputParam   VARCHAR2 (4000);
BEGIN
    vInputParam :=
           'iUserName:'
        || iUserName
        || ' ino:'
        || ino
        || ' iPassport:'
        || iPassport
        || ' ID:'
        || iID
        || ' iLastName:'
        || iLastName
        || ' iFirstName:'
        || iFirstName;

    --log input parameters

    OPEN EMPLISTCURSOR FOR SELECT empno     NOM
                             FROM employee
                            WHERE ROWNUM < 10;

    OPEN oCallStatus FOR
        SELECT 'Y' STATUS, NULL MESSAGE, NULL ERROR_ID FROM DUAL;
EXCEPTION
    WHEN OTHERS
    THEN
        gSqlErrorCode := SQLCODE;
        gSqlErrorMessage := SQLERRM;

        OPEN oCallStatus FOR
            SELECT 'N'                                         STATUS,
                   'Error occured while getting employee list'     MESSAGE,
                   REPLACE (gSqlErrorCode, 'ORA', '')          ERROR_ID
              FROM DUAL;

        CLOSE EMPLISTCURSOR;
--log Error
END getEmployeeList;

Below is OracleDynamicParameters class

public class OracleDynamicParameters : SqlMapper.IDynamicParameters
{
    private readonly DynamicParameters dynamicParameters = new DynamicParameters();
    private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();

    public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null)
    {
        OracleParameter oracleParameter;
        if (size.HasValue)
        {
            oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction);
        }
        else
        {
            oracleParameter = new OracleParameter(name, oracleDbType, value, direction);
        }

        oracleParameters.Add(oracleParameter);
    }

    public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction)
    {
        var oracleParameter = new OracleParameter(name, oracleDbType, direction);
        oracleParameters.Add(oracleParameter);
    }

    public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
    {
        ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity);

        var oracleCommand = command as OracleCommand;

        if (oracleCommand != null)
        {
            oracleCommand.Parameters.AddRange(oracleParameters.ToArray());
        }
    }

    public T Get<T>(string name)
    {
        return dynamicParameters.Get<T>(name);
    }
}

Solution

  • Swap the position for getting the result from the output parameters for callStatus and emps.

    var callStatus = await result.ReadFirstOrDefaultAsync<CallStatus>().ConfigureAwait(false);
    
    var emps = await result.ReadAsync<Employee>().ConfigureAwait(false);
    var emplist = emps.ToList();