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.)