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

int stockvalue = reader["StockValue"] != DbNull.Value ? Convert.ToInt32(reader["StockValue"]) : 0;

Answer 2

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

Answer 3

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 4

While it's convenient to reference reader["StockValue"], it's not very efficient. It's also not strongly-typed, as it returns type object.

Instead, within your code, do something like this:

int stockValueOrdinal = reader.GetOrdinal("StockValue");
int? stockValue = reader.IsDbNull(stockValueOrdinal) ?
    null : 
    reader.GetInt32(stockValueOrdinal);

Of course, it's best to get all of the ordinals at one time, then use them throughout the code.

Answer 5

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 6

I have two following extension methods in my project:

    public static T GetValueSafe<T>(this IDataReader dataReader, string columnName, Func<IDataReader, int, T> valueExtractor)
        where T : class 
    {
        T value;
        if (dataReader.TryGetValueSafe(columnName, valueExtractor, out value))
        {
            return value;
        }

        return null;
    }

    public static bool TryGetValueSafe<T>(this IDataReader dataReader, string columnName, Func<IDataReader, int, T> valueExtractor, out T value)
    {
        int ordinal = dataReader.GetOrdinal(columnName);

        if (!dataReader.IsDBNull(ordinal))
        {
            // Get value.
            value = valueExtractor.Invoke(dataReader, ordinal);

            return true;
        }

        value = default(T);
        return false;
    }

The usage can be like this:

string companyName = dataReader.GetValueSafe("CompanyName", (reader, ordinal) => reader.GetString(ordinal));

Answer 7

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 8

The way I handle this is

int? stockvalue = reader["StockValue"] as int?;

Very simple, clean and one line. If for some reason I absolutely can't have a null value (which I find poor reasoning for usually since I'd rather know if a value has meaning or if it was unitialized for a primitive type) I would do:

int stockvalue = (reader["StockValue"] as int?).GetValueOrDefault(-1);

Answer 9

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.

Answer 10

int? stockvalue = (int?)(!Convert.IsDBNull(result) ? result : null);

One possible solution so that you ensure that the DBNull carries across to your code. For our group, as a best practice, we try and not allow NULL columns in the database unless its really needed. There is more overhead in coding to handle it, and sometimes just rethinking the problem makes it so its not required.

Answer 11

You could do this conversion directly in your DB-query, thus avoiding the special case alltogether.

But I wouldn't call that 'cleaner', unless you can consistently use that form in your code, since you would lose information by returning '0' instead of NULL from the DB.

Answer 12

Here's one way.

int stockvalue = Convert.IsDbNull(reader["StockValue"]) ? 0 : (int)reader["StockValue"];

You could also use TryParse

int stockvalue = 0
Int32.TryParse(reader["StockValue"].ToString(), out stockvalue);

Let us know which way works for you

Answer 13

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