Using Dapper with Oracle stored procedures which r

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?

Answer 1

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.

Answer 2

Just to elaborate on Sams suggestion here's what I came up with. Note that this code is brittle and is now just for Oracle.

Modified Dapper 1.7

void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, SqlMapper.Identity identity)
    {
        if (templates != null)
        {
            foreach (var template in templates)
            {
                var newIdent = identity.ForDynamicParameters(template.GetType());
                Action<IDbCommand, object> appender;

                lock (paramReaderCache)
                {
                    if (!paramReaderCache.TryGetValue(newIdent, out appender))
                    {
                        appender = SqlMapper.CreateParamInfoGenerator(newIdent);
                        paramReaderCache[newIdent] = appender;
                    }
                }

                appender(command, template);
            }
        }

        foreach (var param in parameters.Values)
        {
            string name = Clean(param.Name);
            bool add = !((Oracle.DataAccess.Client.OracleCommand)command).Parameters.Contains(name);
            Oracle.DataAccess.Client.OracleParameter p;
            if(add)
            {
                p = ((Oracle.DataAccess.Client.OracleCommand)command).CreateParameter();
                p.ParameterName = name;
            } else
            {
                p = ((Oracle.DataAccess.Client.OracleCommand)command).Parameters[name];
            }

            var val = param.Value;
            p.Value = val ?? DBNull.Value;
            p.Direction = param.ParameterDirection;
            var s = val as string;
            if (s != null)
            {
                if (s.Length <= 4000)
                {
                    p.Size = 4000;
                }
            }
            if (param.Size != null)
            {
                p.Size = param.Size.Value;
            }
            if (param.DbType != null)
            {
                p.DbType = param.DbType.Value;    
            }
            if (add)
            {
                if (param.DbType != null && param.DbType == DbType.Object)
                {
                    p.OracleDbType = Oracle.DataAccess.Client.OracleDbType.RefCursor;
                    ((Oracle.DataAccess.Client.OracleCommand)command).Parameters.Add(p);
                }
                else
                {
                    ((Oracle.DataAccess.Client.OracleCommand)command).Parameters.Add(p);
                }                       
            }
            param.AttachedParam = p;
        }
    }

Test code

class Program
{
    static void Main(string[] args)
    {
        OracleConnection conn = null;
        try
        {
            const string connString = "DATA SOURCE=XE;PERSIST SECURITY INFO=True;USER ID=HR;PASSWORD=Adv41722";

            conn = new OracleConnection(connString);
            conn.Open();


            var p = new DynamicParameters();
            p.Add(":dep_id", 60);
            p.Add(":employees_c", dbType: DbType.Object, direction: ParameterDirection.Output);
            p.Add(":departments_c", dbType: DbType.Object, direction: ParameterDirection.Output);
            // This will return an IEnumerable<Employee> // How do I return both result?
            var results = conn.Query<Employee>("HR_DATA.GETCURSORS", p, commandType: CommandType.StoredProcedure);



        }
        catch (Exception exception)
        {
            Console.WriteLine(exception);
            throw;
        }
        finally
        {
            if (conn != null && conn.State == ConnectionState.Open)
            {
                conn.Close();
            }                
        }
        Console.WriteLine("Fininhed!");
        Console.ReadLine();
    }
}

class Employee
{
    public int Employee_ID { get; set; }
    public string FIRST_NAME { get; set; }
    public string LAST_NAME { get; set; }
    public string EMAIL { get; set; }
    public string PHONE_NUMBER { get; set; }
}

Answer 3

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

Answer 4

Add this class to your project

and your code should like below :-

        var p = new OracleDynamicParameters();
        p.Add("param1", pAuditType);
        p.Add("param2", pCommnId);
        p.Add("outCursor", dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);

        using (var multi = cnn.QueryMultiple("procedure_name", param: p, commandType: CommandType.StoredProcedure))
        {
            var data = multi.Read();
            return data;
        }