Most efficient way to check for DBNull and then assign to a variable

Question

This question comes up occasionally  but I haven t seen a satisfactory answer   A typical pattern is  row is a DataRow     if  row  value      DBNull Value           someObject Member   row  value         My first question is which is more efficient  I ve flipped the condition      row  value      DBNull Value     Or   row  value   is DBNull     Or   row  value   GetType      typeof DBNull     Or    any suggestions    This indicates that  GetType   should be faster  but maybe the compiler knows a few tricks I don t   Second question  is it worth caching the value of row  value   or does the compiler optimize the indexer away anyway   For example     object valueHolder    if  DBNull Value     valueHolder   row  value          Notes    row  value   exists  I don t know the column index of the column  hence the column name lookup   I m asking specifically about checking for DBNull and then assignment  not about premature optimization  etc      I benchmarked a few scenarios  time in seconds  10 000 000 trials    row  value      DBNull Value  00 00 01 5478995 row  value   is DBNull  00 00 01 6306578 row  value   GetType      typeof DBNull   00 00 02 0138757   Object ReferenceEquals has the same performance as       The most interesting result  If you mismatch the name of the column by case  for example   Value  instead of  value   it takes roughly ten times longer  for a string    row  Value      DBNull Value  00 00 12 2792374   The moral of the story seems to be that if you can t look up a column by its index  then ensure that the column name you feed to the indexer matches the DataColumn s name exactly   Caching the value also appears to be nearly twice as fast   No Caching  00 00 03 0996622 With Caching  00 00 01 5659920   So the most efficient method seems to be    object temp   string variable   if  DBNull Value     temp   row  value              variable   temp ToString

User · Answer

I always use    if  row  value      DBNull Value    someObject Member   row  value      Found it short and comprehensive

User · Answer

public static class DBH            lt summary gt          Return default T  if supplied with DBNull Value          lt  summary gt           lt typeparam name  T  gt  lt  typeparam gt           lt param name  value  gt  lt  param gt           lt returns gt  lt  returns gt      public static T Get lt T gt  object value                   return value    DBNull Value   default T     T value            use like this  DBH Get lt String gt  itemRow  MyField

User · Answer

I personally favour this syntax  which uses the explicit IsDbNull method exposed by IDataRecord  and caches the column index to avoid a duplicate string lookup   Expanded for readability  it goes something like   int columnIndex   row GetOrdinal  Foo    string foo     the variable we re assigning based on the column value  if  row IsDBNull columnIndex       foo   String Empty     or whatever   else      foo   row GetString columnIndex       Rewritten to fit on a single line for compactness in DAL code - note that in this example we re assigning int bar   -1 if row  Bar   is null   int i     can be reused for every field  string foo     row IsDBNull i    row GetOrdinal  Foo      null   row GetString i    int bar    row IsDbNull i   row GetOrdinal  Bar      -1   row GetInt32 i      The inline assignment can be confusing if you don t know it s there  but it keeps the entire operation on one line  which I think enhances readability when you re populating properties from multiple columns in one block of code

User · Answer

I must be missing something  Isn t checking for DBNull exactly what the DataRow IsNull method does   I ve been using the following two extension methods   public static T  GetValue lt T gt  this DataRow row  string columnName  where T   struct       if  row IsNull columnName           return null       return row columnName  as T      public static string GetText this DataRow row  string columnName        if  row IsNull columnName           return string Empty       return row columnName  as string    string Empty      Usage   int  id   row GetValue lt int gt   Id    string name   row GetText  Name    double  price   row GetValue lt double gt   Price      If you didn t want Nullable lt T gt  return values for GetValue lt T gt   you could easily return default T  or some other option instead     On an unrelated note  here s a VB NET alternative to Stevo3000 s suggestion   oSomeObject IntMember   If TryConvert Of Integer  oRow  Value     iDefault  oSomeObject StringMember   If TryCast oRow  Name    String   sDefault   Function TryConvert Of T As Structure  ByVal obj As Object  As T      If TypeOf obj Is T Then         Return New T  DirectCast obj  T       Else         Return Nothing     End If End Function

User · Answer

Not that I ve done this  but you could get around the double indexer call and still keep your code clean by using a static   extension method   Ie   public static IsDBNull lt T gt  this object value  T default        return  value    DBNull Value            default            T value     public static IsDBNull lt T gt  this object value        return value IsDBNull default T        Then   IDataRecord record     Comes from somewhere  entity StringProperty   record  StringProperty   IsDBNull lt string gt  null   entity Int32Property   record  Int32Property   IsDBNull lt int gt  50    entity NoDefaultString   record  NoDefaultString   IsDBNull lt string gt     entity NoDefaultInt   record  NoDefaultInt   IsDBNull lt int gt       Also has the benefit of keeping the null checking logic in one place  Downside is  of course  that it s an extra method call    Just a thought

User · Answer

I try to avoid this check as much as possible     Obviously doesn t need to be done for columns that can t hold null   If you re storing in a Nullable value type  int   etc    you can just convert using as int    If you don t need to differentiate between string Empty and null  you can just call  ToString    since DBNull will return string Empty

User · Answer

There is the troublesome case where the object could be a string  The below extension method code handles all cases  Here s how you would use it       static void Main string   args                object number   DBNull Value           int newNumber   number SafeDBNull lt int gt              Console WriteLine newNumber                public static T SafeDBNull lt T gt  this object value  T defaultValue                 if  value    null              return default T            if  value is string              return  T  Convert ChangeType value  typeof T             return  value    DBNull Value    defaultValue    T value              public static T SafeDBNull lt T gt  this object value                  return value SafeDBNull default T

User · Answer

if in a DataRow the row  fieldname   isDbNull replace it with 0 otherwise get the decimal value   decimal result   rw  fieldname   as decimal     0

User · Answer

The compiler won t optimise away the indexer  i e  if you use row  value   twice   so yes it is slightly quicker to do   object value   row  value      and then use value twice  using  GetType   risks issues if it is null     DBNull Value is actually a singleton  so to add a 4th option - you could perhaps use ReferenceEquals - but in reality  I think you re worrying too much here    I don t think the speed different between  is        etc is going to be the cause of any performance problem you are seeing  Profile your entire code and focus on something that matters    it won t be this

User · Answer

I try to avoid this check as much as possible     Obviously doesn t need to be done for columns that can t hold null   If you re storing in a Nullable value type  int   etc    you can just convert using as int    If you don t need to differentiate between string Empty and null  you can just call  ToString    since DBNull will return string Empty

User · Answer

I personally favour this syntax  which uses the explicit IsDbNull method exposed by IDataRecord  and caches the column index to avoid a duplicate string lookup   Expanded for readability  it goes something like   int columnIndex   row GetOrdinal  Foo    string foo     the variable we re assigning based on the column value  if  row IsDBNull columnIndex       foo   String Empty     or whatever   else      foo   row GetString columnIndex       Rewritten to fit on a single line for compactness in DAL code - note that in this example we re assigning int bar   -1 if row  Bar   is null   int i     can be reused for every field  string foo     row IsDBNull i    row GetOrdinal  Foo      null   row GetString i    int bar    row IsDbNull i   row GetOrdinal  Bar      -1   row GetInt32 i      The inline assignment can be confusing if you don t know it s there  but it keeps the entire operation on one line  which I think enhances readability when you re populating properties from multiple columns in one block of code

User · Answer

I personally favour this syntax  which uses the explicit IsDbNull method exposed by IDataRecord  and caches the column index to avoid a duplicate string lookup   Expanded for readability  it goes something like   int columnIndex   row GetOrdinal  Foo    string foo     the variable we re assigning based on the column value  if  row IsDBNull columnIndex       foo   String Empty     or whatever   else      foo   row GetString columnIndex       Rewritten to fit on a single line for compactness in DAL code - note that in this example we re assigning int bar   -1 if row  Bar   is null   int i     can be reused for every field  string foo     row IsDBNull i    row GetOrdinal  Foo      null   row GetString i    int bar    row IsDbNull i   row GetOrdinal  Bar      -1   row GetInt32 i      The inline assignment can be confusing if you don t know it s there  but it keeps the entire operation on one line  which I think enhances readability when you re populating properties from multiple columns in one block of code

User · Answer

You should use the method   Convert IsDBNull     Considering it s built-in to the Framework  I would expect this to be the most efficient   I d suggest something along the lines of   int  myValue    Convert IsDBNull row  column      null    int   Convert ToInt32 row  column        And yes  the compiler should cache it for you

User · Answer

I always use    if  row  value      DBNull Value    someObject Member   row  value      Found it short and comprehensive

User · Answer

This is how I handle reading from DataRows      lt summary gt      Handles operations for Enumerations     lt  summary gt  public static class DataRowUserExtensions            lt summary gt          Gets the specified data row           lt  summary gt           lt typeparam name  T  gt  lt  typeparam gt           lt param name  dataRow  gt The data row  lt  param gt           lt param name  key  gt The key  lt  param gt           lt returns gt  lt  returns gt      public static T Get lt T gt  this DataRow dataRow  string key                return  T  ChangeTypeTo lt T gt  dataRow key               private static object ChangeTypeTo lt T gt  this object value                Type underlyingType   typeof  T           if  underlyingType    null              throw new ArgumentNullException  value             if  underlyingType IsGenericType  amp  amp  underlyingType GetGenericTypeDefinition   Equals typeof  Nullable lt  gt                           if  value    null                  return null              var converter   new NullableConverter underlyingType               underlyingType   converter UnderlyingType                        Try changing to Guid           if  underlyingType    typeof  Guid                         try                               return new Guid value ToString                               catch                                return null                                  return Convert ChangeType value  underlyingType             Usage example   if  dbRow Get lt int gt   Type      1        newNode   new TreeViewNode                                           ToolTip   dbRow Get lt string gt   Name                          Text    dbRow Get lt string gt   Name   Length  gt  25   dbRow Get lt string gt   Name   Substring 0  25            dbRow Get lt string gt   Name                           ImageUrl    file gif                         ID   dbRow Get lt string gt   ReportPath                          Value   dbRow Get lt string gt   ReportDescription   Replace                                   NavigateUrl      ReportType     dbRow Get lt string gt   ReportPath                             Props to Monsters Got My  Net for ChageTypeTo code

User · Answer

I ve done something similar with extension methods  Here s my code   public static class DataExtensions            lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName                return GetColumnValue lt T gt  record  columnName  default T                    lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt param name  defaultValue  gt The value to return if the column contains a  lt value gt DBNull Value lt  value gt  value  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName  T defaultValue                object value   record columnName           if  value    null    value    DBNull Value                        return defaultValue                    else                       return  T value                      To use it  you would do something like  int number   record GetColumnValue lt int gt   Number  0

User · Answer

The compiler won t optimise away the indexer  i e  if you use row  value   twice   so yes it is slightly quicker to do   object value   row  value      and then use value twice  using  GetType   risks issues if it is null     DBNull Value is actually a singleton  so to add a 4th option - you could perhaps use ReferenceEquals - but in reality  I think you re worrying too much here    I don t think the speed different between  is        etc is going to be the cause of any performance problem you are seeing  Profile your entire code and focus on something that matters    it won t be this

User · Answer

I feel only very few approaches here doesn t risk the prospect OP the most worry  Marc Gravell  Stevo3000  Richard Szalay  Neil  Darren Koppand  and most are unnecessarily complex  Being fully aware this is useless micro-optimization  let me say you should basically employ these   1  Don t read the value from DataReader DataRow twice - so either cache it before null checks and casts conversions or even better directly pass your record X  object to a custom extension method with appropriate signature   2  To obey the above  do not use built in IsDBNull function on your DataReader DataRow since that calls the record X  internally  so in effect you will be doing it twice    3  Type comparison will be always slower than value comparison as a general rule  Just do record X     DBNull Value better   4  Direct casting will be faster than calling Convert class for converting  though I fear the latter will falter less   5  Lastly  accessing record by index rather than column name will be faster again      I feel going by the approaches of Szalay  Neil and Darren Koppand will be better  I particularly like Darren Koppand s extension method approach which takes in IDataRecord  though I would like to narrow it down further to IDataReader  and index column name    Take care to call it   record GetColumnValue lt int  gt   field      and not  record GetColumnValue lt int gt   field      in case you need to differentiate between 0 and DBNull  For example  if you have null values in enum fields  otherwise default MyEnum  risks first enum value being returned  So better call record GetColumnValue lt MyEnum  gt   Field      Since you re reading from a DataRow  I would create extension method for both DataRow and IDataReader by DRYing common code   public static T Get lt T gt  this DataRow dr  int index  T defaultValue   default T         return dr index  Get lt T gt  defaultValue      static T Get lt T gt  this object obj  T defaultValue    Private method on object   just to use internally        if  obj IsNull            return defaultValue       return  T obj     public static bool IsNull lt T gt  this T obj  where T   class        return  object obj    null    obj    DBNull Value      public static T Get lt T gt  this IDataReader dr  int index  T defaultValue   default T         return dr index  Get lt T gt  defaultValue       So now call it like   record Get lt int gt  1     if DBNull should be treated as 0 record Get lt int  gt  1     if DBNull should be treated as null record Get lt int gt  1  -1     if DBNull should be treated as a custom value  say -1   I believe this is how it should have been in the framework  instead of the record GetInt32  record GetString etc methods  in the first place - no run-time exceptions and gives us the flexibility to handle null values   From my experience I had less luck with one generic method to read from the database  I always had to custom handle various types  so I had to write my own GetInt  GetEnum  GetGuid  etc  methods in the long run  What if you wanted to trim white spaces when reading string from db by default  or treat DBNull as empty string  Or if your decimal should be truncated of all trailing zeroes  I had most trouble with Guid type where different connector drivers behaved differently that too when underlying databases can store them as string or binary  I have an overload like this   static T Get lt T gt  this object obj  T defaultValue  Func lt object  T gt  converter        if  obj IsNull            return defaultValue       return converter     null    T obj   converter obj       With Stevo3000 s approach  I find the calling a bit ugly and tedious  and it will be harder to make a generic function out of it

User · Answer

I have IsDBNull in a program which reads a lot of data from a database  With IsDBNull it loads data in about 20 seconds  Without IsDBNull  about 1 second   So I think it is better to use   public String TryGetString SqlDataReader sqlReader  int row        String res           try               res   sqlReader GetString row             catch  Exception                   return res

User · Answer

I ve done something similar with extension methods  Here s my code   public static class DataExtensions            lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName                return GetColumnValue lt T gt  record  columnName  default T                    lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt param name  defaultValue  gt The value to return if the column contains a  lt value gt DBNull Value lt  value gt  value  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName  T defaultValue                object value   record columnName           if  value    null    value    DBNull Value                        return defaultValue                    else                       return  T value                      To use it  you would do something like  int number   record GetColumnValue lt int gt   Number  0

User · Answer

I ve done something similar with extension methods  Here s my code   public static class DataExtensions            lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName                return GetColumnValue lt T gt  record  columnName  default T                    lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt param name  defaultValue  gt The value to return if the column contains a  lt value gt DBNull Value lt  value gt  value  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName  T defaultValue                object value   record columnName           if  value    null    value    DBNull Value                        return defaultValue                    else                       return  T value                      To use it  you would do something like  int number   record GetColumnValue lt int gt   Number  0

User · Answer

The compiler won t optimise away the indexer  i e  if you use row  value   twice   so yes it is slightly quicker to do   object value   row  value      and then use value twice  using  GetType   risks issues if it is null     DBNull Value is actually a singleton  so to add a 4th option - you could perhaps use ReferenceEquals - but in reality  I think you re worrying too much here    I don t think the speed different between  is        etc is going to be the cause of any performance problem you are seeing  Profile your entire code and focus on something that matters    it won t be this

User · Answer

This is how I handle reading from DataRows      lt summary gt      Handles operations for Enumerations     lt  summary gt  public static class DataRowUserExtensions            lt summary gt          Gets the specified data row           lt  summary gt           lt typeparam name  T  gt  lt  typeparam gt           lt param name  dataRow  gt The data row  lt  param gt           lt param name  key  gt The key  lt  param gt           lt returns gt  lt  returns gt      public static T Get lt T gt  this DataRow dataRow  string key                return  T  ChangeTypeTo lt T gt  dataRow key               private static object ChangeTypeTo lt T gt  this object value                Type underlyingType   typeof  T           if  underlyingType    null              throw new ArgumentNullException  value             if  underlyingType IsGenericType  amp  amp  underlyingType GetGenericTypeDefinition   Equals typeof  Nullable lt  gt                           if  value    null                  return null              var converter   new NullableConverter underlyingType               underlyingType   converter UnderlyingType                        Try changing to Guid           if  underlyingType    typeof  Guid                         try                               return new Guid value ToString                               catch                                return null                                  return Convert ChangeType value  underlyingType             Usage example   if  dbRow Get lt int gt   Type      1        newNode   new TreeViewNode                                           ToolTip   dbRow Get lt string gt   Name                          Text    dbRow Get lt string gt   Name   Length  gt  25   dbRow Get lt string gt   Name   Substring 0  25            dbRow Get lt string gt   Name                           ImageUrl    file gif                         ID   dbRow Get lt string gt   ReportPath                          Value   dbRow Get lt string gt   ReportDescription   Replace                                   NavigateUrl      ReportType     dbRow Get lt string gt   ReportPath                             Props to Monsters Got My  Net for ChageTypeTo code

User · Answer

Not that I ve done this  but you could get around the double indexer call and still keep your code clean by using a static   extension method   Ie   public static IsDBNull lt T gt  this object value  T default        return  value    DBNull Value            default            T value     public static IsDBNull lt T gt  this object value        return value IsDBNull default T        Then   IDataRecord record     Comes from somewhere  entity StringProperty   record  StringProperty   IsDBNull lt string gt  null   entity Int32Property   record  Int32Property   IsDBNull lt int gt  50    entity NoDefaultString   record  NoDefaultString   IsDBNull lt string gt     entity NoDefaultInt   record  NoDefaultInt   IsDBNull lt int gt       Also has the benefit of keeping the null checking logic in one place  Downside is  of course  that it s an extra method call    Just a thought

User · Answer

if in a DataRow the row  fieldname   isDbNull replace it with 0 otherwise get the decimal value   decimal result   rw  fieldname   as decimal     0

User · Answer

You should use the method   Convert IsDBNull     Considering it s built-in to the Framework  I would expect this to be the most efficient   I d suggest something along the lines of   int  myValue    Convert IsDBNull row  column      null    int   Convert ToInt32 row  column        And yes  the compiler should cache it for you

User · Answer

I would use the following code in C   VB NET is not as simple    The code assigns the value if it is not null DBNull  otherwise it asigns the default which could be set to the LHS value allowing the compiler to ignore the assign   oSomeObject IntMemeber   oRow  Value   as int     iDefault  oSomeObject StringMember   oRow  Name   as string    sDefault

User · Answer

There is the troublesome case where the object could be a string  The below extension method code handles all cases  Here s how you would use it       static void Main string   args                object number   DBNull Value           int newNumber   number SafeDBNull lt int gt              Console WriteLine newNumber                public static T SafeDBNull lt T gt  this object value  T defaultValue                 if  value    null              return default T            if  value is string              return  T  Convert ChangeType value  typeof T             return  value    DBNull Value    defaultValue    T value              public static T SafeDBNull lt T gt  this object value                  return value SafeDBNull default T

User · Answer

I always use    if  row  value      DBNull Value    someObject Member   row  value      Found it short and comprehensive

User · Answer

I must be missing something  Isn t checking for DBNull exactly what the DataRow IsNull method does   I ve been using the following two extension methods   public static T  GetValue lt T gt  this DataRow row  string columnName  where T   struct       if  row IsNull columnName           return null       return row columnName  as T      public static string GetText this DataRow row  string columnName        if  row IsNull columnName           return string Empty       return row columnName  as string    string Empty      Usage   int  id   row GetValue lt int gt   Id    string name   row GetText  Name    double  price   row GetValue lt double gt   Price      If you didn t want Nullable lt T gt  return values for GetValue lt T gt   you could easily return default T  or some other option instead     On an unrelated note  here s a VB NET alternative to Stevo3000 s suggestion   oSomeObject IntMember   If TryConvert Of Integer  oRow  Value     iDefault  oSomeObject StringMember   If TryCast oRow  Name    String   sDefault   Function TryConvert Of T As Structure  ByVal obj As Object  As T      If TypeOf obj Is T Then         Return New T  DirectCast obj  T       Else         Return Nothing     End If End Function

User · Answer

I feel only very few approaches here doesn t risk the prospect OP the most worry  Marc Gravell  Stevo3000  Richard Szalay  Neil  Darren Koppand  and most are unnecessarily complex  Being fully aware this is useless micro-optimization  let me say you should basically employ these   1  Don t read the value from DataReader DataRow twice - so either cache it before null checks and casts conversions or even better directly pass your record X  object to a custom extension method with appropriate signature   2  To obey the above  do not use built in IsDBNull function on your DataReader DataRow since that calls the record X  internally  so in effect you will be doing it twice    3  Type comparison will be always slower than value comparison as a general rule  Just do record X     DBNull Value better   4  Direct casting will be faster than calling Convert class for converting  though I fear the latter will falter less   5  Lastly  accessing record by index rather than column name will be faster again      I feel going by the approaches of Szalay  Neil and Darren Koppand will be better  I particularly like Darren Koppand s extension method approach which takes in IDataRecord  though I would like to narrow it down further to IDataReader  and index column name    Take care to call it   record GetColumnValue lt int  gt   field      and not  record GetColumnValue lt int gt   field      in case you need to differentiate between 0 and DBNull  For example  if you have null values in enum fields  otherwise default MyEnum  risks first enum value being returned  So better call record GetColumnValue lt MyEnum  gt   Field      Since you re reading from a DataRow  I would create extension method for both DataRow and IDataReader by DRYing common code   public static T Get lt T gt  this DataRow dr  int index  T defaultValue   default T         return dr index  Get lt T gt  defaultValue      static T Get lt T gt  this object obj  T defaultValue    Private method on object   just to use internally        if  obj IsNull            return defaultValue       return  T obj     public static bool IsNull lt T gt  this T obj  where T   class        return  object obj    null    obj    DBNull Value      public static T Get lt T gt  this IDataReader dr  int index  T defaultValue   default T         return dr index  Get lt T gt  defaultValue       So now call it like   record Get lt int gt  1     if DBNull should be treated as 0 record Get lt int  gt  1     if DBNull should be treated as null record Get lt int gt  1  -1     if DBNull should be treated as a custom value  say -1   I believe this is how it should have been in the framework  instead of the record GetInt32  record GetString etc methods  in the first place - no run-time exceptions and gives us the flexibility to handle null values   From my experience I had less luck with one generic method to read from the database  I always had to custom handle various types  so I had to write my own GetInt  GetEnum  GetGuid  etc  methods in the long run  What if you wanted to trim white spaces when reading string from db by default  or treat DBNull as empty string  Or if your decimal should be truncated of all trailing zeroes  I had most trouble with Guid type where different connector drivers behaved differently that too when underlying databases can store them as string or binary  I have an overload like this   static T Get lt T gt  this object obj  T defaultValue  Func lt object  T gt  converter        if  obj IsNull            return defaultValue       return converter     null    T obj   converter obj       With Stevo3000 s approach  I find the calling a bit ugly and tedious  and it will be harder to make a generic function out of it

User · Answer

This is how I handle reading from DataRows      lt summary gt      Handles operations for Enumerations     lt  summary gt  public static class DataRowUserExtensions            lt summary gt          Gets the specified data row           lt  summary gt           lt typeparam name  T  gt  lt  typeparam gt           lt param name  dataRow  gt The data row  lt  param gt           lt param name  key  gt The key  lt  param gt           lt returns gt  lt  returns gt      public static T Get lt T gt  this DataRow dataRow  string key                return  T  ChangeTypeTo lt T gt  dataRow key               private static object ChangeTypeTo lt T gt  this object value                Type underlyingType   typeof  T           if  underlyingType    null              throw new ArgumentNullException  value             if  underlyingType IsGenericType  amp  amp  underlyingType GetGenericTypeDefinition   Equals typeof  Nullable lt  gt                           if  value    null                  return null              var converter   new NullableConverter underlyingType               underlyingType   converter UnderlyingType                        Try changing to Guid           if  underlyingType    typeof  Guid                         try                               return new Guid value ToString                               catch                                return null                                  return Convert ChangeType value  underlyingType             Usage example   if  dbRow Get lt int gt   Type      1        newNode   new TreeViewNode                                           ToolTip   dbRow Get lt string gt   Name                          Text    dbRow Get lt string gt   Name   Length  gt  25   dbRow Get lt string gt   Name   Substring 0  25            dbRow Get lt string gt   Name                           ImageUrl    file gif                         ID   dbRow Get lt string gt   ReportPath                          Value   dbRow Get lt string gt   ReportDescription   Replace                                   NavigateUrl      ReportType     dbRow Get lt string gt   ReportPath                             Props to Monsters Got My  Net for ChageTypeTo code

User · Answer

This is how I handle reading from DataRows      lt summary gt      Handles operations for Enumerations     lt  summary gt  public static class DataRowUserExtensions            lt summary gt          Gets the specified data row           lt  summary gt           lt typeparam name  T  gt  lt  typeparam gt           lt param name  dataRow  gt The data row  lt  param gt           lt param name  key  gt The key  lt  param gt           lt returns gt  lt  returns gt      public static T Get lt T gt  this DataRow dataRow  string key                return  T  ChangeTypeTo lt T gt  dataRow key               private static object ChangeTypeTo lt T gt  this object value                Type underlyingType   typeof  T           if  underlyingType    null              throw new ArgumentNullException  value             if  underlyingType IsGenericType  amp  amp  underlyingType GetGenericTypeDefinition   Equals typeof  Nullable lt  gt                           if  value    null                  return null              var converter   new NullableConverter underlyingType               underlyingType   converter UnderlyingType                        Try changing to Guid           if  underlyingType    typeof  Guid                         try                               return new Guid value ToString                               catch                                return null                                  return Convert ChangeType value  underlyingType             Usage example   if  dbRow Get lt int gt   Type      1        newNode   new TreeViewNode                                           ToolTip   dbRow Get lt string gt   Name                          Text    dbRow Get lt string gt   Name   Length  gt  25   dbRow Get lt string gt   Name   Substring 0  25            dbRow Get lt string gt   Name                           ImageUrl    file gif                         ID   dbRow Get lt string gt   ReportPath                          Value   dbRow Get lt string gt   ReportDescription   Replace                                   NavigateUrl      ReportType     dbRow Get lt string gt   ReportPath                             Props to Monsters Got My  Net for ChageTypeTo code

User · Answer

You should use the method   Convert IsDBNull     Considering it s built-in to the Framework  I would expect this to be the most efficient   I d suggest something along the lines of   int  myValue    Convert IsDBNull row  column      null    int   Convert ToInt32 row  column        And yes  the compiler should cache it for you

User · Answer

I have IsDBNull in a program which reads a lot of data from a database  With IsDBNull it loads data in about 20 seconds  Without IsDBNull  about 1 second   So I think it is better to use   public String TryGetString SqlDataReader sqlReader  int row        String res           try               res   sqlReader GetString row             catch  Exception                   return res

User · Answer

public static class DBH            lt summary gt          Return default T  if supplied with DBNull Value          lt  summary gt           lt typeparam name  T  gt  lt  typeparam gt           lt param name  value  gt  lt  param gt           lt returns gt  lt  returns gt      public static T Get lt T gt  object value                   return value    DBNull Value   default T     T value            use like this  DBH Get lt String gt  itemRow  MyField

User · Answer

Not that I ve done this  but you could get around the double indexer call and still keep your code clean by using a static   extension method   Ie   public static IsDBNull lt T gt  this object value  T default        return  value    DBNull Value            default            T value     public static IsDBNull lt T gt  this object value        return value IsDBNull default T        Then   IDataRecord record     Comes from somewhere  entity StringProperty   record  StringProperty   IsDBNull lt string gt  null   entity Int32Property   record  Int32Property   IsDBNull lt int gt  50    entity NoDefaultString   record  NoDefaultString   IsDBNull lt string gt     entity NoDefaultInt   record  NoDefaultInt   IsDBNull lt int gt       Also has the benefit of keeping the null checking logic in one place  Downside is  of course  that it s an extra method call    Just a thought

User · Answer

I would use the following code in C   VB NET is not as simple    The code assigns the value if it is not null DBNull  otherwise it asigns the default which could be set to the LHS value allowing the compiler to ignore the assign   oSomeObject IntMemeber   oRow  Value   as int     iDefault  oSomeObject StringMember   oRow  Name   as string    sDefault

User · Answer

I always use    if  row  value      DBNull Value    someObject Member   row  value      Found it short and comprehensive

User · Answer

I try to avoid this check as much as possible     Obviously doesn t need to be done for columns that can t hold null   If you re storing in a Nullable value type  int   etc    you can just convert using as int    If you don t need to differentiate between string Empty and null  you can just call  ToString    since DBNull will return string Empty

User · Answer

You should use the method   Convert IsDBNull     Considering it s built-in to the Framework  I would expect this to be the most efficient   I d suggest something along the lines of   int  myValue    Convert IsDBNull row  column      null    int   Convert ToInt32 row  column        And yes  the compiler should cache it for you

User · Answer

I ve done something similar with extension methods  Here s my code   public static class DataExtensions            lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName                return GetColumnValue lt T gt  record  columnName  default T                    lt summary gt          Gets the value           lt  summary gt           lt typeparam name  T  gt The type of the data stored in the record lt  typeparam gt           lt param name  record  gt The record  lt  param gt           lt param name  columnName  gt Name of the column  lt  param gt           lt param name  defaultValue  gt The value to return if the column contains a  lt value gt DBNull Value lt  value gt  value  lt  param gt           lt returns gt  lt  returns gt      public static T GetColumnValue lt T gt  this IDataRecord record  string columnName  T defaultValue                object value   record columnName           if  value    null    value    DBNull Value                        return defaultValue                    else                       return  T value                      To use it  you would do something like  int number   record GetColumnValue lt int gt   Number  0

User · Answer

I try to avoid this check as much as possible     Obviously doesn t need to be done for columns that can t hold null   If you re storing in a Nullable value type  int   etc    you can just convert using as int    If you don t need to differentiate between string Empty and null  you can just call  ToString    since DBNull will return string Empty

User · Answer

Not that I ve done this  but you could get around the double indexer call and still keep your code clean by using a static   extension method   Ie   public static IsDBNull lt T gt  this object value  T default        return  value    DBNull Value            default            T value     public static IsDBNull lt T gt  this object value        return value IsDBNull default T        Then   IDataRecord record     Comes from somewhere  entity StringProperty   record  StringProperty   IsDBNull lt string gt  null   entity Int32Property   record  Int32Property   IsDBNull lt int gt  50    entity NoDefaultString   record  NoDefaultString   IsDBNull lt string gt     entity NoDefaultInt   record  NoDefaultInt   IsDBNull lt int gt       Also has the benefit of keeping the null checking logic in one place  Downside is  of course  that it s an extra method call    Just a thought

User · Answer

The compiler won t optimise away the indexer  i e  if you use row  value   twice   so yes it is slightly quicker to do   object value   row  value      and then use value twice  using  GetType   risks issues if it is null     DBNull Value is actually a singleton  so to add a 4th option - you could perhaps use ReferenceEquals - but in reality  I think you re worrying too much here    I don t think the speed different between  is        etc is going to be the cause of any performance problem you are seeing  Profile your entire code and focus on something that matters    it won t be this

User · Answer

I personally favour this syntax  which uses the explicit IsDbNull method exposed by IDataRecord  and caches the column index to avoid a duplicate string lookup   Expanded for readability  it goes something like   int columnIndex   row GetOrdinal  Foo    string foo     the variable we re assigning based on the column value  if  row IsDBNull columnIndex       foo   String Empty     or whatever   else      foo   row GetString columnIndex       Rewritten to fit on a single line for compactness in DAL code - note that in this example we re assigning int bar   -1 if row  Bar   is null   int i     can be reused for every field  string foo     row IsDBNull i    row GetOrdinal  Foo      null   row GetString i    int bar    row IsDbNull i   row GetOrdinal  Bar      -1   row GetInt32 i      The inline assignment can be confusing if you don t know it s there  but it keeps the entire operation on one line  which I think enhances readability when you re populating properties from multiple columns in one block of code

[.net] Most efficient way to check for DBNull and then assign to a variable?

Examples related to .net

Examples related to dbnull