casting datareader value to a to a Nullable variable

Question

I'm trying to run the following code but get a casting error. How can I rewrite my code to achive the same ?

boolResult= (bool?)dataReader["BOOL_FLAG"] ?? true;
intResult= (int?)dataReader["INT_VALUE"] ?? 0;

Thanks

Answer 1

Use the "IsDbNull" method on the data reader... for example:

bool? result = dataReader.IsDbNull(dataReader["Bool_Flag"]) ? null : (bool)dataReader["Bool_Flag"]

Edit

You'd need to do something akin to: bool? nullBoolean = null;

you'd have

bool? result = dataReader.IsDbNull(dataReader["Bool_Flag"]) ? nullBoolean : (bool)dataReader["Bool_Flag"]

Answer 2

Consider doing it in a function.

Here's something I used in the past (you can make this an extension method in .net 4):

public static T GetValueOrDefault<T>(SqlDataReader dataReader, System.Enum columnIndex)
{
    int index = Convert.ToInt32(columnIndex);

    return !dataReader.IsDBNull(index) ? (T)dataReader.GetValue(index) : default(T);
}

Edit

As an extension (not tested, but you get the idea), and using column names instead of index:

public static T GetValueOrDefault<T>(this SqlDataReader dataReader, string columnName)
{

    return !dataReader.IsDBNull(dataReader[columnName]) ? (T)dataReader.GetValue(dataReader[columnName]) : default(T);
}

usage:

bool? flag = dataReader.GetValueOrDefault("BOOL_COLUMN");

Answer 3

There's an answer here that might be helpful: https://stackoverflow.com/a/3308515/1255900

You can use the "as" keyword. Note the caution mentioned in the comments.

nullableBoolResult = dataReader["BOOL_FLAG"] as bool?;

Or, if you are not using nullables, as in your original post:

boolResult = (dataReader["BOOL_FLAG"] as bool?) ?? 0;

Answer 4

bool? boolResult = null;
int? intResult = null;

if (dataReader.IsDBNull(reader.GetOrdinal("BOOL_FLAG")) == false)
{
  boolResult  = dataReader.GetBoolean(reader.GetOrdinal("BOOL_FLAG"));
}
else
{
  boolResult = true;
}

if (dataReader.IsDBNull(reader.GetOrdinal("INT_VALUE")) == false)
{
   intResult= dataReader.GetInt32(reader.GetOrdinal("INT_VALUE"));
}
else
{
   intResult = 0;
}

Answer 5

using extension method:

public static T GetValueOrDefault <T> (this SqlDataReader reader, string column) {
    var isDbNull = reader[column] == DBNull.Value;
    return !isDbNull ? (T) reader[column] : default (T);
}

Answer 6

Here's my shot at an extension method. Column name semantics and falls back to default(T) when a null is encountered.

public static class DbExtensions
{
    public static T ReadAs<T>(this IDataReader reader, string col)
    {
        object val = reader[col];
        if (val is DBNull)
        {
            // Use the default if the column is null
            return default(T);
        }
        return (T)val;
    }
}

Here is the sample usage. Remember that despite string being a reference type, it will still fail to cast to null from a DBNull. The same is true with int?.

public Facility Bind(IDataReader reader)
{
    var x = new Facility();
    x.ID = reader.ReadAs<Guid>("ID");
    x.Name = reader.ReadAs<string>("Name");
    x.Capacity = reader.ReadAs<int?>("Capacity");
    x.Description = reader.ReadAs<string>("Description");
    x.Address = reader.ReadAs<string>("Address");
    return x;
}

Answer 7

I'm sure I found the inspiration for this somewhere around the interweb but I can't seem to find the original source anymore. Anyway, below you find a utility class which allows to define an extension method on DataReader, like this:

public static class DataReaderExtensions
{
    public static TResult Get<TResult>(this IDataReader reader, string name)
    {
        return reader.Get<TResult>(reader.GetOrdinal(name));
    }

    public static TResult Get<TResult>(this IDataReader reader, int c)
    {
        return ConvertTo<TResult>.From(reader[c]);
    }
 }

Usage:

  reader.Get<bool?>("columnname")

or

 reader.Get<int?>(5)

Here's the enabling utility class:

public static class ConvertTo<T>
{
    // 'Factory method delegate', set in the static constructor
    public static readonly Func<object, T> From;

    static ConvertTo()
    {
        From = Create(typeof(T));
    }

    private static Func<object, T> Create(Type type)
    {
        if (!type.IsValueType) { return ConvertRefType; }
        if (type.IsNullableType())
        {
            return (Func<object, T>)Delegate.CreateDelegate(typeof(Func<object, T>), typeof(ConvertTo<T>).GetMethod("ConvertNullableValueType", BindingFlags.NonPublic | BindingFlags.Static).MakeGenericMethod(new[] { type.GetGenericArguments()[0] }));
        }
        return ConvertValueType;
    }

    // ReSharper disable UnusedMember.Local
    // (used via reflection!)
    private static TElem? ConvertNullableValueType<TElem>(object value) where TElem : struct
    {
        if (DBNull.Value == value) { return null; }
        return (TElem)value;
    }
    // ReSharper restore UnusedMember.Local


    private static T ConvertRefType(object value)
    {
        if (DBNull.Value != value) { return (T)value; }
        return default(T);
    }

    private static T ConvertValueType(object value)
    {
        if (DBNull.Value == value)
        {
            throw new NullReferenceException("Value is DbNull");
        }
        return (T)value;
    }
}

EDIT: makes use of the IsNullableType() extension method defined like so:

    public static bool IsNullableType(this Type type)
    {
        return 
            (type.IsGenericType && !type.IsGenericTypeDefinition) && 
            (typeof (Nullable<>) == type.GetGenericTypeDefinition());
    }

Answer 8

Remember that a DBNull is not the same thing as null, so you cannot cast from one to the other. As the other poster said, you can check for DBNull using the IsDBNull() method.

Answer 9

Try this version. It performs some basic conversion and manages default values as well.