Assign null to a SqlParameter

Question

The following code gives an error - "No implicit conversion from DBnull to int."

SqlParameter[] parameters = new SqlParameter[1];    
SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int);
planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex;
parameters[0] = planIndexParameter;

Hamadryad

You need to cast AgeItem.AgeIndex to object I think... #202771 (btw, why the == at the end of the 3rd line?) – Stevens

Answer 1

The problem is that the ?: operator cannot determine the return type because you are either returning an int value or a DBNull type value, which are not compatible.

You can of course cast the instance of AgeIndex to be type object which would satisfy the ?: requirement.

You can use the ?? null-coalescing operator as follows

SqlParameter[] parameters = new SqlParameter[1];     
SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int);
planIndexParameter.Value = (object)AgeItem.AgeIndex ?? DBNull.Value;
parameters[0] = planIndexParameter; 

Here is a quote from the MSDN documentation for the ?: operator that explains the problem

Either the type of first_expression and second_expression must be the same, or an implicit conversion must exist from one type to the other.

Only

Why is there no exception thrown when trying to cast null to object? I would think it should be AgeItem.AgeIndex as object – Polaris

@Niels Brinch, there would not be an exception because null is an object and as long as you do not try dereference it, it is perfectly legal. However, in this example it is not null being cast to object, it is DBNull.Value which is actually a value type. The ?? operator says 'if AgetItem.AgeIndex is null then return DBNull.Value otherwise returen AgeItem.AgeIndex' then the response is cast to object. See null coalescing operator for more details. msdn.microsoft.com/en-us/library/ms173224.aspx – Only

Technically, your solution using the null-coalescing operator ?? is the same solution as if you were to use the regular ternary ?: - you still need to cast AgeItem.AgeIndex to an object: planIndexParameter.Value = AgeItem.AgeIndex.HasValue ? (object)AgeItem.AgeIndex : DBNull.Value;. – Elyseelysee

If you were to use the regular ternary ?: to do a type-specific comparison, then casting the entire expression won't work. You have to cast the non-dbnull parameter like so: someID == 0 ? DBNull.Value : (object)someID – Wellordered

That is true but if you need using null-able value as an entrance parameter of function that result consume SqlParameter and if it is null you've got error this way is not work and you should use just simple If-Else way. for example: sample.Text.Trim() != "" ? func(sample.Text) : DBNull.Value; will not work as ?: and ?? – Crosley

Answer 2

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;

Manning

The suggestion looked promising so I tried "System.Data.SqlTypes.SqlString.Null" but it doesn't work. It puts actual string of "Null" ('N', 'u', 'l', 'l') into the field instead leaving it blank with true (null). However, the old 2010 "accepted answer" that uses cast with (object) ?? DBNull.Value works correctly. (The ADO.NET provider I used was SQLite but I'm not sure if that makes a difference.) I suggest that others carefully test Brian's tip to make sure null behavior is working as expected. – Overstuff

@JasDev: I vaguely recall describing this trick in a comment to a high rep user (I think Marc Gravell) and being told it only works on Microsoft SQL Server. – Manning

@Overstuff the provider will be the difference this works in SQL Server as Brain point's out. – Blondy

This answer only replaces an explicit cast to object with an implicit.one. In the sample code, exampleNoCast is declared object, so the cast to object still occurs. If, like in the OP's code, the value is assigned directly to SqlParameter.Value which is also of type object, then you still get the cast. – Salientian

Even on SQL Server, this seems to fail when using DataTable/SqlBulkCopy. – Manning

Answer 3

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. Hope this will help.

Anaheim

This answer is really nice because it examples in every way possible. I like the first approach, I usually use EF but in this requirement could not do it and it saves me a lot of time. Thanks! – Linzy

Answer 4

With one line of code, try this:

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

Pontificals

You can also cast AgeItem.AgeIndex to an object: (object)AgeItem.AgeIndex. But yea also prefer your approach – Dreeda

Answer 5

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();

Gleeson

Answer 6

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;

Captive

Answer 7

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);
}

Billmyre

But if the value is DBNull.Value, ADO.NET might have somewhat of a hard time guessing what SqlDbType that might be........ this is convenient - but a bit dangerous.... – Alerion

Answer 8

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

Marketable

But now, you're just omitting a parameter - I highly doubt the stored procedure will be happy about this.... most likely, the call will fail stating "no value for parameter @AgeIndex supplied which was expected"..... – Alerion

Wow. Harsh. Just write the stored proc to default to a value if the parameter is not passed (@AgeIndex int = 0). Happens all the time. The client can either accept the default, or override it by passing the parameter. Why the downvote? – Marketable

Answer 9

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;
}

Apure

Answer 10

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.

Unplug

Answer 11

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;
    }

Holsworth

Answer 12

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);
        }
    }

Mythical

Is that conditional logic backwards? Should DBNull.Value be in the first one? – Milzie

Surely is. Fixed. Thanks. – Mythical

Answer 13

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 }
    LOOK -> , idHito_FileServer == null ? new SqlParameter { ParameterName = "@IdHito_FileServer", Value = DBNull.Value } : new SqlParameter { ParameterName = "@IdHito_FileServer", Value = idHito_FileServer }
    LOOK -> , idTipoDocumental_Almacen == null ? 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);
    }

Treenatreenail

Answer 14

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();

Thermion

Answer 15

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;
}

Layout

Answer 16

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

I'm solving like that.

Milliken

Answer 17

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);

Apure

Answer 18

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();

Lophobranch

Answer 19

            dynamic psd = DBNull.Value;

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


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

Arezzini

Answer 20

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.

Ruelle