Dapper: Cannot insert boolean using oracle

[dvoaviarisonld]

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
            }
        }

[john-cullen]

@dvoaviarisonld I've had this exact same issue. This is the same root cause as https://github.com/StackExchange/Dapper/pull/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.

c# 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/