DBNull cast to (object) returns different value

Question

I am trying to write DBNull.Value using Parameters.AddWithVallue when an optional parameter (a string) of a C# method is null.

public static void Abc(string distrito, string municipio = null)

command.Parameters.AddWithValue("@Municipio", municipio ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@Municipio", municipio.Length > 0 ? municipio : (object)DBNull.Value);

However, (object)DBNULL returns two different values in two different working ways. One writes empty/null and other NULL.

@Dillinger, Move images and code etc. into the questions. Don't bother us with links please. — Quality Catalyst, Jan 04 '17 at 21:00
Edited to include code, i just don't know how to use code on SO to make the image appear. Sorry. Someone edit for me. — Dillinger, Jan 04 '17 at 21:01
The image was broken somehow; SO wouldn't upload it. I had to open it in the browser and screensot it. Weird. Anyway it's the same as the code so it doesn't matter. — 15ee8f99-57ff-4f92-890c-b56153, Jan 04 '17 at 21:03
OP, DO NOT paste in a screenshot of code. You had the actual text of the code. That was the best thing to do. Leave the text of the code as it is, thanks! — 15ee8f99-57ff-4f92-890c-b56153, Jan 04 '17 at 21:05

recursive · Accepted Answer · 2017-01-04T21:07:00.547

3

I'm going to make a few assumptions, but I think I know what's going on. municipio is probably an empty string. It is not null.

In that case, municipio ?? (object)DBNull.Value will be an empty string, not null. However, municipio.Length > 0 ? municipio : (object)DBNull.Value has a value of DBNull.Value, which will generate a null in SQL Server. In this case, if municipio is null, then this code will throw. Since you say the code runs, I'm assuming that municipio is not null.

edited Jan 04 '17 at 21:07

answered Jan 04 '17 at 21:04

recursive

83,943
34
151
241

Question edited, it was messy at first. Check it please. – Dillinger Jan 04 '17 at 21:05
@Dillinger The answer is still correct. – GSerg Jan 04 '17 at 21:06
@Dillinger This answer still applies. I added a little more explanation of my assumptions. – recursive Jan 04 '17 at 21:07
It would not hurt to also mention the [correct approach](https://msdn.microsoft.com/en-us/library/system.string.isnullorempty(v=vs.110).aspx). – GSerg Jan 04 '17 at 21:12
You are correct @recursive - it enters the method as an empty string, not `null`. So, for clarification on your answer, can you add to it that i should use: `AddWithValue("@Municipio", municipio.Length > 0 ? municipio : (object)DBNull.Value)` – Dillinger Jan 04 '17 at 21:21
@Dillinger: That depends whether you want to store a 0-length string or `NULL` in your database in that case. – recursive Jan 04 '17 at 21:37

DBNull cast to (object) returns different value

1 Answers1

Linked