Handle NULL values when reading through OracleDataReader?

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?

Contents hide

Answers:

Method 1

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating