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

User · Answer

This method is dependent on indexFirstName which should be the zero-based column ordinal.

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

If you don't know the column index but wan't to check a name you can use this extension method instead:

public static class DataRecordExtensions
{
    public static bool HasColumn(this IDataRecord dr, string columnName)
    {
        for (int i=0; i < dr.FieldCount; i++)
        {
            if (dr.GetName(i).Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                return true;
        }
        return false;
    }
}

And use the method like this:

if(sqlReader.HasColumn("FirstName"))
{
  employee.FirstName = sqlreader["FirstName"];
}

User · Answer

There are a lot of answers here with useful info  and some wrong info  spread about  I d like to bring it all together   The short answer to the question is to check for DBNull - almost everyone agrees on this bit     Rather than using a helper method to read nullable values per SQL data type a generic method allows us to address this with a lot less code  However  you can t have a single generic method for both nullable value types and reference types  this is discussed at length in  Nullable type as a generic parameter possible  and C  generic type constraint for everything nullable    So  following on from the answers from  ZXX and  getpsyched we end up with this  2 methods for getting nullable values and I ve added a 3rd for non-null values  it completes the set based on method naming    public static T  GetNullableValueType lt T gt  this SqlDataReader sqlDataReader  string columnName  where T   struct       int columnOrdinal   sqlDataReader GetOrdinal columnName       return sqlDataReader IsDBNull columnOrdinal     T  null   sqlDataReader GetFieldValue lt T gt  columnOrdinal      public static T GetNullableReferenceType lt T gt  this SqlDataReader sqlDataReader  string columnName  where T   class       int columnOrdinal   sqlDataReader GetOrdinal columnName       return sqlDataReader IsDBNull columnOrdinal    null   sqlDataReader GetFieldValue lt T gt  columnOrdinal      public static T GetNonNullValue lt T gt  this SqlDataReader sqlDataReader  string columnName        int columnOrdinal   sqlDataReader GetOrdinal columnName       return sqlDataReader GetFieldValue lt T gt  columnOrdinal       I usually use column names  alter these if you use column indexes  Based on these method names I can tell whether I m expecting the data to be nullable or not  quite useful when looking at code written a long time ago   Tips    Not having nullable columns in the database avoids this issue  If you have control over the database then columns should be non-null by default and only nullable where necessary   Don t cast database values with the C   as  operator because if the cast is wrong it will silently return null   Using a default value expression will change database nulls to non-null values for value types like int  datetime  bit etc    Lastly  whilst testing the above methods across all SQL Server data types I discovered you can t directly get a char   from a SqlDataReader  if you want a char   you will have to get a string and use ToCharArray

User · Answer

I think you would want to use   SqlReader IsDBNull indexFirstName

User · Answer

private static void Render IList lt ListData gt  list  IDataReader reader                        while  reader Read                                   listData DownUrl    reader GetSchemaTable   Columns  DownUrl      null    Convert ToString reader  DownUrl      null                               null                 list Add listData                             reader Close

User · Answer

None of these was quite what i wanted   public static T GetFieldValueOrDefault lt T gt  this SqlDataReader reader  string name          int index   reader GetOrdinal name        T value   reader IsDBNull index    default T    reader GetFieldValue lt T gt  index        return value

User · Answer

Here is helper class which others can use if they need based on  marc s answer   public static class SQLDataReaderExtensions               public static int SafeGetInt this SqlDataReader dataReader  string fieldName                        int fieldIndex   dataReader GetOrdinal fieldName               return dataReader IsDBNull fieldIndex    0   dataReader GetInt32 fieldIndex                      public static int  SafeGetNullableInt this SqlDataReader dataReader  string fieldName                        int fieldIndex   dataReader GetOrdinal fieldName               return dataReader GetValue fieldIndex  as int                      public static string SafeGetString this SqlDataReader dataReader  string fieldName                        int fieldIndex   dataReader GetOrdinal fieldName               return dataReader IsDBNull fieldIndex    string Empty   dataReader GetString fieldIndex                      public static DateTime  SafeGetNullableDateTime this SqlDataReader dataReader  string fieldName                        int fieldIndex   dataReader GetOrdinal fieldName               return dataReader GetValue fieldIndex  as DateTime                      public static bool SafeGetBoolean this SqlDataReader dataReader  string fieldName                        return SafeGetBoolean dataReader  fieldName  false                      public static bool SafeGetBoolean this SqlDataReader dataReader  string fieldName  bool defaultValue                        int fieldIndex   dataReader GetOrdinal fieldName               return dataReader IsDBNull fieldIndex    defaultValue   dataReader GetBoolean fieldIndex

User · Answer

One way to do it is to check for db nulls   employee FirstName    sqlreader IsDBNull indexFirstName                  sqlreader GetString indexFirstName

User · Answer

For a string you can simply cast the object version  accessed using the array operator  and wind up with a null string for nulls   employee FirstName    string sqlreader indexFirstName     or  employee FirstName   sqlreader indexFirstName  as string    For integers  if you cast to a nullable int  you can use GetValueOrDefault    employee Age    sqlreader indexAge  as int   GetValueOrDefault      or the  null-coalescing operator        employee Age    sqlreader indexAge  as int      0

User · Answer

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

User · Answer

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

User · Answer

how to about creating helper methods  For String  private static string MyStringConverter object o                if  o    DBNull Value    o    null              return              return o ToString            Usage  MyStringConverter read  indexStringValue      For Int   private static int MyIntonverter object o                if  o    DBNull Value    o    null              return 0           return Convert ToInt32 o           Usage  MyIntonverter read  indexIntValue      For Date  private static DateTime  MyDateConverter object o                return  o    DBNull Value    o    null     DateTime  null   Convert ToDateTime o           Usage  MyDateConverter read  indexDateValue      Note  for DateTime declare varialbe as  DateTime  variable

User · Answer

By influencing from getpsyched s answer  I created a generic method which checks column value by its name  public static T SafeGet lt T gt  this System Data SqlClient SqlDataReader reader  string nameOfColumn      var indexOfColumn   reader GetOrdinal nameOfColumn     return reader IsDBNull indexOfColumn    default T    reader GetFieldValue lt T gt  indexOfColumn       Usage    var myVariable   SafeGet lt string gt  reader   NameOfColumn

User · Answer

and   or use ternary operator with assignment   employee FirstName   rdr IsDBNull indexFirstName                          String Empty  rdr GetString indexFirstName     replace the default  when null  value as appropriate for each property type

User · Answer

reader IsDbNull ColumnIndex  works as many answers says   And I want to mention if you working with column names  just comparing types may be more comfortable   if reader  TeacherImage   GetType      typeof DBNull       logic

User · Answer

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

User · Answer

Old question but maybe someone still need an answer   in real i worked around this issue like that  For int    public static object GatDataInt string Query  string Column                SqlConnection DBConn   new SqlConnection ConnectionString           if  DBConn State    ConnectionState Closed              DBConn Open            SqlCommand CMD   new SqlCommand Query  DBConn           SqlDataReader RDR   CMD ExecuteReader            if  RDR Read                          var Result   RDR Column               RDR Close                DBConn Close                return Result                    return 0          the same for string just return    instead of 0 as    is empty string  so you can use it like   int TotalPoints   GatDataInt QueryToGetTotalPoints  TotalPointColumn  as int     and  string Email   GatDatastring QueryToGetEmail  EmailColumn  as string    very flexible so you can insert any query to read any column and it ll never return with error

User · Answer

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

User · Answer

IsDbNull int  is usually much slower than using methods like GetSqlDateTime and then comparing to DBNull Value  Try these extension methods for SqlDataReader   public static T Def lt T gt  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 lt T gt  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 lt T gt  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 like this   var dd   r Val lt DateTime gt  ords 4    var ii   r Def lt int gt  ords 0    int nn   r Def lt int gt  ords 0

User · Answer

I am using the code listed below to handle null cells in an Excel sheet that is read in to a datatable   if   reader IsDBNull 2        row  Oracle      string reader 2

User · Answer

You can write a Generic function to check Null and include default value when it is NULL  Call this when reading Datareader  public T CheckNull lt T gt  object obj                        return  obj    DBNull Value   default T     T obj               When reading the Datareader use                          while  dr Read                                                          tblBPN InTrRecon Bpn   new tblBPN InTrRecon                                Bpn BPN Date   CheckNull lt DateTime  gt  dr  BPN Date                                 Bpn Cust Backorder Qty   CheckNull lt int  gt  dr  Cust Backorder Qty                                 Bpn Cust Min   CheckNull lt int  gt  dr  Cust Min

User · Answer

I don t think there s a NULL column value  when rows are returned within a datareader using the column name   If you do datareader  columnName   ToString    it will always give you a value that can be a empty string  String Empty if you need to compare    I would use the following and wouldn t worry too much   employee FirstName   sqlreader  columnNameForFirstName   ToString

User · Answer

Convert handles DbNull sensibly   employee FirstName   Convert ToString sqlreader GetValue indexFirstName

User · Answer

Check sqlreader IsDBNull indexFirstName  before you try to read it

User · Answer

As an addition to the answer by marc s  you can use a more generic extension method to get values from the SqlDataReader   public static T SafeGet lt T gt  this SqlDataReader reader  int col                return reader IsDBNull col    default T    reader GetFieldValue lt T gt  col

User · Answer

in c  7 0 we can do   var a   reader  quot ERateCode quot   as string  var b   reader  quot ERateLift quot   as int   var c   reader  quot Id quot   as int    so it will keep null value if it is

User · Answer

This Solution is less vendor-dependent and works with an SQL  OleDB  and MySQL Reader   public static string GetStringSafe this IDataReader reader  int colIndex        return GetStringSafe reader  colIndex  string Empty      public static string GetStringSafe this IDataReader reader  int colIndex  string defaultValue        if   reader IsDBNull colIndex           return reader GetString colIndex       else         return defaultValue     public static string GetStringSafe this IDataReader reader  string indexName        return GetStringSafe reader  reader GetOrdinal indexName       public static string GetStringSafe this IDataReader reader  string indexName  string defaultValue        return GetStringSafe reader  reader GetOrdinal indexName   defaultValue

User · Answer

you can ever check for this as well  if null   x  amp  amp  x HasRows

User · Answer

neat one-liner      while  dataReader Read          employee FirstName     dataReader IsDBNull dataReader GetOrdinal  quot FirstName quot       dataReader  quot FirstName quot   ToString      quot  quot

User · Answer

You may use the conditional operator   employee FirstName   sqlreader  indexFirstName      DBNull Value   sqlreader indexFirstName  ToString

[c#] SQL Data Reader - handling Null column values

Examples related to c#

Examples related to sqldatareader