Handle NULL values when reading through OracleDataReader?

Question

I'm working on my first ASP.Net application, and seem to be hitting a lot of hurdles (background primarily in WinForms with a recent MVC5 project under my belt).

I am successfully making my DB connection using OracleCommand and executing my query, but when I try reading through the rows I am getting a Column contains NULL value on the second row for odr.GetDecimal(1). Anyone know how to handle null values when reading through an OracleDataReader?

Below is my code:

        List<YearsOfService> yearsOfService = new List<YearsOfService>();
        string SQL = "SELECT SCHOOL_YEAR as YEAR, " +
                            "TOTAL_SERVICE_CREDIT as ServiceCredited, " +
                            "RETIREMENT_SALARY as Salary, " +
                            "SOURCE_VALUE as CoveredEmployer " +
                     "FROM " + Schema + ".RANDOM_ORACLE_TABLE a " +
                     "WHERE MEMBER_ACCOUNT_ID = :memberAccountId";

        DbConnection dbc = new DbConnection();
        OracleCommand cmd = dbc.GetCommand(SQL);
        cmd.Parameters.Add(new OracleParameter("memberAccountId", memberAccountId));
        OracleDataReader odr = cmd.ExecuteReader();

        int counter = 0;
        if (odr.HasRows)
        {
            while (odr.Read())
            {
                YearsOfService yos = new YearsOfService();
                yos.Year = odr.GetInt16(0);
                yos.ServiceCredited = odr.GetDecimal(1); // Error on Second Pass

                yos.Salary = odr.GetDecimal(2);

                yos.CoveredEmployer = odr.GetString(3);

                yearsOfService.Add(yos);
                counter++;
            }
        }

        return yearsOfService;
    }

I had thought a simple check for NULL and if so replace with 0 (since expecting a Decimal value) would work with the following, but no luck. Same error: yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1)) ? 0 : odr.GetDecimal(1);.

Full error is:

An exception of type 'System.InvalidCastException' occurred in Oracle.DataAccess.dll but was not handled in user code

Additional information: Column contains NULL data

I have confirmed that my 2 rows being returned are in the following format:

Year|CreditedService|Salary  |CoveredEmployer
2013|0.70128        |34949.66|ER
2014|NULL           | 2213.99|NULL

Anyone have advice on how best to proceed? How should I handle receiving the NULL value when reading through my OracleDataReader?

Solution

yos.ServiceCredited = odr.IsDBNull(1) ? 0 : odr.GetDecimal(1);

OracleDataReader provides a IsDBNull() method.

And the docs on GetDecimal() ask us to do this

Call IsDBNull to check for null values before calling this method.

Comments

[Analytic Lunatic]

Thanks Abhi, this did the trick! I had tried using the IsDBNull() method earlier, but I was implementing it incorrectly. Previously I think I tried something like yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1)). This is my first time using OracleDataReader and I'm inexperienced with how it does the "column" references using the different Get()'s. Thanks for your help!

[Black]

thanks for this. Anyone else think it's absolute madness that a Client has to make a pre-check call like this to find out if the value is Null before you read it? Why doesn't GetDecimal() (etc) just return a Null value like every other DB driver? Seems crazy

[EKW]

@Black I guess that's what we get for using Oracle?

[Andy Hames]

@Black Because a Decimal value cannot be null. If the method were able to return null as you describe, you would have to always check the result wasn't null anyway in order to assign it to a Decimal.