C# 无法将“System.DBNull”类型的对象转换为“System.String”类型

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

提问by Saif Khan

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?

有没有更好的方法来解决这个问题？

采纳答案by User

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:

编辑：还没有关注 ExecuteScalar。如果该字段在返回结果中不存在，它确实会返回 null。所以改用：

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

回答by ppiotrowicz

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

如果 accountNumber 为 null，则表示它是 DBNull 而不是字符串 :)

回答by Nathan Koop

You can use C#'s null coalescing operator

您可以使用 C# 的空合并运算符

return accountNumber ?? string.Empty;

回答by rein

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

回答by Joe

ExecuteScalar will return

ExecuteScalar 将返回

• null if there is no result set
• otherwise the first column of the first row of the resultset, which may be DBNull.

• 如果没有结果集，则为 null
• 否则为结果集第一行的第一列，可能为 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:

如果您知道结果集的第一列是一个字符串，那么要覆盖所有基数，您需要检查 null 和 DBNull。就像是：

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

The above code relies on the fact that DBNull.ToString returns an empty string.

上面的代码依赖于 DBNull.ToString 返回一个空字符串的事实。

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

如果 accountNumber 是另一种类型（比如整数），那么您需要更加明确：

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.

如果您确定您的结果集总是至少有一行（例如 SELECT COUNT(*)...），那么您可以跳过对 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:

在您的情况下，错误消息“无法将类型为 'System.DBNull' 的对象强制转换为类型为 'System.String`”表示结果集的第一列是 DBNUll 值。这是从第一行的强制转换到字符串：

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

Marc_s's comment that you don't need to check for DBNull.Value is wrong.

Marc_s 关于您不需要检查 DBNull.Value 的评论是错误的。

回答by Andrea Parodi

String.Concat transforms DBNull and null values to an empty string.

String.Concat 将 DBNull 和 null 值转换为空字符串。

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

但是，我认为您在代码可理解性方面失去了一些东西

回答by Russel Yang

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.

还有另一种方法可以解决此问题。如何修改你的存储过程？通过使用 ISNULL(your field, "") sql 函数，如果返回值为空，则可以返回空字符串。

Then you have your clean code as original version.

然后您将干净的代码作为原始版本。

回答by Heras

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

这是我用来转换任何可能是 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);

回答by jp2code

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 notreturn nullvalues! If the item is nullor DBNull.Value, it will return an empty String.

此扩展不返回null值！如果项目是null或DBNull.Value，它将返回一个空字符串。

Usage:

用法：

public string GetCustomerNumber(Guid id)
{
    var obj = 
        DBSqlHelperFactory.ExecuteScalar(
            connectionStringSplendidmyApp, 
            CommandType.StoredProcedure, 
            "GetCustomerNumber", 
            new SqlParameter("@id", id)
        );
    return obj.TrimString();
}

回答by Sudhakar Rao

Convert it Like

转换它喜欢

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