Cannot insert boolean using oracle

[DVoavi-Arison]

sqlQuery variable

insert into SomeTable (
 SomeBoolValue // number(1, 0)
) values (
 :SomeBoolValue
);

Dapper call

Class SomeTable {
 bool SomeBoolValue
}
...
var values = new SomeTable{SomeBoolValue = false};
this.conn.ExecuteAsync(sqlQuery, values );

That is causing an exception.

I tried to create a BoolTypeHandler but it was not even called by dapper

 {
 ...
 SqlMapper.AddTypeHandlerImpl(typeof(bool), new BoolTypeHandler(), true);
 ...
 }

 public class BoolTypeHandler : SqlMapper.TypeHandler<bool>
 {
 public override void SetValue(IDbDataParameter parameter, bool value)
 {
 parameter.Value = value ? (byte)1 : (byte)0;
 }

 public override bool Parse(object value)
 {
 return bool.Parse((string)value); // This is just an example
 }
 }

@dvoaviarisonld I've had this exact same issue. This is the same root cause as #458 but there isn't a released version that includes an equivalent change yet.

For now you will need to build it yourself from source. I would recommend checking out the code for the version you have installed and applying one of the TypeHandler fixes yourself.

[rhubley]

Have you tried casting it to an Int? For enums I wish to store as String instead of int I will cast them as string in the values collection, I don't see why that wouldn't work here.

Class SomeTable {
 bool SomeBoolValue
}
...
var values = new SomeTable{SomeBoolValue = false};
this.conn.ExecuteAsync(sqlQuery, new { SomeBoolValue = (int)values.SomeBoolValue});

[ben-at-sparq]

Only solution I've found to this problem is creating a function in the database;

create or replace function bool_to_num(value in boolean) return number
is
begin

 if (value is null) then
 return null;
 elsif (value) then
 return 1;
 else
 return 0;
 end if;

end;
/

You can then continue to used named parameters in your SQL;

insert into table (column) values (bool_to_num(:value))

[daconglee]

I use dapper 1.60,oracle 11g

 SqlMapper.RemoveTypeMap(typeof(bool));
 SqlMapper.AddTypeHandler(typeof(bool), new BoolTypeHandler());

TypeHandlerTests.cs

[ben-at-sparq]

I use dapper 1.60,oracle 11g

 SqlMapper.RemoveTypeMap(typeof(bool));
 SqlMapper.AddTypeHandler(typeof(bool), new BoolTypeHandler());

TypeHandlerTests.cs

Thanks for that, worked a treat!

[NickCraver]

For anyone hitting this today, check out Dapper.Oracle which has boolean type handlers built-in to save a bit of time: https://github.com/DIPSAS/Dapper.Oracle/