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

Question

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?

Walden Leverich · Accepted Answer · 2009-03-06 15:53:13Z

11

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.

edited Mar 6, 2009 at 15:53

answered Mar 6, 2009 at 15:40

Walden Leverich

4,5562 gold badges24 silver badges30 bronze badges

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

3 Comments

LukeH Over a year ago

The compiler will choke on this because it can't implicitly convert from string to DBNull.

Walden Leverich Over a year ago

You are correct. You need to downcast the string (x) to an object. Edited example.

Joel Coehoorn Over a year ago

Not a fan of this, because it makes it easy for .Net to guess the wrong type for a parameter. It would be better to specify the parameter type via an overload that asks for an SqlDbType enum.

JoshBerke · Accepted Answer · 2009-03-06 15:52:38Z

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.

edited Mar 6, 2009 at 15:52

answered Mar 6, 2009 at 15:35

JoshBerke

67.3k25 gold badges131 silver badges170 bronze badges

3 Comments

LukeH Over a year ago

The compiler will choke on this because it can't implicitly convert from string to DBNull.

Jakob Christensen Over a year ago

I believe you can use testString ?? (string) DBNull.Value.

recursive Over a year ago

How about ((object) testString) ?? DBNull.Vale

Oxymoron · Accepted Answer · 2009-03-06 15:29:09Z

0

Maybe the ternary operator is something you find usefull.

answered Mar 6, 2009 at 15:29

Oxymoron

1,4024 gold badges21 silver badges40 bronze badges

Comments

Frederik Gheysels · Accepted Answer · 2009-03-06 15:31:38Z

0

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;
}

answered Mar 6, 2009 at 15:31

Frederik Gheysels

57.2k12 gold badges103 silver badges156 bronze badges

Comments

Jakob Christensen · Accepted Answer · 2009-03-06 15:37:52Z

0

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.

answered Mar 6, 2009 at 15:37

Jakob Christensen

15k2 gold badges53 silver badges82 bronze badges

Comments

Gavin Miller · Accepted Answer · 2009-03-06 15:40:08Z

0

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

answered Mar 6, 2009 at 15:40

Gavin Miller

44k22 gold badges130 silver badges191 bronze badges

2 Comments

uriDium Over a year ago

Hi. Just looked it up, it is disappointing that string as opposed to String is still a reference type. Apparently it is an alias but then for instance string a = "hello"; string b = "h"; b += "ello"; then a==b returns true because it compares their values. Interesting.

Gavin Miller Over a year ago

Yes & No - There's good reason why string is a reference type and not a value type (look at some of Jon Skeet's stuff he's a pro in that area); For such cases as you've specified that's why they created the StringBuilder class.

Jesse · Accepted Answer · 2013-03-04 05:21:52Z

0

Example:

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

This will insert null.

edited Mar 4, 2013 at 5:21

Jesse

8,7597 gold badges49 silver badges57 bronze badges

answered Mar 4, 2013 at 5:00

Chintan Udeshi

111 bronze badge

1 Comment

Jesse Over a year ago

Hi, welcome to Stack Overflow! This is an incomplete snippet of code.

Collectives™ on Stack Overflow

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

7 Answers 7

3 Comments

Edit

3 Comments

Comments

Comments

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Edit

3 Comments

Comments

Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related