0

I'm getting DBNull results from DB.

Trying to apply ?? operator to it like

result["field1"] ?? result["field2"]

field1 = DBNull

field2 = 4

but it doesn't work, returns {} because result["field1"] is not null (it's DBNull). I expect to get that 4 from it.

Tried to do

result["field1"] = null

first, but it doesn't work, it's still DBNull type.

The question is how to handle this, convert DBNull to null in some way? How to make ?? operator work with DBNull values?

To be more precise:

Is there a way to get COALESCE-like behaviour?

That 2 fields are just for example, in reality there will be much more fields and I'm trying to get first not null (so I was hoping to use chaining field1 ?? field2 ?? field3...)

I wasn't precise with that, sorry, my fault.

SOLUTION

Following Peter van der Heijden solution, say I'm getting from DB

result["field1"] = DBNull

result["field2"]= DBNull

result["field3"] = 4

result["field1"] ?? result["field2"] ?? result["field3"]

will return {} (first not null, DBNull is ... well ... not null)

but 

result["field1"] as int? ?? result["field2"] as int? ?? result["field3"] as int?

will return result["field3"] = 4, as expected.

int? can be replaced with whatever result type you get (like DateTime? for example).

a''
  • 2,322
  • 2
  • 23
  • 34

5 Answers5

3

You could use the slightly longer variant:

result["field1"] is DBNull ? result["field2"] : result["field1"];

?? does only work if the left operand is null and as DBNull is an instance of a class it is not null.

Side note @Amit:
The is operator does a type check. You can interpret it "as you read it": It checks if the type of result["field1"] is DBNull. See HERE. This should be faster then a value comparisson with DBNull.Value...

Christoph Fink
  • 22,727
  • 9
  • 68
  • 113
3

I tend to do that as follows:

string s = result["field1"] as string;

or

int? i = result["field2"] as int?;

Or in your case:

string s = result["field1"] as string ?? result["field2"] as string;
heijp06
  • 11,558
  • 1
  • 40
  • 60
  • THAT is a great idea - need to remember that! Also the best solution for the OP I guess... – Christoph Fink Apr 28 '14 at 09:27
  • Yes, it solves my problem. If result["field1"] = DBNull then result["field1"] == null is false BUT result["field1"] as int? == null is true. Thanks. – a'' Apr 28 '14 at 09:34
1

The ?? operator only works will actual null values, DBNull is just a type, and DBNull.Value is just a static value used to signify a null value from the database.

You can't get it to work with ??, all you can do is manually check for result["col"] != DBNull.Value, but then obviously this can be put into a method or an extension method so it reads a little nicer.

The ternary operator is about as close as you are going to get: var val = results["col"] != DBNull.Value ? (int)results["col"] : 0;

Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
1

The ?? operator will never work with DbNull.Value since it is a instance of DbNull.

Use other checks for this, like:

result["field1"] != DbNull.Value ? result["field1"] : result["field2"]
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325
1
DBNull.Value.Equals(result["field1"]) ? result["field2"]: result["field1"];
AHH
  • 981
  • 2
  • 10
  • 26