Unable to cast object of type 'System.DBNull' to type 'System.String`
C#asp.netDatabaseNullC# Problem Overview
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?
C# Solutions
Solution 1 - C#
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;
}
}
Solution 2 - C#
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()
Solution 3 - C#
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.
Solution 4 - C#
You can use C#'s null coalescing operator
return accountNumber ?? string.Empty;
Solution 5 - C#
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);
Solution 6 - C#
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.
Solution 7 - C#
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 :)
Solution 8 - C#
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
Solution 9 - C#
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
Solution 10 - C#
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 :)
Solution 11 - C#
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();
}
Solution 12 - C#
Convert it Like
string s = System.DBNull.value.ToString();