0

I am trying to make the optional parameter of a C# Method to be converted to DBNull (if it is null) to be added with Parameter.AddWithValue method.

I saw many solutions (Set a database value to null with a SqlCommand + parameters), extension methods, etc, but i wanted a single line solution.

command.Parameters.AddWithValue("@Municipio", municipio ?? DBNull.Value)don't work (Operator '??' cannot be applied to operands of type 'string' and 'System.DBNull')

The method: public static void Foo(string bar, string municipio = null)

Unfortunately i can't do string municipio = DBNull.Value because "default parameter must be a compile-time constant" (what i don't exactly understand the meaning).

I was able to make it work as such:

object obj = municipio == null ? command.Parameters.AddWithValue("@Municipio", DBNull.Value) : command.Parameters.AddWithValue("@Municipio", municipio);

I am very uncomfortable with this, seems like a JavaScript way of doing, i want something like:

if municipio == null ? command.Parameters.AddWithValue("@Municipio", DBNull.Value) : command.Parameters.AddWithValue("@Municipio", municipio);

But somehow i am not being able to make it work like this, perhaps im missing some syntax (i tried many parenthesis/etc combinations).

So my question is, is it good practice the one-line solution i reached, or should i replace it?

Community
  • 1
  • 1
Dillinger
  • 341
  • 3
  • 16
  • 1
    Another option is `command.Parameters.AddWithValue("@Municipio", municipio ?? DBNull.Value)` – stuartd Jan 04 '17 at 15:23
  • I had tried it but `Operator '??' cannot be applied to operands of type 'string' and 'System.DBNull'` – Dillinger Jan 04 '17 at 15:24
  • 1
    Sorry. Should be `command.Parameters.AddWithValue("@Municipio", municipio ?? (object)DBNull.Value)` – stuartd Jan 04 '17 at 15:36
  • This way works, but take a look here (http://stackoverflow.com/questions/41473095/dbnull-cast-to-object-returns-different-value) so i can better understand the difference of your "identical" answers. – Dillinger Jan 04 '17 at 20:59

1 Answers1

0

Updated to handle if the variable is not initialized. Here is an example:

            OracleParameter P_COUNTYCODE = new OracleParameter("P_COUNTYCODE", OracleDbType.Varchar2);
            P_COUNTYCODE.Direction = ParameterDirection.Input;
            P_COUNTYCODE.Value = countyCode.Length > 0 ? countyCode : (object)DBNull.Value;

try:

command.Parameters.AddWithValue("@Municipio", !string.IsNullOrEmpty(municipio) > 0 ? municipio : (object)DBNull.Value)
gmdev86
  • 164
  • 5
  • This doesn't check if the value is null, which is what the question specifically asked for (quering the Length of a null value will throw a NullReferenceException) – stuartd Jan 04 '17 at 15:43
  • This way works, but i am waiting for an answer here (http://stackoverflow.com/questions/41473095/dbnull-cast-to-object-returns-different-value) to better understand the observation @stuartd did. – Dillinger Jan 04 '17 at 20:58
  • thank you stuartd! i have updated my answer to handle if the variable was not set. – gmdev86 Jan 04 '17 at 21:09