Storing .NET double value in Oracle DB

Question

I'm using ODP.NET to access Oracle DB from C# .NET. Please see following code:

  OracleConnection con = new OracleConnection();
  con.ConnectionString = "User Id=user;Password=pass;Data Source=localhost/orcl";
  con.Open();

  /* create table */
  DbCommand command = con.CreateCommand();
  command.CommandType = CommandType.Text;

  try
  {
    command.CommandText = "DROP TABLE TEST";
    command.ExecuteNonQuery();
  }
  catch
  {
  }

  //command.CommandText = "CREATE TABLE TEST (VALUE BINARY_DOUBLE)";
  command.CommandText = "CREATE TABLE TEST (VALUE FLOAT(126))";
  command.ExecuteNonQuery();

  /* now insert something */
  double val = 0.8414709848078965;
  command.CommandText = "INSERT INTO TEST VALUES (" + val.ToString(System.Globalization.CultureInfo.InvariantCulture) + ")";
  command.ExecuteNonQuery();

  /* and now read inserted value */
  command.CommandText = "SELECT * FROM TEST";
  DbDataReader reader = command.ExecuteReader();

  reader.Read();
  double res = (double) (decimal)reader[0];

  Console.WriteLine("Inserted " + val + " selected " + res);

The output from this is always: Inserted 0,841470984807897 selected 0,841470984807897

But looking at variable values under debugger val == 0.8414709848078965 res == 0,841470984807897

Why res is rounded up?

I looked into DB and there is stored rounded-up value.

On the other hand I used Oracle SQL Developer to modify this value, and I'm able to store 0.8414709848078965 in database?

I tried types NUMBER, FLOAT(126), BINARY_DOUBLE... always the same result.

Why there is a problem using ODP.NET?

Answer 1

Oracle actually has a higher precision for it's numbers than .net!
I tried this in straight Oracle and it works fine, I recommend changing to use a param

e.g.

-- CREATE TABLE TEST (VALUE NUMBER(38,38)); (initial test)

INSERT INTO TEST VALUES (0.8414709848078965);

SELECT * FROM TEST;
VALUE                  
---------------------- 
0.8414709848078965 

(recommendation)
OracleParameter param = cmd.CreateParameter();  
param.ParameterName = "NUMBERVALUE";  
param.Direction = ParameterDirection.Input;  
param.OracleDbType = OracleDbType.Decimal;  
param.Value = "0.8414709848078965";  
command.Parameters.Add(param); 

Answer 2

OK, I have found that it works if parameter type is OracleDbType.BinaryDouble. But it causes my code to be dependent of ODP.NET. I wanted to use ADO.NET types (DbType) to achieve my code independency.