Null safe way to get values from an IDataReader

Question

(LocalVariable)ABC.string(Name) = (IDataReader)dataReader.GetString(0);

This name value is coming from database.

What happening here is if this name is null while reading it's throwing an exception?

I am manually doing some if condition here. I don't want to write a manual condition to check all my variables.

I am doing something like this now..

String abc = dataReader.GetValue(0);
if (abc == null)
   //assigning null
else
   //assigning abc value

Is there something like can we write extension method for this?

Outwash

I suggest using the method you want to use: dataReader.GetString(0) returns "" (an empty string) if the column is NULL. – Maugre

Answer 1

Here is a couple extension methods that will nicely wrap up all of your concerns around retrieving strongly typed values from a data reader. If the value is DbNull the default of the type will be returned. In the case of string which is a class, a null will be returned. If the field was int, then 0 would be returned. Additionally, if you are expecting an int?, say from an nullable int field, null would be returned.

Specific Usage for Kumar's case:

string abc = datareader.GetValueOrDefault<string>(0);

General Usage

var name = GetValueOrDefault<string>(reader, "Name");

or

var name = reader.GetValueOrDefault<string>("Name");

or

var name = reader.GetValueOrDefault<string>(0);

Extension

public static class NullSafeGetter
{
   public static T GetValueOrDefault<T>(this IDataRecord row, string fieldName)
   {
       int ordinal = row.GetOrdinal(fieldName);
       return row.GetValueOrDefault<T>(ordinal);
   }

   public static T GetValueOrDefault<T>(this IDataRecord row, int ordinal)
   {
       return (T)(row.IsDBNull(ordinal) ? default(T) : row.GetValue(ordinal));
   }
}

from http://skysanders.net/subtext/archive/2010/03/02/generic-nullsafe-idatarecord-field-getter.aspx

Lisalisabet

In case when the value is DBNull and we are expecting int, isn't it bad to just return 0? Shouldn't we throw exception in that case, as something is obviously wrong - we either should be expecting int? or query yields wrong type of the value? Putting 0 where there is no actual value could be okay in some particular cases, but having this in some common used library could lead to a very subtle bugs, I think. – Predate

It's convenient to be able to pass a default value as a parameter: public static T GetValueOrDefault<T>(this IDataRecord row, int ordinal, T defaultValue = default(T)) – Girard

@Predate for int's case you want to have T be int? so the return value could be null. – Sedgewick

@Scott Not really, I don't want return value to be null, when I expect int I want the method to return int not int? and I want it to be exactly as it is in the database. Furthermore, when IDataReader is asked for an int but the underlying value that the reader has is DBNull.Value that is clearly a type mismatch, and we shouldn't return default int value, zero, nor any other value, we should throw an exception. – Predate

Lovely! Thanks for this elegant solution. – Larisalarissa

Answer 2

Similar to @sky-sanders answer but less strict with conversions:

public static T Get<T>(this IDataRecord row, string fieldName)
{
    int ordinal = row.GetOrdinal(fieldName);
    return row.Get<T>(ordinal);
}

public static T Get<T>(this IDataRecord row, int ordinal)
{
    var value = row.IsDBNull(ordinal) ? default(T) : row.GetValue(ordinal);
    return (T)Convert.ChangeType(value, typeof(T));
}

Hickerson

Answer 3

My solution is that:

private static T GetValue<T>(object o) {
    if (typeof(DBNull) != o.GetType()) {
        return (T) o;
    }
    return default(T);
}

When, Status = GetValue<string>(currentDataRow["status"])

Thenceforth

Instead of using typeof() and GetType() can't you do this: "if (o is DBNull)". In my opinion it's more readable. – Virgilio

Chances are o == DBNull.Value is faster than o is DBNull, and most certainly than typeof(DBNull) != o.GetType(). – Custom

Answer 4

Combining top solutions and suggestions, here is a C# 6 arrow expression version with support for GetValue<T> and GetValueOrDefault<T> with optional default value parameters.

public static class DataRecordExtensions {
    /// <summary>
    /// Generically extracts a field value by name from any IDataRecord as specified type. Will throw if DNE.
    /// </summary>
    public static T GetValue<T>(this IDataRecord row, string fieldName)
        => row.GetValue<T>(row.GetOrdinal(fieldName));

    /// <summary>
    /// Generically extracts a field value by ordinal from any IDataRecord as specified type. Will throw if DNE.
    /// </summary>
    public static T GetValue<T>(this IDataRecord row, int ordinal)
        => (T)row.GetValue(ordinal);

    /// <summary>
    /// Generically extracts a field value by name from any IDataRecord as specified type. Will return default generic types value if DNE.
    /// </summary>
    public static T GetValueOrDefault<T>(this IDataRecord row, string fieldName, T defaultValue = default(T))
        => row.GetValueOrDefault<T>(row.GetOrdinal(fieldName), defaultValue);

    /// <summary>
    /// Generically extracts a field value by ordinal from any IDataRecord as specified type. Will return default generic types value if DNE.
    /// </summary>
    public static T GetValueOrDefault<T>(this IDataRecord row, int ordinal, T defaultValue = default(T))
        => (T)(row.IsDBNull(ordinal) ? defaultValue : row.GetValue(ordinal));
}

Danuloff

Answer 5

I'd use something like this:

string abc = (IDataReader)datareader.GetValue(0) ?? "Default";

Aixlachapelle

"The ?? operator is called the null-coalescing operator and is used to define a default value for a nullable value types as well as reference types. It returns the left-hand operand if it is not null; otherwise it returns the right operand." msdn.microsoft.com/en-us/library/ms173224.aspx – Sherburne

have you tested this because DbNull != null – Lisalisabet

@Sky: There was no requirement to check for DbNull - the code this is replacing only checks for null. If I wrote it to check for DbNull, then I would be changing the behavior of the code and I did not want to do such a thing. – Aixlachapelle

Do you expect that an IDataReader is going to contain a null? And the requirement was for an Extension Method. But my question was: Have you tested the code you provided? It seems wonky to me. – Lisalisabet

@Sky: No. It does not even compile simply because the code that @Outwash posted explaining what he is currently doing does not compile. Both he (and I) are effectively saying string foo = (object)bar; which will not compile. Furthermore, I did not even claim that it DID compile. The idea here is for me to convey an idea, not for me to do all of his work for him. I attempted to convey the idea using the same means of communication that he used to convey his question - I thought it would be easier to apply. And the way I did it does not change the behavior of code in any way. Why the hate??? – Aixlachapelle

no hate. I am just pointing out that your answer represents neither an understanding of the underlying issue nor a working solution. Aside from that, testing GetValue against a null is just silly. If presenting a valid answer that demonstrates an understanding of the problem, whether explicitly stated or not, is more work than you want to do then perhaps not answering is the better choice. OP does not have a clear understanding of the problem domain, it is the job of the responder to remedy that, not compound it with similar junk code. peace. – Lisalisabet