C# move oracle nullable int to sql server nullable int using dataset

Question

I have a table with a nullable int field in Oracle named "EmployeeNumber". When i attempt to assign this value to its matching field in SQL Server using an xsd dataset, I receive a can't convert null to int.

sqlEmployeeDT.AddEmployeeRow(dr.CM_REALM, 
dr.CM_CO, dr.EM_FILE_NO, dr.EM_LOCATION, dr.EM_EMPNO)                                                                               

This does not appear to be happening at the point I try to assign the value (addemployeerow) it's happening when I try to read the value, in a strongly typed int column, from Oracle (dr.EM_EMPNO) because Oracle is returning the field value as null but C# is demanding an int.

I've looked for an answer but can't find anything. Most answers are talking about null vs DBNull.Value. But this issue seems to be between Oracle and C#.

I can check the Oracle value before insert using dr.IsEm_EMPNONull() but then I'm faced with inserting the null into a SQL Server int field using sqlEmployeeDT.AddEmployeeRow which produces a "can't convert null to int" on the insert statement.

Is there no straightforward way of doing this?

Answer 1

There could be any number of things going wrong here. You example doesn't show quite enough information to make clear what's going wrong. The most likely explanation is that the AddEmployeeRow method has the fifth parameter defined as type int, which requires a integer, not a null, and not a DBNull.Value.

You might want to check sqlEmployeeDT.Columns["EM_EMPNO"].AllowDBNull to ensure that this is true.

An alternative approach might be to use

DataRow row = sqlEmployeeDT.NewRow();
row["CM_REALM"] = dr.CM_REALM;  //etc.
row["EM_EMPNO"] = DBNull.Value;
sqlEmployeeDT.Rows.Add(row);