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

c# dbnull sqlparameter
cihadakt picture cihadakt · Nov 20, 2013 · Viewed 12k times · Source

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

Answer

Sriram Sakthivel picture Sriram Sakthivel · Nov 20, 2013 
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.

Related questions

Assign null to a SqlParameter

The following code gives an error - "No implicit conversion from DBnull to int." SqlParameter[] parameters = new SqlParameter[1]; SqlParameter planIndexParameter = new SqlParameter("@AgeIndex", SqlDbType.Int); planIndexParameter.Value = (AgeItem.AgeIndex== null) ? DBNull.Value : AgeItem.AgeIndex; parameters[0] = planIndexParameter;

Read More
How do I pass a null value into a parameter for a SqlCommand

Before anyone comments that this has been answered before in other question I KNOW that..but in spite of the answers I have reviewed at Assign null to a SqlParameter Sending null parameters to Sql Server Best method of assigning …

Read More
DBNull if statement

I'm trying to execute a stored procedure and then use an if statement to check for null values and I'm coming up short. I'm a VB guy so please bear with me if I'm making a schoolboy syntax error. objConn = …

Read More