How to handle InvalidCastException in DataSet.Fill when data exceeds allowable precision?

Question

When running Oracle SQL that results in high precision data the Fill command triggers an InvalidCastException with message 'Specified Cast is not valid'. How can we allow the Fill to continue and truncate data to double?

I have tried using the FillErrorEventHandler but the FillErrorEventArgs doesn't contain the values from the database so I cannot cast them to double like I wanted.

Here's the SQL I tested with and the resultant data given when running the query in Oracle Developer.

SQL = select 45/5454 as dummy1 from dual

Data = 0.008250825082508250825082508250825082508251

When running in debugger, args.Values[0] = null (it is type object) but I expected it to contain the data. I wanted to cast Data to double and insert the row manually. Is this possible or is it a limitation that cannot be worked around?

public DataSet GetDataSetFromSQL(string SQL) {
        var ds = new DataSet();
        using (var cmd = OpenCommand(SQL)) {
            using (var da = new OracleDataAdapter(cmd)) {
                da.FillError += new FillErrorEventHandler(FillError);
                da.Fill(ds);
            }
        }
        return ds;
    }

    protected static void FillError(object sender, FillErrorEventArgs args) {
        if (args.Errors.GetType() == typeof(System.InvalidCastException)) {
            // Manually add row truncating double to max precision  
            int length = args.Values.Length;
            object[] myRow = new object[length];

            for (int i = 0; i < length; i++) {
                if (args.Values[i] is double) {
                    myRow[i] = (double)args.Values[i];
                }
                else {
                    myRow[i] = args.Values[i];
                }
            }

            args.DataTable.Rows.Add(myRow);
            args.Continue = true;
        }
    }

Answer 1

Have you tried using the Decimal data type?

Float: 7 digits (32 bit)
Double: 15-16 digits (64 bit)
Decimal: 28-29 significant digits (128 bit)