Possible to use ?? (the coalesce operator) with DBNull?

Question

If I have code similar to the following:

while(myDataReader.Read())
{
  myObject.intVal = Convert.ToInt32(myDataReader["mycolumn"] ?? 0);
}

It throws the error:

Object cannot be cast from DBNull to other types.

defining intVal as a nullable int is not an option. Is there a way for me to do the above?

possible duplicate of [Handling DBNull in C#](http://stackoverflow.com/questions/2433155/handle-dbnull-in-c-sharp) — BoltClock, Feb 24 '12 at 19:32

score 22 · Answer 1 · answered Apr 24 '14 at 18:27

22

Here's one more option:

while (myDataReader.Read())
{
    myObject.intVal = (myDataReader["mycolumn"] as int? ?? 0);
}

answered Apr 24 '14 at 18:27

Edyn

2,409
2
26
25

Absolutely best way! Thanks Edyn. – ClownCoder Mar 31 '16 at 03:51
Brilliant answer! So clean. – John Edwards Aug 10 '16 at 16:43

Bryan Boettcher · Accepted Answer · 2012-02-24T20:41:19.167

15

Can you use an extension method? (written off the top of my head)

public static class DataReaderExtensions 
{
    public static T Read<T>(this SqlDataReader reader, string column, T defaultValue = default(T))
    {
        var value = reader[column];

        return (T)((DBNull.Value.Equals(value))
                   ? defaultValue
                   : Convert.ChangeType(value, typeof(T)));
    }
}

You'd use it like:

while(myDataReader.Read())
{
  int i = myDataReader.Read<int>("mycolumn", 0);
}

edited Feb 24 '12 at 20:41

answered Feb 24 '12 at 19:33

Bryan Boettcher

4,412
1
28
49

I ended up going the extension method route. I was getting an error with the `Convert.ChangeType...` portion so I made some minor changes. – Abe Miessler Feb 24 '12 at 20:24
Can you update my answer with the 'minor changes' you had to make? – Bryan Boettcher Feb 24 '12 at 20:30
Actually I'm still having some trouble getting the casting to work. `Convert.ChangeType (reader[column], typeof(T))` does not compile, so I switched to this `(T)reader[column]` which throws a runtime exception. Any ideas? – Abe Miessler Feb 24 '12 at 20:35
That did it, thanks for the followup. Ended up rolling back my minor changes – Abe Miessler Feb 24 '12 at 20:48

Morphed · Answer 3 · 2012-02-24T20:16:09.863

7

Can you simply use Int32.Tryparse?

int number;
bool result = Int32.TryParse(myDataReader["mycolumn"].ToString(), out number);

According to the MSDN, number will contain 0 if the conversion failed

edited Feb 24 '12 at 20:16

answered Feb 24 '12 at 19:38

Morphed

3,527
2
29
55

1

+1, good answer. This would have worked but the int I am actually trying to assign to is an object property which does not work with `out`. – Abe Miessler Feb 24 '12 at 19:51
5

This doesn't compile, does it? The data reader indexer returns an object reference, TryParse takes a string parameter. Of course, you could call `ToString` on the object, but it's rather inefficient to construct the string representation of an int just so you can parse it. An unboxing conversion would be much more efficent. – phoog Feb 24 '12 at 19:53
@phoog, good point. If it's null ToString would bomb anyway. Could use `as string` instead. – Abe Miessler Feb 24 '12 at 19:55
1

@AbeMiessler `as string` would return null if the object is a boxed int, raising an exception in `TryParse`. But the DataReader's indexer is guaranteed not to return `null`; null data values are represented by `DBNull.Value` (which takes us back to where we started!). So the logic is sound if you call `.ToString()`; it's just inefficient. – phoog Feb 24 '12 at 20:01
I've added the .ToString() to make the answer compile. I certainly admit this is not an efficient solution, but thanks for the feedback! – Morphed Feb 24 '12 at 20:17

Mike Christensen · Answer 4 · 2012-02-24T19:41:27.187

5

How about something like:

object x = DBNull.Value;
int y = (x as Int32?).GetValueOrDefault(); //This will be 0

Or in your case:

int i = (myDataReader["mycolumn"] as Int32?).GetValueOrDefault();

edited Feb 24 '12 at 19:41

answered Feb 24 '12 at 19:35

Mike Christensen

88,082
50
208
326

As John Saunders pointed out in his answer to the linked "possible duplicate" question, this approach has the disadvantage of failing to raise an exception if the data type of the column changes. For example, if "mycolumn" holds a `short` value of 42, the variable `i` will have a value of `0`. A direct cast would raise an exception, calling attention to the type mismatch between the code and the database. – phoog Feb 24 '12 at 19:56
@phoog - Valid point, I guess every method has its pros and cons. Personally, I like my DB and code as tightly coupled as I can. I'd either use an `int?` in code, or not allow `null` in the database. – Mike Christensen Feb 24 '12 at 20:07

Justin Niessner · Answer 5 · 2012-02-24T19:40:06.417

2

Why not use something other than the null coalescing operator (DBNull.Value != null):

int i = myDataReader["mycolumn"] == DBNull.Value ?
            Convert.ToInt32(myDataReader["mycolumn"]) :
            0;

You could always wrap it up in a neat extension method:

public static T Read<T>(this DataReader reader, string column, T defaultVal)
{
    if(reader[column] == DBNull.Value) return defaultVal;
    return Convert.ChangeType(reader[column], typeof(T));
}

edited Feb 24 '12 at 19:40

answered Feb 24 '12 at 19:33

Justin Niessner

242,243
40
408
536

1

I considered a ternary if but `??` seems cleaner if I can pull it off. – Abe Miessler Feb 24 '12 at 19:35
@AbeMiessler I don't think `??` will work with `DBNull`; it's not actually `null`. – dlev Feb 24 '12 at 19:36
You got it the other way round. Also I would suggest this method is not that efficient. – nawfal Feb 24 '12 at 19:38
Did you just dump in my extension method posted above? – Bryan Boettcher Feb 24 '12 at 19:46
3

@insta - Nope. Just realized an extension method would be nice to have. Only so many ways to write that code though (and after looking, the code doesn't even match). – Justin Niessner Feb 24 '12 at 19:47
@JustinNiessner: I'm not upset it was just kinda confusing to see the same method name and parameters and at-a-glance the same code :) – Bryan Boettcher Feb 24 '12 at 19:59

score 1 · Answer 6 · edited Feb 24 '12 at 20:00

1

Nope, only works for nulls.

How about an extension method on object that checks for DBNull, and returns a default value instead?

//may not compile or be syntactically correct! Just the general idea.
public static object DefaultIfDBNull( this object TheObject, object DefaultValue )
{
    if( TheObject is DBNull )
        return DefaultValue;
    return TheObject;
}

edited Feb 24 '12 at 20:00

Joshua Enfield

17,642
10
51
98

answered Feb 24 '12 at 19:34

asawyer

17,642
8
59
87

Why not use generics rather than `object`? – dlev Feb 24 '12 at 19:35
@dlev In the context of dealing with the non generic datareader I thought this would be easier. It was just off the top of my head. The generic answer from insta looks pretty good. – asawyer Feb 24 '12 at 19:37

Possible to use ?? (the coalesce operator) with DBNull?

6 Answers6

Linked

Related