Unable to cast object of type 'System.DBNull' to type 'System.String`

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?

12 Answers

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

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

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.

You can use C#'s null coalescing operator

return accountNumber ?? string.Empty; 

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

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.

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

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

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

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

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

Convert it Like

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