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?

I suggest using the method you want to use: `dataReader.GetString(0)` returns `""` *(an empty string)* if the column is NULL. — Ian Boyd, Oct 22 '21 at 20:32

Sky Sanders · Accepted Answer · 2010-04-10T12:01:08.267

25

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

edited Apr 10 '10 at 12:01

answered Apr 09 '10 at 19:17

Sky Sanders

36,396
8
69
90

1

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. – poke Feb 28 '14 at 14:44
It's convenient to be able to pass a default value as a parameter: `public static T GetValueOrDefault(this IDataRecord row, int ordinal, T defaultValue = default(T))` – mistika Oct 08 '14 at 00:47
@poke for int's case you want to have T be `int?` so the return value could be null. – Scott Chamberlain Jul 19 '15 at 20:56
@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. – poke Aug 06 '15 at 17:13
Lovely! Thanks for this elegant solution. – Scott Fraley May 18 '17 at 22:11

pomber · Answer 2 · 2015-07-19T20:40:43.947

4

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

edited Jul 19 '15 at 20:40

answered Jul 19 '15 at 20:34

pomber

23,132
10
81
94

score 2 · Answer 3 · answered Mar 15 '16 at 18:05

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

score 2 · Answer 4 · answered Apr 01 '11 at 23:18

2

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"])

answered Apr 01 '11 at 23:18

Custodio

8,594
15
80
115

4

Instead of using typeof() and GetType() can't you do this: "if (o is DBNull)". In my opinion it's more readable. – iheartcsharp Apr 04 '12 at 15:47
Chances are `o == DBNull.Value` is faster than `o is DBNull`, and most certainly than `typeof(DBNull) != o.GetType()`. – nawfal Dec 01 '17 at 12:15

score 0 · Answer 5 · answered Apr 09 '10 at 18:31

0

I'd use something like this:

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

answered Apr 09 '10 at 18:31

Jaxidian

13,081
8
83
125

"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." http://msdn.microsoft.com/en-us/library/ms173224.aspx – Powerlord Apr 09 '10 at 19:34
2

have you tested this because `DbNull != null` – Sky Sanders Apr 09 '10 at 19:38
@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. – Jaxidian Apr 09 '10 at 20:21
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. – Sky Sanders Apr 09 '10 at 20:36
@Sky: No. It does not even compile simply because the code that @kumar 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??? – Jaxidian Apr 09 '10 at 21:46
6

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. – Sky Sanders Apr 10 '10 at 11:50

Null safe way to get values from an IDataReader

5 Answers5

Linked