Oracle.DataAccess DataRow.Field<decimal> InvalidCastException

Question

In my code I was using System.Data.OracleClient for ora database connection. I would like to replace this library (because it is obsolete) with Oracle.DataAccess. Unfortunately I found that DataRow.Field() throws InvalidCastException. Same behavior is with (decimal)x.Rows[0]["COLUME_NAME"]. I do not have this issue with System.Data.OracleClient.

Here is code example

using (var oracleConnection = new OracleConnection(connectionString))
{
    using (var command = new OracleCommand("select * from tr", oracleConnection))
    {
        var result = new DataTable();
        var adapter = new OracleDataAdapter(command);
        adapter.Fill(result);
        Console.WriteLine(result.Rows[0].Field<decimal>("TR_SEQ_NUM"));
        //Console.WriteLine((decimal)result.Rows[0]["TR_SEQ_NUM"]);
    }
}

TR_SEQ_NUM has NUMBER(8,0) datatype and full exception is:

System.InvalidCastException: Specified cast is not valid.
   at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value)

Code example working with System.Data.OracleClient but not with Oracle.DataAccess

I know that I can use Convert.ChangeType but I wonder if there is some way to have same behavior as with System.Data.OracleClient. Refactoring of all of my code will too much time expensive.

Answer 1

The value in the database is not a decimal and a boxed int (the 'value' parameter in the error message) cannot be cast to a decimal, despite casting an int to a decimal being OK. This answer leads to more info.

You can see it in action with this:

void Main()
{
    int anInt = 5;
    object boxedInt = (object)anInt;
    decimal unboxed = Convert.ToDecimal(boxedInt); //5
    decimal unboxed2 = (decimal)boxedInt; //InvalidCastException
}

If you look at the Unbox.ValueField method, it does...

  private static T ValueField(object value)
  {
    if (DBNull.Value == value)
      throw DataSetUtil.InvalidCast(Strings.DataSetLinq_NonNullableCast((object) typeof (T).ToString()));
    return (T) value;
  }

Basically you'll need to

Convert.ToDecimal(rows[0]["TR_SEQ_NUM"]);