ASP.NET Core - The type or namespace name 'OracleDynamicParameters' could not be found

Question

In ASP.NET Core 6 with Dapper and an Oracle database, I have a stored procedure to use.

I have these Nuget Packages

Dapper Version 2.0.123 
Oracle.ManagedDataAccess.Core Version 3.21.100

However, I got this error:

Error CS0246
The type or namespace name 'OracleDynamicParameters' could not be found (are you missing a using directive or an assembly reference?)

This is my stored procedure:

create or replace PROCEDURE  "GET_STUDENT_DETAILS" (
        sUserID     IN VARCHAR DEFAULT NULL,
        rResultSet  OUT SYS_REFCURSOR
        )
AS
nCheck        NUMBER;
sUserIDC      VARCHAR(100);

BEGIN
    select instr(sUserID,'.') into nCheck from dual;

IF nCheck>0 THEN 

    SELECT CONCAT(sUserID,'@rotrim.com') into sUserIDC FROM DUAL;
    
    OPEN rResultSet FOR   
    SELECT SU."USER_ID",
          SU."USER_NAME",
          SU."FIRST_NAME",
          SU."LAST_NAME",
        CFUF."EMAIL"
      FROM PEPUSC.smtb_user su, PEPUSC.cstm_function_userdef_fields cfuf
      WHERE su."USER_ID"||'~'=cfuf."REC_KEY"
      AND cfuf."FUNCTION_ID"= 'SMDUSRDF'
      AND  SU. USER_NAME = sUserIDC
      AND SU.USER_STATUS = 'E';
      
ELSE 

OPEN rResultSet FOR
    SELECT SU."USER_ID",
          SU."USER_NAME",
          SU."FIRST_NAME",
          SU."LAST_NAME",
        CFUF."EMAIL"
      FROM PEPUSC.smtb_user su, PEPUSC.cstm_function_userdef_fields cfuf
      WHERE su."USER_ID"||'~'=cfuf."REC_KEY"
      AND cfuf."FUNCTION_ID"= 'SMDUSRDF'
      AND SU.USER_ID = sUserID
      AND SU.USER_STATUS = 'E';
END IF;
END;

This is my C# code:

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using Dapper;

public class StudentRepository : IStudentRepository
{
    private readonly IMapper _mapper;
    private readonly IConfiguration _configuration;
    private readonly ILogger<StudentRepository> _logger;

    public StudentRepository(IMapper mapper,
        ILogger<StudentRepository> logger,
        IConfiguration configuration)
    {
        _mapper = mapper;
        _logger = logger;
        _configuration = configuration;
    }

    public IDbConnection Connection
    {
        get { return new OracleConnection(_configuration.GetConnectionString("DefaultConnection")); }
    }

    public async Task<Response<StudentResponseDto>> GetStudentByUserIdAsync(string sUserID)
    {
        var response = new Response<StudentResponseDto>();

        try
        {
            using (IDbConnection _dbConnection = Connection)
            {
                _dbConnection.Open();
                //
                var parameters = new OracleDynamicParameters();
                parameters.Add("sUserID", sUserID, OracleDbType.Varchar2, ParameterDirection.Input);
                parameters.Add("rResultSet", OracleDbType.RefCursor, ParameterDirection.Output);

                string query = "GET_STUDENT_DETAILS";

                var student = await _dbConnection.QueryFirstOrDefaultAsync<StudentResponse>(query, parameters, commandType: CommandType.StoredProcedure);

                if (fcubsHeader != null)
                {
                    var studentDtos = _mapper.Map<StudentResponseDto>(fcubsHeader);
                    response.Successful = true;
                    response.Data = studentDtos;
                    response.StatusCode = (int)HttpStatusCode.OK;
                    return response;
                }
                else
                {
                    response.Message = $"Data Not Found!";
                    response.Successful = true;
                    response.StatusCode = (int)HttpStatusCode.NoContent;
                    return response;
                }
            }
        }
        catch (OracleException ex)
        {
            response.Successful = false;
            response.StatusCode = (int)HttpStatusCode.BadRequest;
            return response;
            throw;
        }
        catch (Exception ex)
        {
            response.Successful = false;
            response.StatusCode = (int)HttpStatusCode.BadRequest;
            return response;
            throw;
        }
    }
}

How do I resolve the issue?

Answer 1

It looks like the OracleDynamicParameters do not exist either from the implementation of Dapper or Oracle.

However, from some research, there is a developer who implements this OracleDynamicParameters to query the Oracle stored procedure with Dapper. You may have read this article: Call Oracle stored procedure with Dapper (C#) and implement it.

In case you are looking for a pure Dapper solution, you need to migrate the code:

1. Migrate from OracleDynamicParameters to DynamicParameters
2. Migrate from OracleDbType to DbType

From the OracleDbType documentation:

The OracleDbType property and DbType property are linked. Therefore, setting the OracleDbType property changes the DbType property to a supporting DbType.

You may refer to Inference of DbType from OracleDbType for the reference in switching OracleDbType to DbType.

using System.Data;

var parameters = new DynamicParameters();
parameters.Add("sUserID", sUserID, dbType: DbType.String, direction: ParameterDirection.Input);
parameters.Add("rResultSet", null, dbType: DbType.Object, direction: ParameterDirection.Output);