Handle DBNull in C#

Question

Is there a better/cleaner way to do this?

int stockvalue = 0;
if (!Convert.IsDBNull(reader["StockValue"]))
    stockvalue = (int)reader["StockValue"];

Answer 1

The shortest (IMHO) is:

int stockvalue = (reader["StockValue"] as int?) ?? 0;

Explanation:

• If reader["StockValue"] is of type int, the value will be returned, and the "??" operator will return the result
• If reader["StockValue"] is NOT of type int (e.g. DBNull), null will be returned, and the "??" operator will return the value 0 (zero).

Answer 2

Yes you can use int? This way you can have a default value of null instead of 0. Since the result of stockvalue could potentially be 0 there isn't confusion as to whether the database was 0 or null. For instance like this (pre nullable) we had a default initialization of -1 to represent no value was assigned. Personally, I thought this was a little dangerous because if you forget to set it to -1, there is a data corruption issue that can be really difficult to track down.

http://msdn.microsoft.com/en-us/library/2cf62fcy(VS.80).aspx

int? stockvalue = null;

if (!Convert.IsDBNull(reader["StockValue"]))
    stockvalue = (int)reader["StockValue"];

//Then you can check 

if(stockValue.HasValue)
{
  // do something here.
}

Answer 3

use the Nullable<int> type...int? for short

Answer 4

I wrote an extension method several days ago. By using it you could just do:

int? stockvalue = reader.GetValue<int?>("StockValue");

Here's the extension method (modify to fit your needs):

public static class ReaderHelper
{
    public static bool IsNullableType(Type valueType)
    {
        return (valueType.IsGenericType &&
            valueType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
    }

    public static T GetValue<T>(this IDataReader reader, string columnName)
    {
        object value = reader[columnName];
        Type valueType = typeof(T);
        if (value != DBNull.Value)
        {
            if (!IsNullableType(valueType))
            {
                return (T)Convert.ChangeType(value, valueType);
            }
            else
            {
                NullableConverter nc = new NullableConverter(valueType);
                return (T)Convert.ChangeType(value, nc.UnderlyingType);
            }
        }
        return default(T);
    }
}

Answer 5

int? stockValue = reader["StockValue"] == null || reader["StockValue"] == DBNull.Value ? null : (int?)reader["StockValue"];

Answer 6

Not really. You could encapsulate it in a method:

public int getDBIntValue(object value, int defaultValue) {
  if (!Convert.IsDBNull(value)) {
    return (int)value;
  }
  else {
    return defaultValue;
  }

And call it like this:

stockVaue = getDBIntVaue(reader["StockValue"], 0);

Or you could use coalesce in your query to force the returned value to be non-null.

Edit - corrected dumb code errors based on comments received.