Dealing with Null in SqlDataReader

Question

I have a reader like this:

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        var item = new Product
        {
            _x1 = reader.GetString(0),
            _x2 = reader.GetString(1),
            _x3 = reader.GetString(2),
            _x4 = reader.GetDateTime(3)
        };
        list.Add(item);
    }
}

But sometimes the database has some NULL values and I get a data are null exception, how can I deal with it?

Where the null exception appears? Anyway, put a if (reader != null or inside a try/catch — trinaldi
– trinaldi, Commented Dec 17, 2013 at 2:03
@Tico The exception fires when im creating the new Product object. — gog
– gog, Commented Dec 17, 2013 at 2:08
@MikeSchwartz I did that and still not working: public DateTime? _x3{ get; set; } — gog
– gog, Commented Dec 17, 2013 at 2:09
Yeah now that I think about it the reader.GetString() still throw an exception. — Mike Schwartz
– Mike Schwartz, Commented Dec 17, 2013 at 2:10

Eric J. · Accepted Answer · 2013-12-17 02:11:33Z

2

Most likely one of your column values is DBNull. You do not check for that.

I use a series of extension methods when reading a column that might have a null value. If the base type is not nullable, the return value is the nullable variant of the original type.

public static string GetStringOrNull(this IDataReader reader, int ordinal)
{
    return reader.IsDBNull(ordinal) ? null : reader.GetString(ordinal);
}


public static DateTime? GetDateTimeOrNull(this IDataReader reader, int ordinal)
{
    return reader.IsDBNull(ordinal) ? null : (DateTime?)reader.GetDateTime(ordinal);
}

answered Dec 17, 2013 at 2:11

Eric J.

151k65 gold badges353 silver badges563 bronze badges

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

Comments

laylarenee · Accepted Answer · 2013-12-17 02:32:55Z

The underlying problem you have here is that .NET cannot cast "NULL" to a string when you call reader.GetString(). To solve your problem, you must determine what you want to do if the value in the data reader is NULL and provide this in your programming code. Do you want to use an empty string? Do you want to use null? do you want an exception?

I would suggest writing an Extension Method for the DataReader class to create a new function that encapsulates your desired logic. This is an example:

public static string GetStringOrEmptyString(this IDataReader reader, int ordinal)
{
    if (reader.IsDBNull(ordinal)) {
        // if its DBNULL, return empty string
        return "";
    } else {
        // otherwise return thew value as string
        return reader.GetString(ordinal);
    }
}

You can use this like so:

var item = new Product
{
    _x1 = reader.GetStringOrEmptyString(0),
    _x2 = reader.GetStringOrEmptyString(1),
    _x3 = reader.GetStringOrEmptyString(2),
    _x4 = reader.GetDateTime(3)
};

Collectives™ on Stack Overflow

Dealing with Null in SqlDataReader

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related