20

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
  • 203
  • 1
  • 3
  • 4
  • I'm not hugely familiar with Oracle cursors; AFAIK we didn't add any specific support for such. Can you point me at an example of doing this *without* dapper? – Marc Gravell Sep 12 '11 at 15:17
  • Sure, have a look here: http://msdn.microsoft.com/en-us/library/ms971506.aspx#msdnorsps_topic8 – hark Sep 12 '11 at 18:08
  • 1
    SOLUTION IS HERE https://gist.github.com/vijaysg/3096151 – Timeless Aug 07 '14 at 06:44

4 Answers4

12

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
  • 309
  • 3
  • 9
  • 1
    This is still relevant in 2023. I had to change a few things, but this solution got me 95% there. I also had to set `oracleCommand.BindByName = false` explicitly in AddParameters or else I would get the Oracle "Not all variables bound" error. – furman87 Feb 05 '23 at 20:01
  • 2023 here!, when I add this class the stored procedure runs perfectly! thx – BlackSD Mar 24 '23 at 20:46
9

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.

Sam Saffron
  • 128,308
  • 78
  • 326
  • 506
  • Thanks, that was it. What I did is pass in a DbType.Object in the DynamicParameters which I then check for and set the parameter as an OracleDbtype of RefCursor in the AddParameters implementation. It's nice that I didn't have to modify much of Dapper for this. – hark Sep 13 '11 at 14:22
  • 1
    How would you do this in such a fashion as to not edit the sqlmapper file so that upgrades are smoother, I've noticed that the main class is partial, but the implementation of SqlMapper.IDynamicParameters in the DynamicParameters is not marked as virtual. – Stephen Patten Jan 10 '12 at 18:45
  • @SPATEN not following, I don't really want people inheriting off DynamicParameters, standalone implementations of IDynamicParameters should be just fine, dapper calls the interface – Sam Saffron Jan 11 '12 at 23:30
  • @SPATEN I think you are asking for this: http://code.google.com/p/dapper-dot-net/issues/detail?id=69 – Sam Saffron Jan 12 '12 at 00:32
  • Thanks Sam, while we're on the topic of Dapper, can you take a look at the call to conn.Query, is there anyway to return N number of results, e.g. conn.Query(...) So our current example returns 2 cursors. – Stephen Patten Jan 12 '12 at 02:59
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;
        }
Nimesh khatri
  • 763
  • 12
  • 29
3

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; }
}
Stephen Patten
  • 6,333
  • 10
  • 50
  • 84
  • 1
    You can use the above code and QueryMultiple to get multiple outputs out of the stored procedure (as given under the heading 'Multiple Results' at http://code.google.com/p/dapper-dot-net/) –  Jul 11 '12 at 11:43
  • I don't believe that changes anything, at least at the time I logged this feature, it's a bit deeper problem with Dapper and more specifically Oracle. – Stephen Patten Jul 11 '12 at 16:24
  • 1
    http://blog.vijay.name/index.php/2012/07/11/dapper-micro-orm-for-oracle-and-microsoft-net/ –  Jul 12 '12 at 06:55
  • Very good code, I stand corrected, plus I'm bit embarrassed by my implementation vs. that one. Thank you! BTW is that your code? – Stephen Patten Jul 12 '12 at 17:06
  • Vijay, can you answer this question out right so that I can close this question by accepting your answer? – Stephen Patten Apr 25 '13 at 13:49
  • Sorry, I don't get it. I can see in the (great) blog article "Getting the result out from an Oracle Stored Procedure with the help of Dapper seemed very easy after just adding a custom implementation of Dapper.SqlMapper.IDynamicParameters which supports Oracle.DataAccess.Client.OracleDbType." ... but no clue as to WHAT that custom implementation should be... what am I missing? – kpollock Oct 21 '14 at 08:55