Set a database value to null with a SqlCommand + parameters

Question

I was previously taught today how to set parameters in a SQL query in .NET in this answer (click).

Using parameters with values are fine, but when I try to set a field in the database to null I'm unsuccessful. Either the method thinks I am not setting a valid parameter or not specifying a parameter.

e.g.

Dim dc As New SqlCommand("UPDATE Activities SET [Limit] = @Limit WHERE [Activity] = @Activity", cn)  If actLimit.ToLower() = "unlimited" Then     ' It's not nulling :(     dc.Parameters.Add(New SqlParameter("Limit", Nothing)) Else     dc.Parameters.Add(New SqlParameter("Limit", ProtectAgainstXSS(actLimit))) End If 

Is there something I'm missing? Am I doing it wrong?

Answer 1

you want DBNull.Value.

In my shared DAL code, I use a helper method that just does:

    foreach (IDataParameter param in cmd.Parameters)     {         if (param.Value == null) param.Value = DBNull.Value;     } 

Answer 2

I use a SqlParameterCollection extension method that allows me to add a parameter with a nullable value. It takes care of converting null to DBNull. (Sorry, I'm not fluent in VB.)

public static class ExtensionMethods {     public static SqlParameter AddWithNullable<T>(this SqlParameterCollection parms,             string parameterName, T? nullable) where T : struct     {         if (nullable.HasValue)             return parms.AddWithValue(parameterName, nullable.Value);         else             return parms.AddWithValue(parameterName, DBNull.Value);     } } 

Usage:

string? optionalName = "Bozo"; cmd.Parameters.AddWithNullable("@Name", optionalName);