Handling null values in OracleCommand parameters

Question

I'm trying to determine how to specify null as a parameter value in an OracleCommand using the following C# code. I've excerpted the relevant bits, but basically the point is if sal_id comes in with the value 0 it should be stored as null. I've tried Null, "Null", and a couple other things but so far no luck.

cmd.CommandText = "INSERT INTO tcustomer(cust_id, salutation_id) VALUES(ORADBA.SEQCUST.NEXTVAL, :salid) RETURNING cust_id INTO :newcid" ;

if (sal_id==0) {
  cmd.Parameters.Add("salid", Null) ;
} else {
  cmd.Parameters.Add("salid", sal_id) ;
}

cmd.Parameters.Add("newcid", OracleDbType.Int32).Direction = ParameterDirection.ReturnValue ;

cmd.ExecuteNonQuery() ;

String newcidval = cmd.Parameters["newcid"].Value.ToString() ;
cmd.Dispose() ;

Answer 1

Try System.DBNull insead of null.

The DBNull class represents a nonexistent value. In a database, for example, a column in a row of a table might not contain any data whatsoever. That is, the column is considered to not exist at all instead of merely not having a value. A DBNull object represents the nonexistent column

Answer 2

DBNull can not be assigned to any other data type so have to implicit convert it to object.

cmd.Parameters.Add(new OracleParameter(":number_column", OracleType.Number)).Value = (s.Class.HasValue) ? s.Class.Value : (object)DBNull.Value;

where s.Class is int? and parameter value is set as (object)DBNull.Value in case it is null

Answer 3

DBNull.Value works for some but I needed to cast it to a Oracle Type that implemented INullable. I recommend this approach if you are using a Nullable data type:

    (Oracle.ManagedDataAccess.Types.OracleDecimal)command.Parameters["pKey"].Value)).IsNull

Answer 4

you can check Status of your OracleParameter object - if it equals to OracleParameterStatus.NullFetched, nothing was fetched, otherwise use param's Value attribute.

Example:

var _myParam = new OracleParameter( "p_param", OracleDbType.Object, ParameterDirection.Output ) { UdtTypeName = "my_schema.MY_TYPE" };

... (add command parameter)
... (execute command)

var _myResponse = _myParam.Status == OracleParameterStatus.NullFetched ? null : MapMyTypeFromOracleType( (MyType)_myParam.Value );