how to create IDbDataParameter with null-able value?

Question

I have add a null value to parameter list for inserting values to a table, which accepts some null values. Here is my example codes:

 bool sql = true;
 // ....
 List<IDbDataParameter> parameters = new List<IDbDataParmeter>();
 // adding values...
 object objVal = 1;
 parameters.Add(
    sql ? new SqlParameter("@colIntA", objVal) : 
          new OracleParamer(":colIntA", objVal));
 // this would not work, what should I add?
 objVal = string.Empty;
 parameters.Add(
    sql ? new SqlParameter("@colIntB", objVal) : 
          new OracleParamer(":colIntB", objVal));

Not sure if I have use db specific DbNull value and is that also SQL or Oracle specific?

Answer 1

Use DbNull.Value. That will work for any ADO.NET-compatible data source (both SQL and Oracle providers), as it is the responsibility of the provider to know what to do when it encounters a DbNull value.

Answer 2

You're trying to assign an empty string ('') to an int parameter there, so yeah, that's not going to work.

To represent a database-independent null value, use DbNull.Value.

 new SqlParameter("colIntB", DbNull.Value)

(Note that I've left off the "@", which works in my experience with Sqlparameters. I'm not sure whether you can do the same with the ":" for Oracle.)

One extra tip: Use your connection to create the command, and the command to create the parameters. That will create instances of the right class depending on the type of the underlying connection:

IDbConnection conn = sql ? new SqlConnection(...) : new OracleConnection(...);

// this will give you either an SqlCommand or an OracleCommand
IDbCommand cmd = conn.CreateCommand();

// this will give you either an SqlParameter or an OracleParameter
IDbDataParameter param1 = cmd.CreateParameter();
param1.ParameterName = "colIntB";
param1.Value = objVal;

cmd.Parameters.Add(param1);