将 Dapper 与返回游标的 Oracle 存储过程一起使用

Using Dapper with Oracle stored procedures which return cursors

提问by hark

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

如何将Dapper与返回游标的 Oracle 存储过程一起使用？

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.

这里，DbType 是 System.Data.DbType，它没有 Cursor 成员。我试过使用 DbType.Object 但这不适用于 OracleClient 和 OracleDataAcess。

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

使用 OracleType 或 OracleDbType 的可能方法是什么？

采纳答案by Sam Saffron

You would have to implement:

你将不得不实施：

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

Then in the AddParameterscallback you would cast the IDbCommandto an OracleCommandand add the DB specific params.

然后在AddParameters回调中，您将转换IDbCommand为 anOracleCommand并添加数据库特定的参数。

回答by Daniel Smith

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

感谢这里的解决方案。我使用一个简单的 DynamicParameter 装饰器用更少的代码实现了同样的事情：

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

回答by Nimesh khatri

Add thisclass 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;
        }

回答by Stephen Patten

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.

只是为了详细说明 Sams 的建议，这就是我想出的。请注意，此代码很脆弱，现在仅适用于 Oracle。

Modified Dapper 1.7

修改后的 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; }
}