SqlDataReader Best way to check for null values -sqlDataReader.IsDBNull vs DBNull.Value

Question

I want to retrieve decimal values from the database and I would like to know which is the recommended way to check for null values.

I have seen on MSDN - DBNull.Value Field that this check is rarely used.

Thus, is the reader.IsDBNull the best/most efficient way to check for nulls?

I have created 2 sample methods:

public static decimal? GetNullableDecimal(SqlDataReader reader, string fieldName)
{
    if (reader[fieldName] == DBNull.Value)
    {
        return null;
    }
    return (decimal)reader[fieldName];
}

public static decimal? GetNullableDecimal_2(SqlDataReader reader, string fieldName)
{
    if (reader.IsDBNull(reader[fieldName]))
    {
         return null;
    }
    return (decimal)reader[fieldName];
}

Most of the time the fields are going to be null.

Thanks in advance!

False

Which ever one you prefer and find most readable. However, it is worth noting your examples are not strictly equivalent given the use of reader.GetOrdinal in the second. – Airwaves

Internally the syntax reader[fieldName] is resolved to reader.GetOrdinal(fieldName) – Herodotus

I 've amended the example to use reader[fieldName] in both cases – False

Answer 1

I would not get too caught up in the which method is better, because both work and I have used both in code before.

For instance, here is a utility function I dug up from one of my old projects:

/// <summary>
/// Helper class for SqlDataReader, which allows for the calling code to retrieve a value in a generic fashion.
/// </summary>
public static class SqlReaderHelper
{
    private static bool IsNullableType(Type theValueType)
    {
        return (theValueType.IsGenericType && theValueType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
    }

    /// <summary>
    /// Returns the value, of type T, from the SqlDataReader, accounting for both generic and non-generic types.
    /// </summary>
    /// <typeparam name="T">T, type applied</typeparam>
    /// <param name="theReader">The SqlDataReader object that queried the database</param>
    /// <param name="theColumnName">The column of data to retrieve a value from</param>
    /// <returns>T, type applied; default value of type if database value is null</returns>
    public static T GetValue<T>(this SqlDataReader theReader, string theColumnName)
    {
        // Read the value out of the reader by string (column name); returns object
        object theValue = theReader[theColumnName];

        // Cast to the generic type applied to this method (i.e. int?)
        Type theValueType = typeof(T);

        // Check for null value from the database
        if (DBNull.Value != theValue)
        {
            // We have a null, do we have a nullable type for T?
            if (!IsNullableType(theValueType))
            {
                // No, this is not a nullable type so just change the value's type from object to T
                return (T)Convert.ChangeType(theValue, theValueType);
            }
            else
            {
                // Yes, this is a nullable type so change the value's type from object to the underlying type of T
                NullableConverter theNullableConverter = new NullableConverter(theValueType);

                return (T)Convert.ChangeType(theValue, theNullableConverter.UnderlyingType);
            }
        }

        // The value was null in the database, so return the default value for T; this will vary based on what T is (i.e. int has a default of 0)
        return default(T);
    }
}

Usage:

yourSqlReaderObject.GetValue<int?>("SOME_ID_COLUMN");
yourSqlReaderObject.GetValue<string>("SOME_VALUE_COLUMN");

Class

Awesome! I have only one recommendation for adjustment to this.. change this GetValue<T>SqlDataReader to GetValue<T>(this IDataRecord – Olvan

Answer 2

If you want to check for null and handle it (as opposed to checking for null and alerting the program that it was null) you can use the as operator with the null-coalescing operator ??. So in my program

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    response.Employees.Add(new Employee() { Id = dr["id"] as int? ?? default(int), ImageUrl = dr["Photo"] as string, JobTitle = dr["JobTitle"] as string });
}

Goodtempered

Answer 3

Here's a simpler version of @Karl Anderson's answer:

public static class DbHelper
{
    public static T GetValue<T>(this SqlDataReader sqlDataReader, string columnName)
    {
        var value = sqlDataReader[columnName];

        if (value != DBNull.Value)
        {
            return (T)value;
        }

        return default(T);
    }
}

Or even:

public static class DbHelper
{
    public static T GetValue<T>(this SqlDataReader sqlDataReader, string columnName)
    {
        var value = sqlDataReader[columnName];

        return value == DBNull.Value ? default(T) : (T) value;
    }
}

Direct casting seems to work just fine for either nullable or non-nullable types.

Arty