C# 如何使用具有空值的数据读取器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1362588/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use datareader with null values
提问by Anthony
Say I have this class:
说我有这门课：
class myclass
{
public int Field1{ get; set; }
public int? Field2 { get; set; } //Note Field2 is nullable
}
I'm trying to populate a generic list with data coming from a database. As GetSqlInt32 implements INullable I would have thought that the below code would work. It doesn't. It generates an error if Field2 is null.
我正在尝试使用来自数据库的数据填充通用列表。当 GetSqlInt32 实现 INullable 时，我会认为下面的代码会起作用。它没有。如果 Field2 为空，则会生成错误。
List<myclass> mylist=new List<myclass>();
int Field1_Ordinal = rdr.GetOrdinal("Field1");
int Field2_Ordinal = rdr.GetOrdinal("Field2");
SqlDataReader rdr = cmd.ExecuteReader(); //Execute a stored procedure to retrieve data from the database
while (rdr.Read())
{
mylist.Add(new myclass
{
Field1 = rdr.GetSqlInt32(Field1_Ordinal).Value,
Field2 = rdr.GetSqlInt32(Field2_Ordinal).Value //Error if field2 is null
});
}
Any ideas why it doesn't work?
任何想法为什么它不起作用？
采纳答案by Jon Skeet
It seems to me that you need a conversion like this (using an extension method for convenience):
在我看来，您需要这样的转换（为方便起见，使用扩展方法）：
public static int? ToNullableInt32(this SqlInt32 value)
{
return value.IsNull ? (int?) null : value.Value;
}
Then:
然后：
Field2 = rdr.GetSqlInt32(Field2_Ordinal).ToNullableInt32()
(Comment on other answers: there's no need to bring DbNullinto this, as SqlInt32can already represent null values. You just need to detect that before using Value.)
（评论其他答案：没有必要DbNull引入这个，因为SqlInt32它已经可以代表空值。你只需要在使用之前检测到它Value。）
回答by MatthewMartin
DbNull.Value != null
DbNull.Value != null
So you need either a ? : expression or a if block to convert database nulls to c# nulls and vica versa.
所以你需要一个 ? : 表达式或 if 块将数据库空值转换为 c# 空值，反之亦然。
回答by Rune Grimstad
I thinkit is beacuse the value returned is DBNull.Value, and not null.
我认为这是因为返回的值是DBNull.Value，而不是null。
Instead you can use the IsDbNull() method to check if the field is null before reading it.
相反，您可以使用 IsDbNull() 方法在读取该字段之前检查该字段是否为空。
回答by Charles Bretana
You have to use a special method on the reader to detect when the value is null
您必须在阅读器上使用特殊方法来检测值何时为空
mylist.Add(new myclass
{
Field1 = rdr.IsDbNull(Field1_Ordinal)? 0:
rdr.GetSqlInt32(Field1_Ordinal).Value,
Field2 = rdr.IsDbNull(Field2_Ordinal)? 0: // whatever default value you wish...
rdr.GetSqlInt32(Field2_Ordinal).Value // No error now
});
回答by ZXX
Here's a pain reduction variation on the theme. If someone knows how to merge Val and Ref into one template function fell free to post. You will have to state the type explicitly (C# compiled can't be bothered :-) but this:
这是该主题的减轻疼痛的变体。如果有人知道如何将 Val 和 Ref 合并到一个模板函数中，可以随意发布。您将必须明确说明类型（C# 编译不能被打扰:-) 但这：
var dd = r.Val<DateTime>(ords[4]);
var ii = r.Def<int>(ords[0]);
int nn = r.Def<int>(ords[0]);
is still maked my fingers happy :-)
仍然让我的手指高兴:-)
public static T Def<T>(this SqlDataReader r, int ord)
{
var t = r.GetSqlValue(ord);
if (t == DBNull.Value) return default(T);
return ((INullable)t).IsNull ? default(T) : (T)t;
}
public static T? Val<T>(this SqlDataReader r, int ord) where T:struct
{
var t = r.GetSqlValue(ord);
if (t == DBNull.Value) return null;
return ((INullable)t).IsNull ? (T?)null : (T)t;
}
public static T Ref<T>(this SqlDataReader r, int ord) where T : class
{
var t = r.GetSqlValue(ord);
if (t == DBNull.Value) return null;
return ((INullable)t).IsNull ? null : (T)t;
}
回答by TcTom
I'm trying to export an Access database with 39 fields - many with NULL values. I couldn't get the extension method working so I wrote the following function:
我正在尝试导出具有 39 个字段的 Access 数据库 - 许多字段为 NULL 值。我无法使扩展方法工作，所以我编写了以下函数：
private string ChkDbStr(object inObj)
{
if (inObj == null)
{ return ""; }
else
{ return inObj.ToString(); }
}
As I read each field that might contain a NULL, I code the field read as: ChkDbStr(DbReader.GetValue(1))
当我读取每个可能包含 NULL 的字段时，我将读取的字段编码为： ChkDbStr(DbReader.GetValue(1))
回答by Ignacio Soler Garcia
Check out this solution which wasn't written by me:
看看这个不是我写的解决方案：
employee.FirstName = sqlreader[indexFirstName] as string;
employee.Age = sqlreader[indexAge] as int? ?? default(int);
It was originally proposed here:
它最初是在这里提出的：
