C# SqlDataReader 检查空值的最佳方法 -sqlDataReader.IsDBNull 与 DBNull.Value

SqlDataReader Best way to check for null values -sqlDataReader.IsDBNull vs DBNull.Value

提问by diver

I want to retrieve decimal values from the database and I would like to know which is the recommended way to check for null values.

我想从数据库中检索十进制值，我想知道哪种方法是检查空值的推荐方法。

I have seen on MSDN - DBNull.Value Field that this check is rarely used.

我在MSDN - DBNull.Value Field 上看到这个检查很少使用。

Thus, is the reader.IsDBNullthe best/most efficient way to check for nulls?

因此，reader.IsDBNull检查空值的最佳/最有效方法是什么？

I have created 2 sample methods:

我创建了 2 个示例方法：

public static decimal? GetNullableDecimal(SqlDataReader reader, string fieldName)
{
    if (reader[fieldName] == DBNull.Value)
    {
        return null;
    }
    return (decimal)reader[fieldName];
}

public static decimal? GetNullableDecimal_2(SqlDataReader reader, string fieldName)
{
    if (reader.IsDBNull(reader[fieldName]))
    {
         return null;
    }
    return (decimal)reader[fieldName];
}

Most of the time the fields are going to be null.

大多数情况下，这些字段将为空。

Thanks in advance!

提前致谢！

采纳答案by Karl Anderson

I would not get too caught up in the which method is better, because both work and I have used both in code before.

我不会太纠结于哪种方法更好，因为这两种方法都有效，而且我之前都在代码中使用过。

For instance, here is a utility function I dug up from one of my old projects:

例如，这是我从我的一个旧项目中挖掘出来的一个实用函数：

/// <summary>
/// Helper class for SqlDataReader, which allows for the calling code to retrieve a value in a generic fashion.
/// </summary>
public static class SqlReaderHelper
{
    private static bool IsNullableType(Type theValueType)
    {
        return (theValueType.IsGenericType && theValueType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)));
    }

    /// <summary>
    /// Returns the value, of type T, from the SqlDataReader, accounting for both generic and non-generic types.
    /// </summary>
    /// <typeparam name="T">T, type applied</typeparam>
    /// <param name="theReader">The SqlDataReader object that queried the database</param>
    /// <param name="theColumnName">The column of data to retrieve a value from</param>
    /// <returns>T, type applied; default value of type if database value is null</returns>
    public static T GetValue<T>(this SqlDataReader theReader, string theColumnName)
    {
        // Read the value out of the reader by string (column name); returns object
        object theValue = theReader[theColumnName];

        // Cast to the generic type applied to this method (i.e. int?)
        Type theValueType = typeof(T);

        // Check for null value from the database
        if (DBNull.Value != theValue)
        {
            // We have a null, do we have a nullable type for T?
            if (!IsNullableType(theValueType))
            {
                // No, this is not a nullable type so just change the value's type from object to T
                return (T)Convert.ChangeType(theValue, theValueType);
            }
            else
            {
                // Yes, this is a nullable type so change the value's type from object to the underlying type of T
                NullableConverter theNullableConverter = new NullableConverter(theValueType);

                return (T)Convert.ChangeType(theValue, theNullableConverter.UnderlyingType);
            }
        }

        // The value was null in the database, so return the default value for T; this will vary based on what T is (i.e. int has a default of 0)
        return default(T);
    }
}

Usage:

用法：

yourSqlReaderObject.GetValue<int?>("SOME_ID_COLUMN");
yourSqlReaderObject.GetValue<string>("SOME_VALUE_COLUMN");

回答by VictorySaber

If you want to check for null and handle it (as opposed to checking for null and alerting the program that it was null) you can use the asoperator with the null-coalescing operator ??. So in my program

如果您想检查 null 并处理它（而不是检查 null 并提醒程序它为 null），您可以使用as带有 null-coalescing operator 的运算符??。所以在我的程序中

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
    response.Employees.Add(new Employee() { Id = dr["id"] as int? ?? default(int), ImageUrl = dr["Photo"] as string, JobTitle = dr["JobTitle"] as string });
}

回答by Mark

Here's a simpler version of @Karl Anderson's answer:

这是@Karl Anderson 答案的更简单版本：

public static class DbHelper
{
    public static T GetValue<T>(this SqlDataReader sqlDataReader, string columnName)
    {
        var value = sqlDataReader[columnName];

        if (value != DBNull.Value)
        {
            return (T)value;
        }

        return default(T);
    }
}

Or even:

甚至：

public static class DbHelper
{
    public static T GetValue<T>(this SqlDataReader sqlDataReader, string columnName)
    {
        var value = sqlDataReader[columnName];

        return value == DBNull.Value ? default(T) : (T) value;
    }
}

Direct casting seems to work just fine for either nullable or non-nullable types.

对于可为空或不可为空的类型，直接强制转换似乎工作得很好。