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

score 6 · Accepted Answer · answered Nov 20 '13 at 09:38

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 '13 at 09:38

Sriram Sakthivel

72,067
7
111
189

1

so DBNull.Value and null is not same? – Sudhakar Tillapudi Nov 20 '13 at 09:43
@Sudhakar Nope, when you say `null` you're ignoring the parameter and `DBNull` says you're passing a null value to DB – Sriram Sakthivel Nov 20 '13 at 09:43
so we can not pass null when table column is defined as not null right? – Sudhakar Tillapudi Nov 20 '13 at 09:47
@Sudhakar When table is defined as `not null` you can't pass either `null` or `DbNull` both will fail – Sriram Sakthivel Nov 20 '13 at 09:50
Okay, then when we will be using `null` instead of `DBNull.Value`? i think we should never use `null` then right? – Sudhakar Tillapudi Nov 20 '13 at 09:53
@Sudhakar That is database specific. – flindeberg Nov 20 '13 at 09:58
@Sudhakar `null` is certainly not required. I added it in my answer to avoid confusion and the diff b/w DBNull and null. – Sriram Sakthivel Nov 20 '13 at 09:58
@SriramSakthivel: Thanks for your explanation , your answer is very clear now. – Sudhakar Tillapudi Nov 20 '13 at 10:02
@SriramSakthivel Does sending a DbNull.Value.ToString() as parameter is shown in db column NULL or "" ? – cihadakt Nov 20 '13 at 11:26
@cihata87 It it equal to passing empty string "" – Sriram Sakthivel Nov 20 '13 at 11:27

score 2 · Answer 2 · answered Nov 20 '13 at 09:33

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.

score 1 · Answer 3 · answered Nov 20 '13 at 09:33

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 '13 at 09:33

Kamil Budziewski

22,699
14
85
105

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

3 Answers3

Linked

Related