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?

Polypeptide

you should really look into @rein's answer, will save you lots of time in the long run – Jylland

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

Ard

That won't work - the "accountNumber" is not a database value but a regular old Plain Old .NET "object" instance - you need to check against normal "null" value. The DBNull.Value would work for a SqlDataReader or a SqlParameter - but not for this object here. – Alcoholicity

You're right, I started to optimize the condition check part, haven't looked at the line before. Mea culpa. – Ard

There is typo in your post that I can't really edit because the edit requires 6 characters to be changed. Can someone change accountNumber.TosString() to accountNumber.ToString() – Nevsa

@Alcoholicity Depending on db/query layout, you need to check against either of them or even both. If the WHERE does not match any row, you'll get a null, if the selected row has NULL in that column, the return value is System.DBNull. – Abramson

In the first case @Abramson mentions -not matching any row- you can rely on Convert.ToString or any other Convert method if you are fine with the value they return when converting from null: empty string for strings, 0 for numeric values, false for boolean, MinValue for DateTime... msdn.microsoft.com/en-us/library/vstudio/… – Kuching

This will still from an Exception in a try loop. I'd recommend scrapping this idea and following rein's solution immediately below. – Ritualism

Answer 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;
    }
}

Chacha

Yes, a function like this is the only practical solution. Any kind of in-line logic will fail after you have copied and pasted it a thousand times. :-) – Yeoman

this will not work if you try converting 1 to bool (Convert.ToBoolean(1) works fine tho) – Jylland

@roman: so then we would want to have an additional check (prior to checking for null) that checks for a boolean type... – Querulous

If you want or need to use Convert functions, then this is not working. There are several scenarios where you might prefer convert to an explicit cast. @romanm noted one of them. Another one is when you work with decimals and care about the different rounding mechanisms that Convert.ToInt32 and (int) use. The former rounds to the nearest even value, while the explicit cast just truncates the value: #1609301 If possible, I would eliminate NULLs from the mix, using T-SQL ISNULL function – Kuching

@Kuching This function is supposed to act like an implicit cast from a SQL data type to a C#/.NET data type. If you have needs for an explicit cast, don't use this function - do it explicitly instead. – Chacha

OMG!!! Thaaaank you... I struggled for so long to find a solution to the comparing values from a database which was either DBNull.Value or datetime(2) which looped through results, sometimes being DBNull and sometimes being of the correct date. Can't compare date to see if it's DBNull, can't convert null to datetime, etc., etc. This saved my life... – Crean

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

Remington

my resultset will not always return a row. – Polypeptide

Answer 4

You can use C#'s null coalescing operator

return accountNumber ?? string.Empty;

Abomination

-1: That won't compile: the method returns a string and accountNumber is an object. – Remington

return Cmd.ExecuteScalar().ToString() ?? String.Empty; – Naucratis

return Cmd.ExecuteScalar().ToString() did the job for me – Colver

Answer 5

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

Verruca

Answer 6

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.

Splat

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

Trilobite

Or return (accountNumber as string) ?? string.Empty; , with accountNumber still being an object. If you prefer to keep your database call on its own line. – Entail

Answer 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

Epiboly

What happens if you write return "" + accountNumber;? – Quinquennium

Answer 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

Illegible

Answer 10

A more concise approach using more recent C# syntax and also accounting for nullable types:

private static T? FromDbNull<T>(object? obj) => 
    obj == null || obj == DBNull.Value ? default : (T)obj;

Can be used with a data reader as follows:

        while (reader.Read())
        {
            var newObject = new SomeObject(
                FromDbNull<string?>(reader["nullable_field_1"]),
                FromDbNull<string?>(reader["nullable_field_2"]),
                FromDbNull<string?>(reader["nullable_field_3"]), 
                FromDbNull<double?>(reader["nullable_field_4"])
            );
            
            response.Add(newObject);
        }

Shih

Answer 11

Since I just had a similar error myself and this is the first hit on google. If you do not have your properties nullable configured to match your database, the same error can occur.

If you just need a quick & ugly fix, you can add:

#nullable disable

at the start of your model class.

Vasily

Answer 12

based on answer from @rein

public static class DbDataReaderExtensions
{
    public static TObjProp Get<TObj, TObjProp>(
        this DbDataReader reader,
        Expression<Func<TObj, TObjProp>> expression)
    {
        MemberExpression member = expression.Body as MemberExpression;
        string propertyName = member.Member.Name;

        //PropertyInfo propInfo = member.Member as PropertyInfo;

        var recordOrdinal = reader.GetOrdinal(propertyName);
        var obj = reader.GetValue(recordOrdinal);

        if (obj == null || obj == DBNull.Value)
        {
            return default(TObjProp);
        }
        else
        {
            return (TObjProp)obj;
        }
    }
}

Given:

public class MyClass
{
    public bool? IsCheckPassed { get; set; }
}

Use as:

var test = reader.Get<MyClass, bool?>(o => o.IsCheckPassed);

or, if you hardcode class type in exception method:

var test = reader.Get(o => o.IsCheckPassed);

p.s. I haven't figured yet how to make generics implicit without sacrificing code length.. fee free to comment and suggest improvements

Full example:

public async Task<MyClass> Test(string connectionString) {
    var result = new MyClass();
    
    await using var con = new SQLiteConnection(connectionString);
    con.Open();

    await using var cmd = con.CreateCommand();
    cmd.CommandText = @$"SELECT Id, IsCheckPassed FROM mytable";
    
    var reader = await cmd.ExecuteReaderAsync();
    while (reader.Read()) {
        // old, not working! Throws exception!
        //bool? isCheckPassed1 = reader.GetBoolean(reader.GetOrdinal("IsCheckPassed"));
        
        // old, working, but too long (also if you have like 20 properties then all the more reasons to refactor..)
        bool? isCheckPassed2 = null;
        bool? isCheckPassed2Temp = reader.GetValue(reader.GetOrdinal("IsCheckPassed"));
        if (isCheckPassed2Temp != null && isCheckPassed2Temp != DBNull.Value)
            isCheckPassed2 = (bool?)isCheckPassed2Temp;
        
        // new
        var isCheckPassed3 = reader.Get<MyClass, bool?>(o => o.IsCheckPassed);
        // repeat for 20 more properties :)
        
        result.IsCheckPassed = isCheckPassed3;
    }
    
    return result;
}

Solution will work for as long as table column names match property names of the class. And might not be production-grade performance wise, so use or modify at your own risk :)

Rendering

Answer 13

Convert it Like

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

Meador