Assign null to a SqlParameter

The accepted answer suggests making use of a cast. However, most of the SQL types have a special Null field which can be used to avoid this cast.

For example, SqlInt32.Null "Represents a DBNull that can be assigned to this instance of the SqlInt32 class."

int? example = null;
object exampleCast = (object) example ?? DBNull.Value;
object exampleNoCast = example ?? SqlInt32.Null;

You need pass DBNull.Value as a null parameter within SQLCommand, unless a default value is specified within stored procedure (if you are using stored procedure). The best approach is to assign DBNull.Value for any missing parameter before query execution, and following foreach will do the job.

foreach (SqlParameter parameter in sqlCmd.Parameters)
{
    if (parameter.Value == null)
    {
        parameter.Value = DBNull.Value;
    }
}

Otherwise change this line:

planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex;

As follows:

if (AgeItem.AgeIndex== null)
    planIndexParameter.Value = DBNull.Value;
else
    planIndexParameter.Value = AgeItem.AgeIndex;

Because you can't use different type of values in conditional statement, as DBNull and int are different from each other.

With one line of code, try this:

var piParameter = new SqlParameter("@AgeIndex", AgeItem.AgeIndex ?? (object)DBNull.Value);

If you use the conditional(ternary) operator the compiler needs an implicit conversion between both types, otherwise you get an exception.

So you could fix it by casting one of both to System.Object:

planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : (object) AgeItem.AgeIndex;

But since the result is not really pretty and you always have to remember this casting, you could use such an extension method instead:

public static object GetDBNullOrValue<T>(this T val)
{
    bool isDbNull = true;
    Type t = typeof(T);

    if (Nullable.GetUnderlyingType(t) != null)
        isDbNull = EqualityComparer<T>.Default.Equals(default(T), val);
    else if (t.IsValueType)
        isDbNull = false;
    else
        isDbNull = val == null;

    return isDbNull ? DBNull.Value : (object) val;
}

Then you can use this concise code:

planIndexParameter.Value = AgeItem.AgeIndex.GetDBNullOrValue();

Try this:

SqlParameter[] parameters = new SqlParameter[1];    
SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int);

planIndexParameter.IsNullable = true; // Add this line

planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex== ;
parameters[0] = planIndexParameter;

In my opinion the better way is to do this with the Parameters property of the SqlCommand class:

public static void AddCommandParameter(SqlCommand myCommand)
{
    myCommand.Parameters.AddWithValue(
        "@AgeIndex",
        (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex);
}

you can do something like this. Here startDate and endDate are nullable datetime param

var Statistics= db.Database.SqlQuery<ViewStatistics>("YourStoreProcedure_Or_sqlQuery  @startDate,@endDate",
        new SqlParameter("startDate", startDate?? (object)DBNull.Value),
        new SqlParameter("endDate", endDate?? (object)DBNull.Value)
        ).ToList();

Try this:

if (AgeItem.AgeIndex != null)
{
   SqlParameter[] parameters = new SqlParameter[1];
   SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int);
   planIndexParameter.Value = AgeItem.AgeIndex;
   parameters[0] = planIndexParameter;
}

In other words, if the parameter is null just don't send it to your stored proc (assuming, of course, that the stored proc accepts null parameters which is implicit in your question).

Consider using the Nullable(T) structure available. It'll let you only set values if you have them, and your SQL Command objects will recognize the nullable value and process accordingly with no hassle on your end.

if (_id_categoria_padre > 0)
{
    objComando.Parameters.Add("id_categoria_padre", SqlDbType.Int).Value = _id_categoria_padre;
}
else
{
    objComando.Parameters.Add("id_categoria_padre", DBNull.Value).Value = DBNull.Value;
}

I use a simple method with a null check.

public SqlParameter GetNullableParameter(string parameterName, object value)
{
  if (value != null)
  {
    return new SqlParameter(parameterName, value);
  }
  else
  {
    return new SqlParameter(parameterName, DBNull.Value);
  }
}

A simple extension method for this would be:

public static void AddParameter(
  this SqlCommand sqlCommand,
  string parameterName,
  SqlDbType sqlDbType,
  object item
)
{
  sqlCommand.Parameters
    .Add(parameterName, sqlDbType)
    .Value = item ?? DBNull.Value;
}

My code, working in real project Look the ternary operator beafore make the sqlparameter this is the best way for me, withou problems:

public bool Key_AddExisting(
  string clave,
  int? idHito_FileServer,
  int? idTipoDocumental_Almacen,
  string tipoExp_CHJ,
  int idTipoExp_Verti2,
  int idMov_Verti2
)
{
  List<SqlParameter> pars = new List<SqlParameter>()
  {
    new SqlParameter { ParameterName = "@Clave", Value = clave },
    idHito_FileServer == null // <-- Look
      ? new SqlParameter { ParameterName = "@IdHito_FileServer", Value = DBNull.Value }
      : new SqlParameter { ParameterName = "@IdHito_FileServer", Value = idHito_FileServer },
    idTipoDocumental_Almacen == null // <-- Look
      ? new SqlParameter { ParameterName = "@IdTipoDocumental_Almacen", Value = DBNull.Value }
      : new SqlParameter { ParameterName = "@IdTipoDocumental_Almacen", Value = idTipoDocumental_Almacen },
    new SqlParameter { ParameterName = "@TipoExp_CHJ", Value = tipoExp_CHJ },
    new SqlParameter { ParameterName = "@IdTipoExp_Verti2", Value = idTipoExp_Verti2 },
    new SqlParameter { ParameterName = "@IdMov_Verti2", Value = idMov_Verti2 }
  };

  string sql = "INSERT INTO [dbo].[Enlaces_ClavesCHJ_MovimientosVerti2] " +
    "( " +
    "  [Clave] " +
    ", [IdHito_FileServer] " +
    ", [IdTipoDocumental_Almacen] " +
    ", [TipoExp_CHJ] " +
    ", [IdTipoExp_Verti2] " +
    ", [IdMov_Verti2] " +
    ") " +
    "VALUES" +
    "( " +
    "  @Clave" +
    ", @IdHito_FileServer" +
    ", @IdTipoDocumental_Almacen" +
    ", @TipoExp_CHJ" +
    ", @IdTipoExp_Verti2" +
    ", @IdMov_Verti2" +
    ")";

  return DbBasic.ExecNonQuery(ref this.conn, sql, pars);
}

try something like this:

if (_id_categoria_padre > 0)
{
    objComando.Parameters.Add("id_categoria_padre", SqlDbType.Int).Value = _id_categoria_padre;
}
else
{
    objComando.Parameters.Add("id_categoria_padre", DBNull.Value).Value = DBNull.Value;
}

int? nullableValue = null;
object nullableValueDB
{
   get{
       if(nullableValue==null)
          return DBNull.Value;
       else
          return (int)nullableValue;
   }
}

I'm solving like that.

if (AgeItem.AgeIndex== null)  
    cmd.Parameters.Add(new SqlParameter("ParaMeterName", SqlDbType.DateTime).Value = DBNull);  
else  
    cmd.Parameters.Add(new SqlParameter("ParaMeterName", SqlDbType.DateTime).Value = AgeItem.AgeIndex);

sqlCom.Parameters
  .Add(new SqlParameter("@qavCode", SqlDbType.Char, 11))
  .Value = (object)(string.IsNullOrEmpty(rf.Request.QavCode)
    ? null
    : rf.Request.QavCode) ?? DBNull.Value;

To improve the usage of the null-coalescing operator ?? to manage empty strings in my example I mixed the regular ternary ?: to the null-coalescing operator ??.

Hope my suggestion is useful.

This is what I simply do...

var PhoneParam = new SqlParameter("@Phone", DBNull.Value);
if (user.User_Info_Phone != null) {
  PhoneParam.SqlValue = user.User_Info_Phone;
}

return this.Database.SqlQuery<CustLogonDM>(
  "UpdateUserInfo @UserName, @NameLast, @NameMiddle, @NameFirst, @Address, @City, @State, @PostalCode, @Phone",
  UserNameParam, NameLastParam, NameMiddleParam, NameFirstParam, AddressParam, CityParam, StateParam, PostalParam, PhoneParam
).Single();

dynamic psd = DBNull.Value;

if (schedule.pushScheduleDate > DateTime.MinValue)
{
  psd = schedule.pushScheduleDate;
}

sql.DBController.RunGeneralStoredProcedureNonQuery(
  "SchedulePush",
  new string[] { "@PushScheduleDate" },
  new object[] { psd }, 10, "PushCenter"
);