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?
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());
}
}
}
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.
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;
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With