Null column values are handled by SQL Data Reader.

Problem

To create POCOs from a database, I’m using a SQLdatareader. Except when it comes across a null value in the database, the code works. If the database’s FirstName field contains a null value, for example, an exception is thrown.

employee.FirstName = sqlreader.GetString(indexFirstName);

In this case, what is the best strategy to deal with null values?

Asked by DenaliHardtail

Solution #1

You must test for IsDBNull:

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

This is your sole reliable method of detecting and dealing with the situation.

Those things are wrapped up in extension methods, and if the column is null, I usually return a default value:

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

You can now refer to it as:

employee.FirstName = SqlReader.SafeGetString(indexFirstName);

You’ll never have to worry about a null value or an exception again.

Answered by marc_s

Solution #2

For default values, use the as operator in conjunction with the?? operator. Value types must be viewed as nullable and have a default value.

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

The as operator takes care of the casting, including the DBNull check.

Answered by stevehipwell

Solution #3

employee.FirstName = sqlreader[indexFirstName] as string;

If you cast an integer to a nullable int, you can use GetValueOrDefault on it ()

employee.Age = (sqlreader[indexAge] as int?).GetValueOrDefault();

Alternatively, you might use the null-coalescing operator (??).

employee.Age = (sqlreader[indexAge] as int?) ?? 0;

Answered by Gone Coding

Solution #4

IsDbNull(int) is typically much slower than using GetSqlDateTime and comparing to DBNull.Value. Try out these SqlDataReader extension methods.

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

Use them in the following way:

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

Answered by ZXX

Solution #5

As several replies claim, reader.IsDbNull(ColumnIndex) works.

Also, if you’re working with column names, simply comparing types can be more convenient.

if(reader["TeacherImage"].GetType() == typeof(DBNull)) { //logic }

Answered by PJ3

Post is based on https://stackoverflow.com/questions/1772025/sql-data-reader-handling-null-column-values