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

Question

There were similar questions, but the answers were not what I was looking for. I want to insert a NULL value in a SQL Server database if the link is NULL or the value is not assigned yet. I'm currently testing null and looks like

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

It looks and seems incredibly awkward to me. I have quite a few values ​​that I insert into the database, and there are a lot of checking them all, as above. Does .Net do this? I thought maybe if I used a string as opposed to a String, but that also does not work. Looking around, I found articles that talk about using Nullable types.

 System.Nullable<T> variable 

It looks like primitives, int ?, char? double? and bool ?. So this might work for those but about strings? I missed something. What types should I use for primitive values ​​and for string values, so I do not need to check the values ​​many times before inserting them.

EDIT: Before getting too many answers about triple operators. I like them, but not in this context. It makes no sense for me to test this value and have all this additional logic when such a thing could be implemented below in the .Net structure, and if I knew what types to give, then it would receive it for free.

Edit: Okay, so the guys are helping me formulate my attack plan. I will use Nullable for my primitives (int ?, double? And so on), And for my strings I will use String, but ??? test. This makes things less verbose. Is there something that I am missing here, for example, that could lose semantics?

Answer 1

Even better than triple, is the double question mark operator (??). It takes the first nonzero value. So:

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

will provide you a parm with a DBNull.Value 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.

Answer 2

Nullable is great for primitives. I will need to check the behavior of the string, but one option, at least to clear your code, is to define a method for expanding the string.

You can use the operator for strings:

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

?? returns the first element if it is not null, otherwise it returns the second element.

Edit

The code above is fixed, so it will be compiled, you need to pass DBNull.Value to the object.

Answer 3

Perhaps the ternary operator is what you find useful.

Answer 4

What I sometimes do is:

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

Answer 5

I agree that this is inconvenient and a bit unfortunate, that SqlParameter.Value should be set to DbNull.Value. But there is no way around this, so you have to live with zero testing.

Answer 6

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

Answer 7

Example:

 if (txtDisplayName.Text != "") { insert into table (id,display_name) values ('" + txtID.Text + "','" + txtDisplayName.Text + "'); } else { insert into table (id) values ('" + txtID.Text + "'); } 

Insert null .