I acquired the above mentioned error during my application. This is actually 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 changed 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();
    }

It is possible to better way for this?

Having a simple generic function you may make this super easy. Simply do this:

return ConvertFromDBVal<string>(accountNumber);

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

A shorter form may be used:

return (accountNumber == DBNull.Value) ? string.Empty : accountNumber.TosString ()

EDIT: Haven't compensated focus on ExecuteScalar. It will really return null when the area is absent in exchange result.

So use rather:

return (accountNumber == null) ? string.Empty : accountNumber.TosString ()

ExecuteScalar will return

  • null if there's no result set
  • otherwise the very first column from the first row from the resultset, which might be DBNull.

Knowing the first column from the resultset is really a string, then to pay for all bases you have to look for both null and DBNull. Something similar to:

object accountNumber = ...ExecuteScalar(...);
return (accountNumber == null) ? String.Empty : accountNumber.ToString();

The above mentioned code depends on the truth that DBNull.ToString returns a clear string.

If accountNumber was another type (say integer), then you'd have to be more explicit:

object accountNumber = ...ExecuteScalar(...);
return (accountNumber == null || Convert.IsDBNull(accountNumber) ?     
         (int) accountNumber : 0;

Knowing without a doubt that the resultset will invariably have a minumum of one row (e.g. Choose COUNT(*)...), you'll be able to skip the look for null.

Inside your situation the mistake message "Not able to cast object of type ‘System.DBNull’ to type ‘System.String`" signifies the first column of the result set is really a DBNUll value. This really is in the cast to string around the first line:

string accountNumber = (string) ... ExecuteScalar(...);

Marc_s's comment that you don't have to look for DBNull.Value is wrong.

I guess it can be done such as this:

string accountNumber = DBSqlHelperFactory.ExecuteScalar(...) as string;

If accountNumber is null this means it had been DBNull not string :)

You should use C#'s null coalescing operator

return accountNumber ?? string.Empty;

There's a different way to workaround this problem. What about modify your store procedure? by utilizing ISNULL(your area, "") sql function , you are able to return empty string when the return value is null.

Then you've your clean code as original version.