Menu
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?
424
You can insert NULL value into an int column with a condition i.e. the column must not have NOT NULL constraints. The syntax is as follows. INSERT INTO yourTableName(yourColumnName) values(NULL);
The rule for assigning NULL values to variables or table columns is simple: Use keyword "NULL" directly as normal values. Specificly, "NULL" can be used in SET statements to assign NULL values to variables. "NULL" can be used in SET clauses in UPDATE statements.
NULL can be inserted in any column by using the assignment operator "=". Also, to test whether a NULL is present or not we have to use certain operators, for example, IS NULL and IS NOT NULL. NULL value in SQL signifies that the column's corresponding value is either undefined or unknown.
Code Inspection: Insert NULL into NOT NULL columnCREATE TABLE br2 ( id INT NOT NULL, col1 NVARCHAR (20) NOT NULL, col2 NVARCHAR (20) NOT NULL, ); -- INSERT INTO br2 (id, col1, col2) VALUES (1, NULL, NULL);
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.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With