How to use datareader with null values

Question

Let's say I have this class:

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

I am trying to populate a general list with data coming from a database. Since GetSqlInt32 implements INullable, I would have thought that the code below would work. This is not true. 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 this is not working?

Answer 1

It seems to me that you need a transformation similar to this (using the 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 is no need to bring DbNull to this, since SqlInt32 can already represent zero values. You just need to find that before using Value .)

Answer 2

Check out this solution that was not 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 is a variation of pain reduction on the topic. If someone knows how to combine Val and Ref into one function of the template, you can freely post messages. You will need to specify the type explicitly (compiled C # cannot 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]); 

still clenching, my fingers are 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 this is a beacose return value of DBNull.Value , not null .

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

Answer 5

You need to use a special method to read when the value is null

 mylist.Add(new myclass { Field1 = rdr.IsDbNull(Field1_Ordinal)? 0: rdr.GetSqlInt32(Field1_Ordinal).Value, Field2 = rdr.IsDbNull(Field2_Ordinal)? 0: // whatever default value you wish... rdr.GetSqlInt32(Field2_Ordinal).Value // No error now }); 

Answer 6

I am trying to export an Access database with 39 fields - many with NULL values. I could not get the extension method to work, so I wrote the following function:

 private string ChkDbStr(object inObj) { if (inObj == null) { return ""; } else { return inObj.ToString(); } } 

When I read every field that may contain NULL, I encode the field read as: ChkDbStr (DbReader.GetValue (1))

Answer 7

DbNull.Value! = Null

So you need either ?: an expression or an if block to convert database zeros to C # nulls and vica versa.