Handling null values with SqlDataReader

eggie5

See the below C# snippet that's itterating over a SqlDataReader:

while (reader.Read())
{
c.ID = reader.GetInt32(0);
c.Category.ID = reader.GetInt32(1);
c.Subject = reader.GetString(2).Trim();
c.Body = reader.GetString(3).Trim();
c.SourceVideoClip = reader.GetString(4).Trim();
c.StillImage = reader.GetString(5).Trim();
c.Notes = reader.GetString(6).Trim();
c.UploadTime = reader.GetDateTime(7);
c.AdID = reader.GetInt32(8);
c.ValidAfter = reader.GetDateTime(9);
c.InvalidAfter = reader.GetDateTime(10);
c.BillingCode = reader.GetInt32(11);
c.Name= reader.GetString(12).Trim();
c.Birth = reader.GetDateTime(13);
c.Category.Name = reader.GetString(14);
}

Everything runs fine until it gets to reader.GetString(4).Trim();, which is null, so it throws an SqlNullValueException exception. How can I gracefully handle these null values. It seems GetString & GetInt just throw exceptions when they encounter null values. I guess the solution I'm looking for would return null or 0 for a db null value. How am I supposed to deal with this. Do I have to put if statements around each GetXX to check if it's null?

/\ |_ E X E GG

Pete OHanlon

I tend to use a set of wrappers to handle these. For instance:

public static string GetValue(IDataReader reader, string columnName)
{
   int pos = reader.GetOrdinal(columnName);
   if (reader.IsDBNull(pos))
      return string.Empty;
   return reader.GetString(columnName).Trim();
}

This would make your code easier to maintain because you could do:

c.Notes = Utility.GetValue(reader, "Notes");

Deja View - the feeling that you've seen this post before.

Pete OHanlon

Another way to do this is to use a generic function:

public static T GetData<T>(IDataReader reader, string column)
{
  if (reader.IsDBNull(reader.GetOrdinal(column)))
    return default(T);
  return (T)reader[reader.GetOrdinal(column)];
}

Then, call it using

c.Category.Name = Utility.GetData<string>(reader, "categoryName");

Deja View - the feeling that you've seen this post before.

Mike Dimmick

If you can't handle NULL values in the client code, don't let them get into the databases. Set the column's nullable property to false (if creating the table in SQL, use columnname datatype NOT NULL). I tend to use a cast with the indexed property which returns an object, which will leave a reference variable (such as a string) set to null, but cause a NullReferenceException if casting to a value type like int. In general I prefer not to allow NULLs in the columns, at least in part to ensure that you can detect an outer join that didn't have a match (useful for doing a multi-column NOT IN equivalent).

Stability. What an interesting concept. -- Chris Maunder

eggie5

I like this!

/\ |_ E X E GG

Pete OHanlon

The issue isn't so much with the OP being unable to handle nulls in his code, but more to do with him looking for a simple way to handle them in the minimum amount of code. Now, nulls definitely cause some contention - they almost seem to cause religious hysteria among developers. At their most basic, they are useful for identifying the absence of a value. Suppose that you wanted to capture if a person was married or not, it is valid to have three conditions; Yes, No and Unkown (or null). This could be because somebody didn't ask the question so you don't know whether or not they are. Oh well, that's enough rambling about nulls. It's time for me to go get a life again.

Deja View - the feeling that you've seen this post before.