Using Dapper with Oracle stored procedures which return cursors

Question

Using Dapper with Oracle stored procedures which return cursors

.net c# dapper oracle

Question

How would one go about using Dapper with Oracle stored procedures which return cursors?

var p = new DynamicParameters();
p.Add("foo", "bar");
p.Add("baz_cursor", dbType: DbType.? , direction: ParameterDirection.Output);

Here, the DbType is System.Data.DbType which does not have a Cursor member. I've tried using DbType.Object but that does not work with both OracleClient and OracleDataAcess.

What would be a possible way to use OracleType or OracleDbType instead?

hark

Popular Answer

Thanks for the solution here. I achieved the same thing with a little less code using a simple DynamicParameter decorator:

public class OracleDynamicParameters : SqlMapper.IDynamicParameters
{
    private readonly DynamicParameters dynamicParameters = new DynamicParameters();

    private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>();

    public void Add(string name, object value = null, DbType? dbType = null, ParameterDirection? direction = null, int? size = null)
    {
        dynamicParameters.Add(name, value, dbType, direction, size);
    }

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

Daniel Smith

View more on Stack Overflow

Accepted Answer

You would have to implement:

 public interface IDynamicParameters
 {
    void AddParameters(IDbCommand command, Identity identity);
 }

Then in the AddParameters callback you would cast the IDbCommand to an OracleCommand and add the DB specific params.

Using Dapper with Oracle stored procedures which return cursors

Question

Accepted Answer

Popular Answer

Performance

Expression Evaluator