Best method of assigning NULL value to SqlParamete

Question

I have a number of optional input parameters I am using in a C# class method. Since the optional syntax creates a value of '0' when the parameter is not used, the SQL insert command I call in the method winds up inserting as such. However, I need the command to insert a NULL value instead of a 0 when the parameter is not being used. What is the best way to accomplish this without using a large amount of 'if' statements?

Below is the code I am referring to. Is there syntax/a command of some kind that will allow me to specify a NULL value in the SqlParameter declaration?

public int batchInsert
(
    int id, 
    int outcome, 
    int input = 0, 
    int add = 0, 
    int update = 0,
    int delete = 0,
    int errors = 0, 
    int warnings = 0
)
{
    string sts;
    if (outcome == 0)
    {
        sts = "S";
    }
    else if (outcome == 1)
    {
        sts = "W";
    }
    else
    {
        sts = "E";
    }

    SqlConnection sqlConn = new SqlConnection(this.connString);
    SqlParameter runId = new SqlParameter("@runId", id);
    SqlParameter endTime = new SqlParameter("@endTime", DateTime.Now);
    SqlParameter status = new SqlParameter("@status", sts);
    SqlParameter sqlInput = new SqlParameter("@itemsRead", input);
    SqlParameter sqlAdd = new SqlParameter("@add", add);
    SqlParameter sqlUpdate = new SqlParameter("@update", update);
    SqlParameter sqlDelete = new SqlParameter("@delete", delete);
    SqlParameter sqlError = new SqlParameter("@errors", errors);
    SqlParameter sqlWarning = new SqlParameter("@warnings", warnings);
    SqlParameter result = new SqlParameter("@outcome", results[outcome]);
    SqlCommand sqlComm = new SqlCommand(insertCommand(), sqlConn);

Answer 1

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.

Answer 2

Yes, for the value of the parameter, just use DBNull.Value. For example:

SqlParameter sqlError = 
    new SqlParameter("@errors", errors == 0 ? (object)DBNull.Value : errors);

Or write a little helper:

private object ValueOrDBNullIfZero(int val) {
   if ( val == 0 ) return DBNull.Value;
   return val;
}

Then:

SqlParameter sqlError = 
    new SqlParameter("@errors", ValueOrDBNullIfZero(errors));

Answer 3

You'd need to check each parameter value and use DBNull.Value send null. We have an extension method off object to make this a little easier.

public static class ObjectExtensions
{
    public static object OptionalParam<T>(this T value, T optionalValue = default(T))
    {
        return Equals(value,optionalValue) ? (object)DBNull.Value : value;
    }
}

Usage:

var sqlInput = new SqlParameter("@itemsRead", input.OptionalParam());

Or if you want to consider a non-default, arbitrary value as the default value:

var sqlInput = new SqlParameter("@itemsRead", input.OptionalParam(-1));

Answer 4

Another clear way to set a null datetime parameter when necessary

if(obj.myDate == DateTime.MinValue)
{
    aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = DBNull.Value;
}
else
{
    aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = obj.myDate ;
}

Answer 5

short syntax!

  cmd.Parameters.Add(new SqlParameter{SqlValue=username ?? (object)DBNull.Value,ParameterName="usuario" }  );

Answer 6

This is the easiest way to assign a Null value to sql parameter

            command.Parameters.AddWithValue("@Comment", string.IsNullOrEmpty(comment) ? (object)DBNull.Value : comment);