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?
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.
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());
}
}
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why