How to parse value from database datareader and handle possible NULL value?

I have run into this problem a few times and have never come up with a good answer. I figure others must have dealt with this already.

I have a datareader returned from the database and I want to use the values therein however values may or may not contain NULL. I would like to have a helper function that takes in the value from the datareader and returns the value if it is not NULL and blank space if it is NULL.

The problem I have is that the data-type of the variable I am testing is variable. It can be a String, and Integer or a DateTime. Can anyone suggest a simple way to test the value and then return the开发者_开发知识库 original value (as same data-type if possible) or something else if it is NULL?

I am using VB.NET at the moment but I would also like to know how to do this in C#. Thank you.

This should work for most things I believe (I've not tested it mind):

 public T ParseValue<T>(System.Data.SqlClient.SqlDataReader reader, string column)
 {
     T result = default(T);

     if (!reader.IsDBNull(reader.GetOrdinal(column)))
         result = (T)reader.GetValue(reader.GetOrdinal(column));

     return result;
 }

And will return you the default value for that type if it is null

int v = dr.GetValue<int>("vvv");             // throws if column is null
int? w = dr.GetValue<int?>("www");           // set to null if column is null
int x = dr.GetValue<int?>("xxx") ?? -1;      // set to -1 if column is null
string y = dr.GetValue<string>("yyy");       // set to null if column is null
string z = dr.GetValue<string>("zzz") ?? ""  // set to "" if column is null

// ...

public static T GetValue<T>(this IDataRecord source, string fieldName)
{
    return source.GetValue<T>(source.GetOrdinal(fieldName));
}

public static T GetValue<T>(this IDataRecord source, int fieldIndex)
{
    if (source == null)
        throw new ArgumentNullException("source");

    if (fieldIndex < 0)
        throw new ArgumentOutOfRangeException("fieldIndex", fieldIndex,
                                              "Index cannot be negative.");

    if (source.IsDBNull(fieldIndex))
    {
        T defaultValue = default(T);
        if (defaultValue == null)
            return defaultValue;
    }

    // throws if the field is null and T is a non-nullable value type
    return (T)source.GetValue(fieldIndex);
}

I use an extension method that takes an object, the expected/final type and the default value. If the object is null (or DBNull) it returns the default value. If the object can be converted to the final/expected type it does the conversion and returns a strongly typed object. If the conversion fails it returns a default value or throws an exception based upon if you are strict or not. Here is what the method looks like -

    /// <summary>
    /// Gets the non null value.
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="item">The item.</param>
    /// <param name="defaultValue">The default value.</param>
    /// <param name="strict">if set to <c>true</c> [strict].</param>
    /// <returns></returns>
    [DebuggerStepThrough]
    public static T GetNonNullValue<T>(this object item, T defaultValue, bool strict) {
        if(item.IsNullOrEmpty() || item == DBNull.Value) {
            return defaultValue;
        }

        var originalType = item.GetType();
        var targetType = typeof(T);

        if(originalType == targetType || originalType.IsSubclassOf(targetType)) {
            return (T)item;
        }

        TypeConverter typeConverter = TypeDescriptor.GetConverter(targetType);
        if(typeConverter.CanConvertFrom(originalType)) {
            return (T)typeConverter.ConvertFrom(item);
        }

        typeConverter = TypeDescriptor.GetConverter(originalType);
        if(typeConverter.CanConvertTo(targetType)) {
            return (T)typeConverter.ConvertTo(item, targetType);
        }

        if(strict) {
            throw new QIGException("Conversion from {0} to {1} failed!", originalType, targetType);
        }

        return defaultValue;
    }

Edit1: To clarify, you'll use this with your data reader like this -

        SqlDataReader dr = GetResultsIntoDataReader();
        string column1Value = dr["ColumnName1"].GetNonNullValue(String.Empty);
        int? column2Value = dr["ColumnName2"].GetNonNullValue(new Nullable<int>());
        double column3Value = dr["ColumnName3"].GetNonNullValue(0.0);

I found that w69rdy's answer did not work for SQL Money type or Numeric. I tried using it to convert Money to float and double. Neither worked. I converted my Money type to Numeric (18,4) and still it would not convert to float or double.

In all cases it threw a type cast exception.

I did however get YetAnotherUser's answer to work with a couple minor changes.

Had to change:

if(item.IsNullOrEmpty() || item == DBNull.Value)

to

if(item == null || item == DBNull.Value) **

AND

throw new QIGException("Conversion from {0} to {1} failed!", originalType, targetType);

to

throw new Exception(string.Format("Conversion from {0} to {1} failed!", originalType, targetType));

Also added:

public static T GetNonNullValue<T>(this object item, T defaultValue)
{
    return item.GetNonNullValue(defaultValue, true);
}

so the call could be:

curInsurance.Pharmacy = dataReader["Pharmacy"].GetNonNullValue(-1d);

NOT

curInsurance.Pharmacy = dataReader["Pharmacy"].GetNonNullValue(-1d, true);

Also I had to read about Custom Extension, but man are they cool. I am going to have to explore them some more.

You can use the IDataReader.IsDBNull method to check if a field is NULL in the database.