Unable to cast object of type System DBNull to type System String

Question

I got the above error in my app  Here is the original code  public string GetCustomerNumber Guid id         string accountNumber               string DBSqlHelperFactory ExecuteScalar connectionStringSplendidmyApp                             CommandType StoredProcedure                              GetCustomerNumber                              new SqlParameter   id   id         return accountNumber ToString         I replaced with  public string GetCustomerNumber Guid id       object accountNumber                object DBSqlHelperFactory ExecuteScalar connectionStringSplendidCRM                                   CommandType StoredProcedure                                    spx GetCustomerNumber                                    new SqlParameter   id   id        if  accountNumber is System DBNull               return string Empty            else              return accountNumber ToString              Is there a better way around this

User · Answer

I use an extension to eliminate this problem for me, which may or may not be what you are after.

It goes like this:

public static class Extensions
{

    public String TrimString(this object item)
    {
        return String.Format("{0}", item).Trim();
    }

}

Note:

This extension does not return null values! If the item is null or DBNull.Value, it will return an empty String.

Usage:

public string GetCustomerNumber(Guid id)
{
    var obj = 
        DBSqlHelperFactory.ExecuteScalar(
            connectionStringSplendidmyApp, 
            CommandType.StoredProcedure, 
            "GetCustomerNumber", 
            new SqlParameter("@id", id)
        );
    return obj.TrimString();
}

User · Answer

There is another way to workaround this issue  How about modify your store procedure  by using ISNULL your field      sql function   you can return empty string if the return value is null    Then you have your clean code as original version

User · Answer

This is the generic method that I use to convert any object that might be a DBNull Value   public static T ConvertDBNull lt T gt  object value  Func lt object  T gt  conversionFunction        return conversionFunction value    DBNull Value   null   value       usage   var result   command ExecuteScalar     return result ConvertDBNull Convert ToInt32     shorter   return command      ExecuteScalar        ConvertDBNull Convert ToInt32

User · Answer

A shorter form can be used   return  accountNumber    DBNull Value    string Empty   accountNumber ToString     EDIT  Haven t paid attention to ExecuteScalar  It does really return null if the field is absent in the return result  So use instead   return  accountNumber    null    string Empty   accountNumber ToString

User · Answer

With a simple generic function you can make this very easy  Just do this   return ConvertFromDBVal lt string gt  accountNumber     using the function   public static T ConvertFromDBVal lt T gt  object obj        if  obj    null    obj    DBNull Value                return default T      returns the default value for the type           else               return  T obj

User · Answer

Convert it Like   string s   System DBNull value ToString

User · Answer

ExecuteScalar will return   null if there is no result set otherwise the first column of the first row of the resultset  which may be DBNull    If you know that the first column of the resultset is a string  then to cover all bases you need to check for both null and DBNull   Something like   object accountNumber      ExecuteScalar       return  accountNumber    null    String Empty   accountNumber ToString      The above code relies on the fact that DBNull ToString returns an empty string    If accountNumber was another type  say integer   then you d need to be more explicit   object accountNumber      ExecuteScalar       return  accountNumber    null    Convert IsDBNull accountNumber                   int  accountNumber   0    If you know for sure that your resultset will always have at least one row  e g  SELECT COUNT         then you can skip the check for null   In your case the error message  Unable to cast object of type    System DBNull    to type    System String   indicates that the first column of your result set is a DBNUll value   This is from the cast to string on the first line   string accountNumber    string      ExecuteScalar         Marc s s comment that you don t need to check for DBNull Value is wrong

User · Answer

You can use C  s null coalescing operator  return accountNumber    string Empty

User · Answer

Since I got an instance which isn t null and if I compared to DBNULL I got Operator      cannot be applied to operands of type  string  and  system dbnull  exeption  and if I tried to change to compare to NULL  it simply didn t work   since DBNull is an object  even that s the accepted answer   I decided to simply use the  is  keyword   So the result is very readable   data    item is DBNull    String Empty   item

User · Answer

String Concat transforms DBNull and null values to an empty string   public string GetCustomerNumber Guid id       object accountNumber                object DBSqlHelperFactory ExecuteScalar connectionStringSplendidCRM                                   CommandType StoredProcedure                                    spx GetCustomerNumber                                    new SqlParameter   id   id         return String Concat accountNumber         However  I think you lose something on code understandability

User · Answer

I suppose you can do it like this   string accountNumber   DBSqlHelperFactory ExecuteScalar      as string    If accountNumber is null it means it was DBNull not string

[c#] Unable to cast object of type 'System.DBNull' to type 'System.String`

Note:

Usage:

Examples related to c#

Examples related to asp.net

Examples related to database

Examples related to null