Is there a more elegant form for assigning NULL to InsertCommand's NVarChar?

Question

This code works for me very well:

if (someStr == null)
  da.InsertCommand.Parameters.Add("@SOMESTR", SqlDbType.NVarChar).Value = DBNull.Value;
else
  da.InsertCommand.Parameters.Add("@SOMESTR", SqlDbType.NVarChar).Value = someStr;

But my intuition tells me that there may be a one-liner version of it. Something like:

  da.InsertCommand.Parameters.Add("@SOMESTR", SqlDbType.NVarChar).Value = someStr==null ? DBNull.Value : someStr ;

But the one-liner I just posted above fails of course because DBNull.Value doesn't cast to String.

Is there a way to accomplish the one liner I so desire?

@cadrell0 Your approach works! Please post as answer so that I can accept. — scatmoi, Oct 19 '12 at 19:23
We've done this in the past with casting: (object)DBNull.Value should do it for you. (or similarly close syntax) — Adam Wenger, Oct 19 '12 at 19:23
possible duplicate of [How do I Parameterize a null string with DBNull.Value clearly and quickly](http://stackoverflow.com/questions/2602949/how-do-i-parameterize-a-null-string-with-dbnull-value-clearly-and-quickly) — Kiquenet, Nov 28 '13 at 11:11

score 6 · Accepted Answer · answered Oct 19 '12 at 19:27

You could cast someStr to an object

For example:

da.InsertCommand.Parameters.Add("@SOMESTR", SqlDbType.NVarChar).Value = someStr==null ? DBNull.Value : (object)someStr;

Or you could do as Oded and Servy suggested and use an extension method. While it may add a few lines of code it will save you from duplicate code.

As Servy pointed out, putting it on object could lead to clutter. For this reason I would put it on SqlParameter

public static void SetValue(this SqlParameter parameter, object value)
{
    parameter.Value = value == null ? DBNull.Value : value;
}

Then use it like so

da.InsertCommand.Parameters.Add("@SOMESTR", SqlDbType.NVarChar).SetValue(someStr);

score 4 · Answer 2 · answered Oct 19 '12 at 19:21

4

Create a function or extension method that will do the test and make the assignments as needed. Pass in someStr and the parameter name.

You will then be able to set the parameter in one line.

answered Oct 19 '12 at 19:21

Oded

489,969
99
883
1,009

That's elegant but defeats the purpose of reducing the number of lines of code. – scatmoi Oct 19 '12 at 19:24
1

@scatmoi - Why is that goal so desirable? And if you factor out such a function properly, it can be reused for many parameters, reducing the overall LOC. – Oded Oct 19 '12 at 19:25
@scatmoi - Well, the first objection for MS to include it is that it is easy enough to implement by yourself. They can't foresee _every_ possible requirement someone _may_ have - can you imagine what the size of the framework libraries would be if they would **not** stop _somewhere_? – Oded Oct 19 '12 at 20:42

score 4 · Answer 3 · answered Oct 19 '12 at 19:23

4

You can use the following extension method (or make it a non-extension method if you prefer)

public static object ConvertNull(this object obj)
{
    return obj ?? DBNull.Value;
}

You can then do:

da.InsertCommand.Parameters.Add("@SOMESTR", SqlDbType.NVarChar).Value = someStr.ConvertNull()

answered Oct 19 '12 at 19:23

Servy

202,030
26
332
449

That's beautiful. But moves the extra lines of code from one place to another. I wish ConvertNull() were a C# library function. – scatmoi Oct 19 '12 at 19:25
1

@scatmoi If you only ever do this once, it increases the lines of code. If you do it more than once it decreases it. If you happen to only ever do this once then just don't make the method (but I doubt that's the case). – Servy Oct 19 '12 at 19:26
+1 It makes 1-2 extra lines in an extension method class, which you can take advantage of over and over. I like this better than my previous implementations. – Adam Wenger Oct 19 '12 at 19:26
1

Note that you'll want this in an extensions class that you only include in contexts where you deal with DB commands a lot; adding extra "clutter" to the intellisense for `object` by putting this in a general purpose extensions would likely do more harm than good.. – Servy Oct 19 '12 at 19:28
1

@Servy et al you are of course right, but then it raises the question: How come Microsoft didn't provide this as a library method in any of its VS versions? – scatmoi Oct 19 '12 at 19:46
3

@scatmoi Well, for starters, it's so easy for you to make there's no reason you *need* them to do it for you. Next, as with any other feature, it doesn't exist because it wasn't: 1) thought of 2) selected for development 2) coded 3) tested 4) deployed. My guess is that this one stopped before #1, and if not there, then #2. Language features do no "exists by default unless there is a reason to exclude them". Features are, by default, not implemented, until all of the required steps are completed to implement them. – Servy Oct 19 '12 at 19:49
@Servy +1 for a great answer. – scatmoi Oct 19 '12 at 20:05

score 2 · Answer 4 · answered Oct 19 '12 at 19:34

2

da.InsertCommand.Parameters.Add("@SOMESTR", SqlDbType.NVarChar).Value = ((object)someStr) ?? DBNull.Value;

answered Oct 19 '12 at 19:34

D'Arcy Rittich

167,292
40
290
283

score 0 · Answer 5 · answered Oct 19 '12 at 19:36

0

Write a stored procedure for inserting, set default value for your argument as null, Use that stored procedure for your insert command, then don't just pass that argument to that sproc..

answered Oct 19 '12 at 19:36

techBeginner

3,792
11
43
59

Is there a more elegant form for assigning NULL to InsertCommand's NVarChar?

5 Answers5

Linked