C# 带有 SQLDataReader 的可空日期时间
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17489960/
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
Nullable DateTime with SQLDataReader
提问by Jay
I almost hate to ask this question seems like it has been asked a million times before but even with me researching the other question I still cant seem to figure this out in my case.
我几乎不想问这个问题，好像它之前已经被问过一百万次了，但即使我研究了另一个问题，我似乎仍然无法在我的情况下弄清楚这一点。
I read that DateTime is a nullable type and I tried a few of the examples but I am trying to figure out if it is NULL in the database my SQLDATAREADER is failing.
我读到 DateTime 是一个可以为空的类型，我尝试了一些示例，但我试图弄清楚它在我的 SQLDATAREADER 失败的数据库中是否为 NULL。
Error
错误
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot 'be called on Null values.'
System.Data.SqlTypes.SqlNullValueException：数据为空。不能“对 Null 值调用此方法或属性”。
DetailsClass
详情类
private DateTime? startingDate;
public DateTime? StartingDate
{
get{ return startingDate; }
set{ startingDate = value; }
}
// constructor
Public DetailsClass(DateTime? startingDate)
{
this.startingDate = startingDate;
}
DBClass
数据库类
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = con.CreateCommand())
{
List<DetailsClass> details = new List<DetailsClass>();
DetailsClass dtl;
try
{
con.Open();
cmd.CommandText = "Stored Procedure Name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MyParameter", myparameter);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dtl = new DetailsClass((
reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))),
reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
reader.GetDateTime(reader.GetOrdinal("STARTINGDATE")));
details.Add(dtl);
}
reader.Close();
return details;
}
}
采纳答案by Bob Vale
Here is a helper method to get the value out from the reader
这是从读取器中获取值的辅助方法
public static class ReaderExtensions {
public static DateTime? GetNullableDateTime(this SqlDataReader reader, string name){
var col = reader.GetOrdinal(name);
return reader.IsDBNull(col) ?
(DateTime?)null :
(DateTime?)reader.GetDateTime(col);
}
}
Update on how to use in response to comment
更新如何使用以回应评论
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd = con.CreateCommand())
{
List<DetailsClass> details = new List<DetailsClass>();
DetailsClass dtl;
try
{
con.Open();
cmd.CommandText = "Stored Procedure Name";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@MyParameter", myparameter);
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
dtl = new DetailsClass((
reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))),
reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
reader.GetNullableDateTime("STARTINGDATE"));
details.Add(dtl);
}
reader.Close();
return details;
}
}
Also note you are using reader.IsDBNull(1)and then reader.GetOrdinal. Probably should be reader.IsDBNull(reader.GetOrdinal("EMAIL"))
另请注意，您正在使用reader.IsDBNull(1)然后reader.GetOrdinal. 大概应该是reader.IsDBNull(reader.GetOrdinal("EMAIL"))
回答by Marco
replace
代替
DateTime startingDate;
with
和
DateTime? startingDate;
The question mark marks it as a nullable value and your reader should be able to set startingdate to null instead of throwing an exception.
问号将其标记为可空值，您的读者应该能够将开始日期设置为空而不是抛出异常。
you could also check for null values while your reader is working and replace null values with empty strings
您还可以在阅读器工作时检查空值，并用空字符串替换空值
while(reader.read())
{
//column is an int value of your column. I.e: if the column ist the 8th column, set column to 7 (0-based)
StartingDate = (reader.IsDBNull(column)) ? null : reader.GetOrdinal("STARTINGDATE"));
//instead of null you could also return a specific date like 1.1.1900 or String.Empty
}
回答by Karl Anderson
Try this:
尝试这个：
Make the startingDatevariable nullable, like this:
制作startingDate变量nullable，像这样：
DateTime? startingDate;
Now, when retrieving the value from the SqlDataReaderobject you need to use the IsDbNullmethod, which will determine if the value is NULLcoming back from the database, like this:
现在，当从SqlDataReader对象中检索值时，您需要使用该IsDbNull方法，该方法将确定该值是否NULL从数据库中返回，如下所示：
if !reader.IsDBNull(reader.GetOrdinal("STARTINGDATE"))
{
startingDate = reader.GetDateTime(reader.GetOrdinal("STARTINGDATE"));
}
else
{
startingDate = null;
}
Note: I am not sure where you are assigning startingDateto the value read from the database, because it is not shown in the posted code. That is why I assigned it directly in my example, but you may need to adjust the placement of the example logic.
注意：我不确定您将在哪里分配startingDate给从数据库读取的值，因为它没有显示在发布的代码中。这就是我在示例中直接分配的原因，但您可能需要调整示例逻辑的位置。
回答by Abhitalks
Can you please tell us exactly which line is throwing the error while debugging. It would be easier.
你能告诉我们在调试时到底哪一行抛出错误吗？会更容易。
if (! reader.IsDBNull(reader.GetOrdinal("STARTINGDATE"))) {
obj.startingDate = reader.GetDateTime(reader.GetOrdinal("STARTINGDATE"));
}
no need to explicitly assign null if it is DBNull, because it is a nullable type so by default it will contain null.
如果它是 DBNull，则无需显式分配 null，因为它是可空类型，因此默认情况下它将包含 null。
Ok, as per your updated code (note the comments):
好的，根据您更新的代码（注意注释）：
while (reader.Read()) {
dtl = new DetailsClass((reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"))),
// here you are checking null for email
reader.IsDBNull(1) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
// here you are not checking null for startingdate ?
reader.GetDateTime(reader.GetOrdinal("STARTINGDATE")));
details.Add(dtl);
}
Lets try it in more verbose way:
让我们以更详细的方式尝试一下：
while (reader.Read()) {
dtl = new DetailsClass();
dtl.membershipgen = reader.IsDBNull(reader.GetOrdinal("MEMBERSHIPGEN")) ? null : reader.GetInt32(reader.GetOrdinal("MEMBERSHIPGEN"));
dtl.email = reader.IsDBNull(reader.GetOrdinal("EMAIL")) ? null : reader.GetString(reader.GetOrdinal("EMAIL")),
dtl.startingdate = reader.IsDBNull(reader.GetOrdinal("STARTINGDATE")) ? null : reader.GetDateTime(reader.GetOrdinal("STARTINGDATE")));
details.Add(dtl);
}
回答by user3882848
I know this question has been answered, but further simplifying the code to deal with other nullable variable type. The answer above only limits to one specific variable type. The nice thing about this is that it also allows you to include a default value rather than setting it as null.
我知道这个问题已经得到解答，但进一步简化了处理其他可为空变量类型的代码。上面的答案只限于一种特定的变量类型。这样做的好处是它还允许您包含默认值而不是将其设置为 null。
public static T GetDataType<T>( this SqlDataReader r, string name, object def = null )
{
var col = r.GetOrdinal(name);
return r.IsDBNull(col) ? (T)def : (T)r[name];
}
then in the code, you can use such as
然后在代码中，您可以使用例如
...
while(reader.Read())
{
data1Bool = reader.GetDataType<bool?>("data1"); // if it's null then we'll set it as null
data2intNotNull = reader.GetDataType<int>("data2", 0); // if the data is null, then we'll set to 0
data3date = reader.GetDataType<DateTime?>("data3", DateTime.Now); // same example as above, but instead of setting to 0, I can default it to today's date.
}
...
I had other issue regarding of null-able value object, and this saved me hours of trying to figure out what's going on. (WPF doesn't explain the issue when compiling runtime.)
我还有其他关于可为 null 的值对象的问题，这为我节省了数小时试图弄清楚发生了什么的时间。（WPF 在编译运行时没有解释这个问题。）
