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?

score 24 · Accepted Answer · answered Sep 01 '09 at 13:51

24

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

answered Sep 01 '09 at 13:51

Jon Skeet

1,421,763
867
9,128
9,194

Thanks for this but why not something like this without using an extension method: Field2 = (rdr.IsDBNull(Field2_Ordinal) ? (int?)null : rdr.GetSqlInt32(Field2_Ordinal).Value) – Anthony Sep 01 '09 at 13:57
@Anthony: Simplicity, basically. Which version do you think is easier to read? :) (Yes, it means having the additional extension method, but you only need that *once*, however many nullable int fields you have.) – Jon Skeet Sep 01 '09 at 13:58
You could also create an extension method on DataReader called GetNullableInt32 or something like that, of course. – Jon Skeet Sep 01 '09 at 13:59
@Jon: an extension method on DataReader is probably the best way. Once created, I don't have to worry about it. Thanks. – Anthony Sep 01 '09 at 14:01
Shouldn't reader.GetFieldValue be able to handle null values? – Farinha Sep 26 '13 at 14:33
@Farinha: I honestly don't know whether it does. The documentation for DbReader.GetFieldValue doesn't mention that... – Jon Skeet Sep 26 '13 at 15:03
You're right, the documentation for DbReader.GetFieldValue even specifies what types it supports, and int? is not one of them. Went with creating an extension method as suggested. – Farinha Sep 26 '13 at 15:30

score 6 · Answer 2 · edited May 23 '17 at 12:14

6

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

edited May 23 '17 at 12:14

Community

1
1

answered Aug 26 '11 at 17:58

Ignacio Soler Garcia

21,122
31
128
207

score 4 · Answer 3 · answered Jul 22 '10 at 10:35

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

score 1 · Answer 4 · answered Sep 01 '09 at 13:49

1

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.

answered Sep 01 '09 at 13:49

Rune Grimstad

35,612
10
61
76

1

The value returned is a SqlInt32, not a DBNull.Value. – Jon Skeet Sep 01 '09 at 13:52

score 1 · Answer 5 · answered Sep 01 '09 at 13:50

1

You have to use a special method on the reader to detect 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
});

answered Sep 01 '09 at 13:50

Charles Bretana

143,358
22
150
216

in my case: Field2 = rdr.IsDbNull(Field2_Ordinal)? (int?) null: rdr.GetSqlInt32(Field2_Ordinal).Value Thanks for your answer. – Anthony Sep 01 '09 at 14:05

score 1 · Answer 6 · answered Dec 06 '10 at 20:39

I'm trying to export an Access database with 39 fields - many with NULL values. I couldn't get the extension method working so I wrote the following function:

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

As I read each field that might contain a NULL, I code the field read as: ChkDbStr(DbReader.GetValue(1))

score 0 · Answer 7 · answered Sep 01 '09 at 13:48

0

DbNull.Value != null

So you need either a ? : expression or a if block to convert database nulls to c# nulls and vica versa.

answered Sep 01 '09 at 13:48

MatthewMartin

32,326
33
105
164

How to use datareader with null values

7 Answers7