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

Question

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.

Iain Ward · Accepted Answer · 2011-02-16 15:30:10Z

8

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

edited Feb 16, 2011 at 15:30

answered Feb 16, 2011 at 14:53

Iain Ward

9,9765 gold badges37 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

webworm Over a year ago

I like this. Simple yet effective. Now I just need to convert it to VB.NET.

Iain Ward Over a year ago

@webworm Excellent, let me know how you get on with it. Also note the update I've just made, I've simplified it slightly and fixed an error with checking if its null (I'd been using a custom reader that lets you check if a columns null by the name, but the SqlDataReader needs an index)

LukeH · Accepted Answer · 2011-02-16 14:53:23Z

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

YetAnotherUser · Accepted Answer · 2011-02-16 15:13:05Z

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

lethalMango · Accepted Answer · 2011-06-09 18:56:44Z

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.

Johann Blais · Accepted Answer · 2011-02-16 14:47:36Z

0

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

answered Feb 16, 2011 at 14:47

Johann Blais

9,4596 gold badges50 silver badges66 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related