.Net inserting NULL values into SQL Server database from variable values

Question

Hi. There have been similar questions but the answers weren't what I was looking for. I want to insert the a value of NULL into the SQL Server database if the reference is NULL or a value has not yet been assigned. At the moment I am testing for null and it looks like

String testString = null;

if (testString == null)
{
    command.Parameters.AddParameter(new SqlParameter("@column", DBNull.Value);
}
else
{
    command.Parameters.AddParameter(new SqlParameter("@column", testString);
}

This looks and feels incredibly clumsy to me. I have quite a few values that I am inserting into a database and to test them all like the above is very verbose. Does .Net not handle this in some way. I thought maybe if I used string as opposed to String but that also does not appear to work. Looking around I found articles which talk about using Nullable types.

System.Nullable<T> variable

This seems to work for primitives, int?, char? double? and bool?. So that might work for those but what about strings? Am I missing something here. What types should I be using for primitive values and for string values so that I do not have to repeatedly test values before inserting them.

EDIT: Before I get too many answers about ternary operators. I like them but not in this context. It doesn't make sense for me to need to test that value and have all that extra logic, when that sort of thing could have been inplemented lower down in the .Net framework and if I knew what types to give then it would get it for free.

Edit: Okay, so guys help me formulate my plan of attack. I will use the Nullable for my primitives (int?, double? etc) and for my Strings I will use the String but the ?? test. This keeps things less verbose. Is there anything that I am missing here, like maybe losing some semantics?

Answer 1

Maybe the ternary operator is something you find usefull.

Answer 2

What I sometimes do, is this:

command.Parameters.Add ("@column", SqlDbType.VarChar).Value = DBNull.Value;

if( String.IsNullOrEmpty (theString) == false )
{
    command.Parameters["@column"].Value = theString;
}

Answer 3

Nullable works great for primitives. I'm going to have to test the behavior of string but one option to at least clean up your code would be to define an extension method of string.

You can use the ?? operator for strings:

command.Parameters.AddParameter(new SqlParameter("@column", myNull ?? (object)DBNull.Value);

?? returns the first item if it is not null, other wise it returns the second item.

Edit

Fixed the code above so it will compile you need to cast DBNull.Value to an object.

Answer 4

I agree that it is clumsy and a bit unfortunate that SqlParameter.Value must be set to DbNull.Value. But there is no way around it so you have to live with testing for null.

Answer 5

Nullable<T> cannot be applied to String since it is a reference type and not a value type.

Answer 6

Even better than the ternary is the double-question-mark (??) operator. Takes the first non-null value. So:

string x = null;
command.Parameters.AddParameter(
     new SqlParameter("@column", (object)x ?? DBNull.Value);

would give you a parm with a value of DBNull.Value, but

string x = "A String";
command.Parameters.AddParameter(
     new SqlParameter("@column", (object)x ?? DBNull.Value);

Would give you a parm with "A String" as the value.