8

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.

Simon Karlsson
  • 4,090
  • 22
  • 39
Václav Starý
  • 327
  • 4
  • 11
  • using Oracle.ManagedDataAccess.Client; //use the managed version What happens when you call tbl.Rows[0][0].GetType(); ? .Field isn't possble in the new version you use column indexes/names. You are going to have to refactor, System.Data.OracleClient is way out of date but the casting should be handled for you when you fill the table... – Matt Feb 09 '16 at 03:40
  • Also, Number(8,0) would not be a decimal, why are you casting it to one? It's very rare that Decimal's need to be used, not that it would cause the issue you are having but that might save you some memory when the app is running – Matt Feb 09 '16 at 03:48
  • I agree with _LessNoviceProgrammer_. I think the correct type for `NUMBER(8,0)` is `int`... – M Denis Mar 07 '17 at 13:56

2 Answers2

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"]);
Community
  • 1
  • 1
Llwyd
  • 106
  • 1
  • 3
0

Looks your data contains null value and you cannot convert a nullable data to non nullable decimal type

Please use the below statment to read the nullable decimal column from DB

result.Rows[0].Field<decimal?>("TR_SEQ_NUM")
Rameshkan B
  • 78
  • 9