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?
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.