Unable to cast object of type 'System.DBNull&#

问题:

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?

回答1:

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

回答2:

With a simple generic function you can make this very easy. Just do this:

return ConvertFromDBVal<string>(accountNumber);

using the function:

public static T ConvertFromDBVal<T>(object obj)
{
    if (obj == null || obj == DBNull.Value)
    {
        return default(T); // returns the default value for the type
    }
    else
    {
        return (T)obj;
    }
}

回答3:

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.

回答4:

You can use C#\'s null coalescing operator

return accountNumber ?? string.Empty;

回答5:

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.

回答6:

This is the generic method that I use to convert any object that might be a DBNull.Value:

public static T ConvertDBNull<T>(object value, Func<object, T> 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);

回答7:

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

回答8:

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

回答9:

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

回答10:

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

回答11:

Convert it Like

string s = System.DBNull.value.ToString();