Web API读取SQL Server含NULL值数据时DBNull转字符串报错如何解决

报错原因 #

SQL Server中的NULL值在.NET运行时中对应System.DBNull类型，和C#本身的null不是同一概念，直接将DBNull类型对象强制转换为string会触发类型转换异常。
你遇到的报错信息：

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

修复方法 #

有两种通用处理方案，可根据业务需求选择：

方案1：SQL NULL转换为空字符串 #

直接调用字段对象的ToString()方法即可，DBNull的ToString()实现默认返回空字符串，无需额外判空：

Customer ExampleCustomer = new()
{
    CustomerID = AGetCommandDataReader["CustomerID"].ToString(),
    CompanyName = AGetCommandDataReader["CompanyName"].ToString(),
    ContactName = AGetCommandDataReader["ContactName"].ToString(),
    ContactTitle = AGetCommandDataReader["ContactTitle"].ToString(),
    Address = AGetCommandDataReader["Address"].ToString(),
    City = AGetCommandDataReader["City"].ToString(),
    Region = AGetCommandDataReader["Region"].ToString(),
    PostalCode = AGetCommandDataReader["PostalCode"].ToString(),
    Country = AGetCommandDataReader["Country"].ToString(),
    Phone = AGetCommandDataReader["Phone"].ToString(),
    Fax = AGetCommandDataReader["Fax"].ToString()
};

方案2：SQL NULL转换为C# null #

使用as运算符做类型转换，as转换失败时会直接返回null，符合将数据库NULL映射为C# NULL的需求：

Customer ExampleCustomer = new()
{
    CustomerID = AGetCommandDataReader["CustomerID"] as string,
    CompanyName = AGetCommandDataReader["CompanyName"] as string,
    ContactName = AGetCommandDataReader["ContactName"] as string,
    ContactTitle = AGetCommandDataReader["ContactTitle"] as string,
    Address = AGetCommandDataReader["Address"] as string,
    City = AGetCommandDataReader["City"] as string,
    Region = AGetCommandDataReader["Region"] as string,
    PostalCode = AGetCommandDataReader["PostalCode"] as string,
    Country = AGetCommandDataReader["Country"] as string,
    Phone = AGetCommandDataReader["Phone"] as string,
    Fax = AGetCommandDataReader["Fax"] as string
};

额外优化建议 #

建议使用using语句管理数据库连接、命令、DataReader对象，无需手动调用Close()方法，即使代码执行出现异常也会自动释放资源，避免资源泄漏：

List<Customer> ExampleCustomers = new();
using SqlCommand AGetCommand = new()
{
    Connection = DataSource,
    CommandType = CommandType.StoredProcedure,
    CommandText = "GetCustomers"
};
using SqlDataReader AGetCommandDataReader = AGetCommand.ExecuteReader();
if (AGetCommandDataReader.HasRows)
{
    while (AGetCommandDataReader.Read())
    {
        // 此处使用上述任意一种修复后的赋值逻辑即可
        Customer ExampleCustomer = new()
        {
            CustomerID = AGetCommandDataReader["CustomerID"] as string,
            // 剩余属性赋值省略
        };
        ExampleCustomers.Add(ExampleCustomer);
    }
}
return ExampleCustomers;

内容的提问来源于stack exchange，提问作者dhwng7