How to use datareader with null values

Question

Say I have this class:

class myclass
{
    public int Field1{ get; set; }
    public int? Field2 { get; set; } //Note Field2 is nullable
 }

I'm trying to populate a generic list with data coming from a database. As GetSqlInt32 implements INullable I would have thought that the below code would work. It doesn't. It generates an error if Field2 is null.

List<myclass> mylist=new List<myclass>();

int Field1_Ordinal = rdr.GetOrdinal("Field1");
int Field2_Ordinal = rdr.GetOrdinal("Field2");

SqlDataReader rdr = cmd.ExecuteReader(); //Execute a stored procedure to retrieve data from the database

while (rdr.Read())
 {
   mylist.Add(new myclass
   {
      Field1 = rdr.GetSqlInt32(Field1_Ordinal).Value,
      Field2 = rdr.GetSqlInt32(Field2_Ordinal).Value  //Error if field2 is null
   });
 }

Any ideas why it doesn't work?

Answer 1

It seems to me that you need a conversion like this (using an extension method for convenience):

public static int? ToNullableInt32(this SqlInt32 value)
{
    return value.IsNull ? (int?) null : value.Value;
}

Then:

Field2 = rdr.GetSqlInt32(Field2_Ordinal).ToNullableInt32()

(Comment on other answers: there's no need to bring DbNull into this, as SqlInt32 can already represent null values. You just need to detect that before using Value.)

Answer 2

Check out this solution which wasn't written by me:

employee.FirstName = sqlreader[indexFirstName] as string;
employee.Age = sqlreader[indexAge] as int? ?? default(int);

It was originally proposed here:

SQL Data Reader - handling Null column values

Answer 3

Here's a pain reduction variation on the theme. If someone knows how to merge Val and Ref into one template function fell free to post. You will have to state the type explicitly (C# compiled can't be bothered :-) but this:

var dd = r.Val<DateTime>(ords[4]);
var ii = r.Def<int>(ords[0]);
int nn = r.Def<int>(ords[0]);

is still maked my fingers happy :-)

public static T Def<T>(this SqlDataReader r, int ord)
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return default(T);
    return ((INullable)t).IsNull ? default(T) : (T)t;
}

public static T? Val<T>(this SqlDataReader r, int ord) where T:struct
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return null;
    return ((INullable)t).IsNull ? (T?)null : (T)t;
}

public static T Ref<T>(this SqlDataReader r, int ord) where T : class
{
    var t = r.GetSqlValue(ord);
    if (t == DBNull.Value) return null;
    return ((INullable)t).IsNull ? null : (T)t;
}

Answer 4

I think it is beacuse the value returned is DBNull.Value, and not null.

Instead you can use the IsDbNull() method to check if the field is null before reading it.