Inserting NULL to SQL DB from C# DbCommand

Question

        DbParameter param = comm.CreateParameter();
        param = comm.CreateParameter();
        param.ParameterName = "@StaffId";
        if (!string.IsNullOrEmpty(activity.StaffId))
            param.Value = activity.StaffId;
        param.DbType = DbType.String;
        comm.Parameters.Add(param);

The above does not work (obviously), object not instantiated. I am attempting to insert a NULL into the database when StaffId is NOT populated. How can I achieve this?

Perfect! If you post this as answer I'll mark it as correct. — SkonJeet
– SkonJeet, Commented Mar 21, 2012 at 9:27
As for why... stackoverflow.com/questions/4488727/what-is-the-point-of-dbnull/… — Marc Gravell
– Marc Gravell, Commented Mar 21, 2012 at 9:33

Andrey Gurinov · Accepted Answer · 2012-03-21 09:43:25Z

49

You can use DBNull.Value when you need to pass NULL as a parameter to the stored procedure.

param.Value = DBNull.Value;

Or you can use that instead of your if operator:

param.Value = !string.IsNullOrEmpty(activity.StaffId) ? activity.StaffId : (object)DBNull.Value;

edited Mar 21, 2012 at 9:43

answered Mar 21, 2012 at 9:29

Andrey Gurinov

2,9051 gold badge23 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SkonJeet Over a year ago

Thanks for the edit Andrey - appreciate it. It seems crazy to have to cast this as an object though (to me anyway).

KV Prajapati · Accepted Answer · 2012-03-21 09:24:59Z

8

Try DBNull.Value

if (!string.IsNullOrEmpty(activity.StaffId))
   param.Value = activity.StaffId;
else
  param.Value=DBNull.Value;

answered Mar 21, 2012 at 9:24

KV Prajapati

94.8k20 gold badges151 silver badges188 bronze badges

2 Comments

SkonJeet Over a year ago

is there a way of using the ? operator for a sort of shorthand if else statement here?? It seems to not like the String/DBNull.Value conversion!?

Andrey Gurinov Over a year ago

Try this: param.Value = !string.IsNullOrEmpty(activity.StaffId) ? activity.StaffId : (object)DBNull.Value;

Niko Yakimov · Accepted Answer · 2018-04-05 18:28:56Z

5

You can always use the null-coalescing operator (??)

param.Value = activity.StaffId ?? (object)DBNull.Value;

answered Apr 5, 2018 at 18:28

Niko Yakimov

831 silver badge4 bronze badges

Comments

Andomar · Accepted Answer · 2012-03-21 09:25:02Z

3

You could use DBNull.Value:

param.Value = DBNull.Value;

answered Mar 21, 2012 at 9:25

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

Inserting NULL to SQL DB from C# DbCommand

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Linked

Related