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