How to insert null value in Database through parameterized query

Question

I have a datetime datatype : dttm

Also the database field type is datatime

Now I am doing this:

if (dttm.HasValue)
{
    cmd.Parameters.AddWithValue("@dtb", dttm);
}
else
{
    // It should insert null value into database
    // through cmd.Parameters.AddWithValue("@dtb", _____)
}

How can this be done.

From msdn.microsoft.com/en-us/library/… : The value to be added. Use DBNull.Value instead of null, to indicate a null value. — a1ex07
– a1ex07, Commented May 23, 2013 at 14:54

Kevin Kunderman · Accepted Answer · 2013-05-23 15:02:59Z

47

This can be done using the null-coalescing operator: if the value of dttm is null the DBNull.Value will be inserted otherwise the value of dttm will be used

cmd.Parameters.AddWithValue("@dtb", dttm ?? (object) DBNull.Value);

This will eliminate the need for the if statment

edited May 23, 2013 at 15:02

answered May 23, 2013 at 14:55

Kevin Kunderman

2,1341 gold badge19 silver badges30 bronze badges

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

1 Comment

Jon Skeet Over a year ago

Yup, this is the cleanest approach. I wasn't entirely sure of the viability of casting in the second operand of the null coalescing operator, but it works fine.

Carlos Landeras · Accepted Answer · 2013-05-23 14:54:29Z

22

Use DBNull.Value

if (dttm.HasValue)
{
    cmd.Parameters.AddWithValue("@dtb", dttm);
}
else
{
    cmd.Parameters.AddWithValue("@dtb", DBNull.Value)
}

answered May 23, 2013 at 14:54

Carlos Landeras

11.1k11 gold badges61 silver badges84 bronze badges

Comments

Furkan Ekinci · Accepted Answer · 2013-05-23 14:54:10Z

4

if your field allows null value;

if (dttm.HasValue)
{
    cmd.Parameters.AddWithValue("@dtb", dttm);
}
else
{
    cmd.Parameters.AddWithValue("@dtb", DBNull.Value)
}

answered May 23, 2013 at 14:54

Furkan Ekinci

2,7523 gold badges32 silver badges43 bronze badges

Collectives™ on Stack Overflow

How to insert null value in Database through parameterized query

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related