SqlDataReader检查空值的最佳方法-SqlDataReader.IsDBNull与DBNull.Value
本文关键字:IsDBNull DBNull Value -SqlDataReader 方法 检查 空值 最佳 SqlDataReader | 更新日期: 2023-09-27 18:29:49
我想从数据库中检索十进制值,我想知道哪种方法是检查空值的推荐方法。
我在MSDN-DBNull.Value字段上看到这种检查很少使用。
因此,reader.IsDBNull
是检查null的最佳/最有效的方法吗?
我创建了两个示例方法:
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];
}
大多数情况下,字段将为空。
提前感谢!
我不会太纠结于哪种方法更好,因为两者都可以工作,而且我以前在代码中都使用过。
例如,下面是我从一个旧项目中挖掘出的一个实用函数:
/// <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);
}
}
用法:
yourSqlReaderObject.GetValue<int?>("SOME_ID_COLUMN");
yourSqlReaderObject.GetValue<string>("SOME_VALUE_COLUMN");
如果要检查null并处理它(而不是检查null并警告程序它为null),可以将as
运算符与null合并运算符??
一起使用。所以在我的程序中
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 });
}
下面是@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);
}
}
甚至:
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;
}
}
对于可为null或不可为null的类型,直接转换似乎都很好。