SQL Data Reader - handling Null column values

Question

I'm using a SQLdatareader to build POCOs from a database. The code works except when it encounters a null value in the database. For example, if the FirstName column in the database contains a null value, an exception is thrown.

employee.FirstName = sqlreader.GetString(indexFirstName);

What is the best way to handle null values in this situation?

Answer 1

You need to check for IsDBNull:

if(!SqlReader.IsDBNull(indexFirstName))
{
  employee.FirstName = sqlreader.GetString(indexFirstName);
}

That's your only reliable way to detect and handle this situation.

I wrapped those things into extension methods and tend to return a default value if the column is indeed NULL:

public static string SafeGetString(this SqlDataReader reader, int colIndex)
{
   if(!reader.IsDBNull(colIndex))
       return reader.GetString(colIndex);
   else 
       return string.Empty;
}

Now you can call it like this:

employee.FirstName = SqlReader.SafeGetString(indexFirstName);

and you'll never have to worry about an exception or a NULL value again.

Answer 2

One way to do it is to check for db nulls:

employee.FirstName = (sqlreader.IsDBNull(indexFirstName) 
    ? ""
    : sqlreader.GetString(indexFirstName));

Answer 3

We use a series of static methods to pull all of the values out of our data readers. So in this case we'd be calling DBUtils.GetString(sqlreader(indexFirstName)) The benefit of creating static/shared methods is that you don't have to do the same checks over and over and over...

The static method(s) would contain code to check for nulls (see other answers on this page).

Answer 4

What I tend to do is replace the null values in the SELECT statement with something appropriate.

SELECT ISNULL(firstname, '') FROM people

Here I replace every null with a blank string. Your code won't throw in error in that case.

Answer 5

and / or use ternary operator with assignment:

employee.FirstName = rdr.IsNull(indexFirstName))? 
                     String.Empty: rdr.GetString(indexFirstName);

replace the default (when null) value as appropriate for each property type...

Answer 6

Check sqlreader.IsDBNull(indexFirstName) before you try to read it.

Answer 7

I think you would want to use:

SqlReader.IsDBNull(indexFirstName)

Answer 8

IsDbNull(int) is usually much slower that using methods like Get*Sql*DateTime and then comparing to DBNull.Value. Try these extension methods for SqlDataReader. Use then like:

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

---

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 9

You should use the as operator combined with the ?? operator for default values. Value types will need to be read as nullable and given a default.

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

The as operator handles the casting including the check for DBNull.