Default value SqlCommand.Parameters.Add C# Issue

Question

I have a program that connect to a database, use a stored procedure to insert a line in a table, and close the connection. Everything works ok instead of the line below.

VisualStudio underline in red the last line :

string value = textBox_value.Text;

command.Parameters.Add(new SqlParameter("@value", (String.IsNullOrEmpty(value)) ? DBNull.Value : value));

Error message :

There is no explicit conversion between 'System.DBNull' and 'string'

String.IsNullOrEmpty(value) returns true if the value is empty, else returns false.

I found the solution by writting this enourmous line of code :

if (String.IsNullOrWhiteSpace(value)) 
{
        command.Parameters.Add(new SqlParameter("@value", DBNull.Value));
}
else 
{
        command.Parameters.Add(new SqlParameter("@value", CT_Intitule));
}

But is that normal we can't perform a one-line condition ?

EDIT

Thanks to Rahul Singh, that pointed the MSDN documentation in which the function is described as expecting 2 same type, I resolved my issue doing this :

command.Parameters.Add
(
    ( String.IsNullOrWhiteSpace(value) ) ? 
        new SqlParameter("@value", CT_Intitule) 
        : 
        new SqlParameter("@value", DBNull.Value)
);

It sounds that's happening because of ?: operator since it's expressions have to convert to each other an implicit way at least.. — Soner Gönül
– Soner Gönül, Commented Mar 4, 2015 at 11:33
Yep, I think I am understanding what is happening. Actually, ? : condition expect the two parameters to be the same type. I didn't get this before. — Anwar
– Anwar, Commented Mar 4, 2015 at 11:35

Steve · Accepted Answer · 2015-03-04 11:47:33Z

1

You could fix you code casting the DBNull.Value to object

command.Parameters.Add(new SqlParameter("@value", 
                     string.IsNullOrEmpty(value) 
                     ? (object)DBNull.Value
                     : CT_Intitule);

But I wish to discourage the usage of the overload that pass directly the value.
It is always better and more error safe to always specify the parameter type

command.Parameters.Add(new SqlParameter("@value", SqlDbType.NVarChar)).Value =
                     string.IsNullOrEmpty(value) 
                     ? (object)DBNull.Value
                     : CT_Intitule;

You could read more about the effects of the C# code on database performances here

How Data Access Code Affects Database Performance

answered Mar 4, 2015 at 11:47

Steve

217k22 gold badges242 silver badges296 bronze badges

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

Comments

Rahul Singh · Accepted Answer · 2015-03-04 11:35:45Z

1

According to MSDN:-

Either the type of first_expression and second_expression must be the same, or an implicit conversion must exist from one type to the other.

Conditional Operator expects both types to be same, for example you cannot do:-

string test = 1 == 1 ? "True" : 5;

It will throw the same error because there is no implicit conversion possible between a string ("True") and int (5).

answered Mar 4, 2015 at 11:35

Rahul Singh

21.8k6 gold badges43 silver badges61 bronze badges

1 Comment

Anwar Over a year ago

Yes this is exact. I am updating my question with the solution I found, it was not that hard, thanks for pointing !

Collectives™ on Stack Overflow

Default value SqlCommand.Parameters.Add C# Issue

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related