Difference between DbNull.Value and DbNull.Value.ToString()

Question

I wanted to learn which usage is true?

if(!string.IsNullOrEmpty(parentID))
   cmd.Parameters.Add(new SqlParameter("@ParentSesID", parentID));
else
   cmd.Parameters.Add(new SqlParameter("@ParentSesID", DBNull.Value));

OR

if(!string.IsNullOrEmpty(parentID))
   cmd.Parameters.Add(new SqlParameter("@ParentSesID", parentID));
else
   cmd.Parameters.Add(new SqlParameter("@ParentSesID", DBNull.Value.ToString()));

Sriram Sakthivel · Accepted Answer · 2013-11-20 09:38:51Z

6

cmd.Parameters.Add(new SqlParameter("@ParentSesID", parentID));

This is passing a parentID to parameter @ParentSesID.

cmd.Parameters.Add(new SqlParameter("@ParentSesID", DBNull.Value));

is passing a null value to parameter.

cmd.Parameters.Add(new SqlParameter("@ParentSesID", DBNull.Value.ToString()));

is passing equal to string.Empty, which is not allowed in numerical data types.

cmd.Parameters.Add(new SqlParameter("@ParentSesID", null);

is same as ignoring the parameter.

So when you need to pass null to SP you've to pass DBNull.Value.

answered Nov 20, 2013 at 9:38

Sriram Sakthivel

73.8k7 gold badges118 silver badges200 bronze badges

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

10 Comments

Sudhakar Tillapudi Over a year ago

so DBNull.Value and null is not same?

Sriram Sakthivel Over a year ago

@Sudhakar Nope, when you say null you're ignoring the parameter and DBNull says you're passing a null value to DB

Sudhakar Tillapudi Over a year ago

so we can not pass null when table column is defined as not null right?

Sriram Sakthivel Over a year ago

@Sudhakar When table is defined as not null you can't pass either null or DbNull both will fail

Sudhakar Tillapudi Over a year ago

Okay, then when we will be using null instead of DBNull.Value? i think we should never use null then right?

|

Tigran · Accepted Answer · 2013-11-20 09:33:05Z

2

This one: cmd.Parameters.Add(new SqlParameter("@ParentSesID", DBNull.Value)); is a correct, in case the field you are writing to can accept NULL value in database.

This one: cmd.Parameters.Add(new SqlParameter("@ParentSesID", DBNull.Value.ToString())); if the field is a, say, VARCHAR, and does not accept NULL, but you want to indicate in some way an absence of value in that field, so you write inside your application specific "no value" value. So your application knows, if it founds that value in the field, that means: no value.

answered Nov 20, 2013 at 9:33

Tigran

62.3k8 gold badges90 silver badges124 bronze badges

Comments

Kamil Budziewski · Accepted Answer · 2013-11-20 09:33:58Z

1

DBNull.Value.ToString() will return empty string, so I think

cmd.Parameters.Add(new SqlParameter("@ParentSesID", DBNull.Value));

is a good approach and here the only approach

see here:

http://ideone.com/iGo1Jh

answered Nov 20, 2013 at 9:33

Kamil Budziewski

23.1k14 gold badges88 silver badges107 bronze badges

Collectives™ on Stack Overflow

Difference between DbNull.Value and DbNull.Value.ToString()

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related