Instantly share code, notes, and snippets.

Embed
What would you like to do?
OracleDynamicParameters class for Dapper
using Dapper;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
public class OracleDynamicParameters : Dapper.SqlMapper.IDynamicParameters {
private static Dictionary<SqlMapper.Identity, Action<IDbCommand, object>> paramReaderCache = new Dictionary<SqlMapper.Identity, Action<IDbCommand, object>>( );
private Dictionary<string, ParamInfo> parameters = new Dictionary<string, ParamInfo>( );
private List<object> templates;
private class ParamInfo {
public string Name { get; set; }
public object Value { get; set; }
public ParameterDirection ParameterDirection { get; set; }
public OracleDbType? DbType { get; set; }
public int? Size { get; set; }
public IDbDataParameter AttachedParam { get; set; }
}
/// <summary>
/// construct a dynamic parameter bag
/// </summary>
public OracleDynamicParameters ( ) {
}
/// <summary>
/// construct a dynamic parameter bag
/// </summary>
/// <param name="template">can be an anonymous type or a DynamicParameters bag</param>
public OracleDynamicParameters ( object template ) {
AddDynamicParams( template );
}
/// <summary>
/// Append a whole object full of params to the dynamic
/// EG: AddDynamicParams(new {A = 1, B = 2}) // will add property A and B to the dynamic
/// </summary>
/// <param name="param"></param>
public void AddDynamicParams (
#if CSHARP30
object param
#else
dynamic param
#endif
) {
var obj = param as object;
if ( obj != null ) {
var subDynamic = obj as OracleDynamicParameters;
if ( subDynamic == null ) {
var dictionary = obj as IEnumerable<KeyValuePair<string, object>>;
if ( dictionary == null ) {
templates = templates ?? new List<object>( );
templates.Add( obj );
} else {
foreach ( var kvp in dictionary ) {
#if CSHARP30
Add(kvp.Key, kvp.Value, null, null, null);
#else
Add( kvp.Key, kvp.Value );
#endif
}
}
} else {
if ( subDynamic.parameters != null ) {
foreach ( var kvp in subDynamic.parameters ) {
parameters.Add( kvp.Key, kvp.Value );
}
}
if ( subDynamic.templates != null ) {
templates = templates ?? new List<object>( );
foreach ( var t in subDynamic.templates ) {
templates.Add( t );
}
}
}
}
}
/// <summary>
/// Add a parameter to this dynamic parameter list
/// </summary>
/// <param name="name"></param>
/// <param name="value"></param>
/// <param name="dbType"></param>
/// <param name="direction"></param>
/// <param name="size"></param>
public void Add (
#if CSHARP30
string name, object value, DbType? dbType, ParameterDirection? direction, int? size
#else
string name, object value = null, OracleDbType? dbType = null, ParameterDirection? direction = null, int? size = null
#endif
) {
parameters[ Clean( name ) ] = new ParamInfo( ) { Name = name, Value = value, ParameterDirection = direction ?? ParameterDirection.Input, DbType = dbType, Size = size };
}
private static string Clean ( string name ) {
if ( !string.IsNullOrEmpty( name ) ) {
switch ( name[ 0 ] ) {
case '@':
case ':':
case '?':
return name.Substring( 1 );
}
}
return name;
}
void SqlMapper.IDynamicParameters.AddParameters ( IDbCommand command, SqlMapper.Identity identity ) {
AddParameters( command, identity );
}
/// <summary>
/// Add all the parameters needed to the command just before it executes
/// </summary>
/// <param name="command">The raw command prior to execution</param>
/// <param name="identity">Information about the query</param>
protected void 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, false );
paramReaderCache[ newIdent ] = appender;
}
}
appender( command, template );
}
}
foreach ( var param in parameters.Values ) {
string name = Clean( param.Name );
bool add = !( ( OracleCommand ) command ).Parameters.Contains( name );
OracleParameter p;
if ( add ) {
p = ( ( OracleCommand ) command ).CreateParameter( );
p.ParameterName = name;
} else {
p = ( ( 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.OracleDbType = param.DbType.Value;
}
if ( add ) {
command.Parameters.Add( p );
}
param.AttachedParam = p;
}
}
/// <summary>
/// All the names of the param in the bag, use Get to yank them out
/// </summary>
public IEnumerable<string> ParameterNames {
get {
return parameters.Select( p => p.Key );
}
}
/// <summary>
/// Get the value of a parameter
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="name"></param>
/// <returns>The value, note DBNull.Value is not returned, instead the value is returned as null</returns>
public T Get<T> ( string name ) {
var val = parameters[ Clean( name ) ].AttachedParam.Value;
if ( val == DBNull.Value ) {
if ( default( T ) != null ) {
throw new ApplicationException( "Attempting to cast a DBNull to a non nullable type!" );
}
return default( T );
}
return ( T ) val;
}
}
@caoyang1024

This comment has been minimized.

Show comment
Hide comment
@caoyang1024

caoyang1024 Aug 7, 2014

This method takes 3 parameters.

//appender = SqlMapper.CreateParamInfoGenerator(newIdent, false, false);

caoyang1024 commented Aug 7, 2014

This method takes 3 parameters.

//appender = SqlMapper.CreateParamInfoGenerator(newIdent, false, false);

@caoyang1024

This comment has been minimized.

Show comment
Hide comment
@caoyang1024

caoyang1024 Aug 7, 2014

hi vijay, pls take a look at this question

http://stackoverflow.com/questions/23145558

caoyang1024 commented Aug 7, 2014

hi vijay, pls take a look at this question

http://stackoverflow.com/questions/23145558

@marede

This comment has been minimized.

Show comment
Hide comment
@marede

marede Oct 13, 2016

hi vijay, pls take a look at this question http://stackoverflow.com/questions/23145558

I stumbled on the same problem.
You need to declare all Oracle.ManagedDataAccess.Client param properties with output parameters with OracleDbType different from .RefCursor.

ODP.NET example:
cmd.Parameters.Add(new OracleParameter("out_res", OracleDbType.Int32, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Current, null)); --> WORKING
cmd.Parameters.Add(new OracleParameter("out_res", OracleDbType.Int32, 0, ParameterDirection.Output)); --> NOT WORKING

So you need to expose the properties in the ParamInfo and obviously in the .Add method

marede commented Oct 13, 2016

hi vijay, pls take a look at this question http://stackoverflow.com/questions/23145558

I stumbled on the same problem.
You need to declare all Oracle.ManagedDataAccess.Client param properties with output parameters with OracleDbType different from .RefCursor.

ODP.NET example:
cmd.Parameters.Add(new OracleParameter("out_res", OracleDbType.Int32, 0, ParameterDirection.Output, false, 0, 0, "", DataRowVersion.Current, null)); --> WORKING
cmd.Parameters.Add(new OracleParameter("out_res", OracleDbType.Int32, 0, ParameterDirection.Output)); --> NOT WORKING

So you need to expose the properties in the ParamInfo and obviously in the .Add method

@koxkox

This comment has been minimized.

Show comment
Hide comment

koxkox commented Feb 27, 2018

@epaulsen

This comment has been minimized.

Show comment
Hide comment
@epaulsen

epaulsen Apr 27, 2018

Needed this for work, but needed it to support both Oracle.DataAccess and Oracle.ManagedDataAccess.
Took your gist and changed it to use reflection. Also added CollectionType, ArrayBindCount and BindByName, and all the other properties that can be added directly to a OracleParameter.
Repo here: https://github.com/DIPSASA/Dapper.Oracle
Nuget package here: https://www.nuget.org/packages/Dapper.Oracle/

epaulsen commented Apr 27, 2018

Needed this for work, but needed it to support both Oracle.DataAccess and Oracle.ManagedDataAccess.
Took your gist and changed it to use reflection. Also added CollectionType, ArrayBindCount and BindByName, and all the other properties that can be added directly to a OracleParameter.
Repo here: https://github.com/DIPSASA/Dapper.Oracle
Nuget package here: https://www.nuget.org/packages/Dapper.Oracle/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment