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

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 2

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 3

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 4

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

Answer 5

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 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));