Consider NULL values ​​when reading through OracleDataReader?

Question

I am working on my first ASP.Net application and seem to run into a lot of obstacles (mainly in WinForms with the recent MVC5 project under my belt).

I successfully connect my DB connection using OracleCommand and executing my query, but when I try to read the rows, I get Column contains NULL value in the second row for odr.GetDecimal(1) . Does anyone know how to handle null values ​​when reading through 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 thought of a simple check for NULL, and if so replaced with 0 (since the Decimal value is expected) it will work with the following, but no luck. Same error: yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1)) ? 0 : odr.GetDecimal(1); yos.ServiceCredited = Convert.IsDBNull(odr.GetDecimal(1)) ? 0 : odr.GetDecimal(1); .

Full error:

An exception of type "System.InvalidCastException" occurred in Oracle.DataAccess.dll, but was not processed in the user code

Additional Information: Column contains NULL data

I confirmed that my 2 rows will be returned in the following format:

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

Anyone have any tips on how best to act? How should I handle getting NULL when reading through my OracleDataReader?

Answer 1

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

OracleDataReader provides the IsDBNull() method.

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

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