Best method of assigning NULL value to SqlParameter

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

Do you have cases in which zero is a legitimate value to write in the database table? — Steve, Dec 20 '12 at 22:08
If you use nullable types in C# you can use this syntax to substitute a database `NULL` for a C# `null`: `myVariable ?? (object)DBNull.Value`. — HABO, Dec 20 '12 at 22:12

score 40 · Accepted Answer · edited Oct 30 '15 at 13:55

40

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

edited Oct 30 '15 at 13:55

TrueWill

25,132
10
101
150

answered Dec 20 '12 at 22:05

Eric Petroelje

59,820
9
127
177

For some reason I only saw the "Yes, for the value of the parameter, just use DbNull.Value", and not the example. Thx! – NealR Dec 20 '12 at 22:13
@georgem - Yup, I'm thinking that should work as well, although most of the time ADO.NET is smart enough to convert a `null` value to `DbNull.Value` on its own, so I'm not sure that would be necessary. – Eric Petroelje Oct 20 '14 at 18:04
thanks, work perfect for my needs. don't forget to allow null value on table column. – Benny Margalit May 26 '16 at 07:45
Thank you. Precisely what I needed for a piece of code I'm working on. – Gustav Swanepoel Jan 30 '22 at 13:29

moribvndvs · Answer 2 · 2012-12-20T22:26:37.200

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

score 3 · Answer 3 · answered May 27 '16 at 06:25

3

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

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

answered May 27 '16 at 06:25

Parsa

1,299
11
17

Another option is to use `Convert.DBNull` instead of `(object)DBNull.Value`. Example (with a nullable int): `command.Parameters.AddWithValue("@optionalParam", id.HasValue ? id.Value : Convert.DBNull);` – digiogo Jun 21 '16 at 19:12

score 2 · Answer 4 · answered Sep 19 '13 at 11:55

with small helper class we can create extenction method to add DBNull.Value to sqlparameter

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;

namespace System.Data.SqlClient
{
    public static class ExtensionMethods 

    {



        public static SqlParameter AddParameter(this SqlParameterCollection parms, SqlParameter param)
        {
            if (param.Value == null)
            {
                param.Value = DBNull.Value;
                return parms.Add(param);
            }
            else
            {
                return parms.Add(param);        
            }

        }
}

usage

SqlParameter _FraudPackageID = new SqlParameter("@FraudPackageID", System.Data.SqlDbType.BigInt);
 _FraudPackageID.Value = FraudPackageID;
 _FraudPackageID.Direction = System.Data.ParameterDirection.Input;

_cmd.Parameters.AddParameter(_FraudPackageID);

if you assign or pass null as value to sqlparameter this extenction method will automatically converts it to DBNull and reassign to the sqlparameter.

so no need to worry about what the values we are passing dynamically.

score 1 · Answer 5 · answered Dec 20 '12 at 22:12

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.

answered Dec 20 '12 at 22:12

Dillie-O

29,277
14
101
140

score 0 · Answer 6 · answered Jul 31 '15 at 09:13

0

short syntax!

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

answered Jul 31 '15 at 09:13

GoAntonio

183
2
13

score 0 · Answer 7 · answered Oct 06 '17 at 15:35

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

Best method of assigning NULL value to SqlParameter

7 Answers7

Linked