How to handle booleans with Oracle's Managed ODP.Net

user_463939 0
Viewed 903

I have a legacy application that until now has worked quite nicely with Microsoft's deprecated System.Data.OracleClient driver. C# boolean values are generally represented in its Oracle database as Number(5,0). For years this has worked quite serenely.

We are now attempting to migrate to Oracle's Managed ODP.Net replacement, and Booleans are proving particularly troublesome. Having replaced the Microsoft driver with the Oracle ODP.Net nuget package, I find that write operations with sql such as "insert.. where mybooleancolumn = true" (or parameterised equivalents) throw an 'undefined datatype' exception, and reading boolean values from the database ("Select mybooleancolumn from ....") gives all sort of errors when the values are assigned to C# booleans.

How do others overcome this problem? Is there some additional mapping that needs to be done?

odp.net c#
3 Answers

In the Oracle database I'm connecting to (using Oracle's Managed ODP.NET), the boolean are represented with a NUMBER(1, 0).

Here is the correct mapping code in my web.config that allows this mapping to work correctly:

<oracle.manageddataaccess.client>
    <version number="*">
      <edmMappings>
        <edmNumberMapping>
          <add NETType="bool" MinPrecision="1" MaxPrecision="1" DBType="Number" />
          <add NETType="byte" MinPrecision="2" MaxPrecision="2" DBType="Number" />
          <add NETType="int16" MinPrecision="3" MaxPrecision="4" DBType="Number" />
          <add NETType="int32" MinPrecision="5" MaxPrecision="9" DBType="Number" />
          <add NETType="int64" MinPrecision="10" MaxPrecision="19" DBType="Number" />
        </edmNumberMapping>
      </edmMappings>

      <settings>
       <setting name="TNS_ADMIN" value="PATH TO TNS_ADMIN" />
      </settings>
    </version>
  </oracle.manageddataaccess.client>

The important bit is:

<add NETType="bool" MinPrecision="1" MaxPrecision="1" DBType="Number" />

If you define such a mapping it should work.

EDIT: Don't forget to refresh your edmx model (just update it with no changes) and to rebuild the application to make sure it is updated using the new mappings.

user_1537970

The way I do it set the OracleParameter's OracleDbType to Int32 but set it's Value to a Boolean. e.g.

var param = new OracleParameter("mybooleancolumn", OracleDbType.Int32);
param.Value = true;
user_28448290

I made it work by adding mapping in config:

    <oracle.manageddataaccess.client>
        <version number="*">
            <edmMappings>
                <edmMapping dataType="number">
                    <add name="bool" precision="1"/>
                </edmMapping>
            </edmMappings>
        </version>
    </oracle.manageddataaccess.client>

And setting OracleCommand's UseEdmMapping to true. Something like this:

string oradb = "Data Source=ORCL;User Id=hr;Password=hr;";
OracleConnection conn = new OracleConnection(oradb);
conn.Open(); 
OracleCommand cmd = new OracleCommand();
cmd.UseEdmMapping = true; // # THIS IS WHAT I HAD TO SET TO TRUE #
cmd.Connection = conn;
cmd.CommandText = "select department_name from departments where department_id = 10";
cmd.CommandType = CommandType.Text; 
OracleDataReader dr = cmd.ExecuteReader();
dr.Read();
label1.Text = dr.GetString(0);
conn.Dispose();
user_25986760
Related