Pour a datareader value into a value before the Nullable variable

Question

I am trying to run the following code but getting a casting error. How can I rewrite my code to achieve the same?

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

thanks

Answer 1

Use the IsDbNull method for a data reader ... for example:

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

Edit

Do you need to do something similar: BOOL? nullBoolean = null;

you will have

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

Answer 2

Think about it in a function.

Here's something I've used in the past (you can do this with the 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), but instead of column names:

 public static T GetValueOrDefault<T>(this SqlDataReader dataReader, string columnName) { return !dataReader.IsDBNull(dataReader[columnName]) ? (T)dataReader.GetValue(dataReader[columnName]) : default(T); } 

using:

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

Answer 3

 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 4

There is an answer here that may be useful: https://stackoverflow.com/a/166268/

You can use the keyword "how." Pay attention to 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 5

I'm sure I found inspiration for this somewhere around interweb, but I don't seem to be able to find the source anymore. In any case, below you will find a utility class that allows you to define the extension method in the DataReader, for example:

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

Using:

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

or

  reader.Get<int?>(5) 

Here is the inclusive 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: uses the IsNullableType () extension method, defined as follows:

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

Answer 6

Here is my extension photo. The semantics of the column name and returns to default(T) when zero 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 a usage example. Remember that despite the fact that string is a reference type, it still will not be converted to null from DBNull . int? same 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

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

Answer 8

Try this version. It performs basic conversion and manages the default values.