C# 如何从数据库中获取可为空的 DateTime
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9503698/
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 get nullable DateTime out of the database
提问by pikachu
My SQL Server database contains nullable DateTime values. How can I convert them to a nullable DateTime object in my application in C#?
我的 SQL Server 数据库包含可为空的 DateTime 值。如何在我的 C# 应用程序中将它们转换为可为空的 DateTime 对象？
This is what I would think it would look like, but it doesn't:
这就是我认为的样子，但事实并非如此：
DateTime? dt = (DateTime?) sqldatareader[0];
采纳答案by Dour High Arch
A SQL null is not the same as a .NET null; you have to compare against System.DBNull.Value:
SQL null 与 .NET null 不同；您必须与 System.DBNull.Value 进行比较：
object sqlDateTime = sqldatareader[0];
DateTime? dt = (sqlDateTime == System.DBNull.Value)
? (DateTime?)null
: Convert.ToDateTime(sqlDateTime);
In answer to your comment, the data type of the Itemproperty of a DataReaderis that of the underlying database type. It could be System.Data.SqlTypes.SqlDateTimefor a non-null SQL Server database, or System.DBNullfor a null column, or System.Data.Odbc.OdbcTypes.SmallDateTimefor an ODBC database, or really just about anything. The only thing you can rely on is that it is of type object.
为了回答您的评论，Itema的属性的数据类型DataReader是基础数据库类型的数据类型。它可以System.Data.SqlTypes.SqlDateTime用于非空 SQL Server 数据库，也可以System.DBNull用于空列，或者System.Data.Odbc.OdbcTypes.SmallDateTime用于 ODBC 数据库，或者几乎任何东西。您唯一可以依赖的是它的类型object。
This is also why I suggest using Convert.ToDateTime()instead of type coercion to DateTime. There is no guarantee a ODBC or whatever date column can be coerced to a .NET DateTime. I note your comment specifies a "sqldatareader", and a SQL Server System.Data.SqlTypes.SqlDateTimecan indeed be coerced to a System.DateTime, but your original question did not tell us that.
这也是为什么我建议使用Convert.ToDateTime()而不是类型强制转换到DateTime. 不能保证可以将 ODBC 或任何日期列强制转换为 .NET DateTime。我注意到您的评论指定了“sqldatareader”，并且System.Data.SqlTypes.SqlDateTime确实可以将 SQL Server强制转换为System.DateTime，但是您最初的问题并没有告诉我们。
For more information on using DataReaders, consult MSDN.
有关使用DataReaders 的更多信息，请参阅MSDN。
回答by Mark S. Rasmussen
You need to check if the value "is DBNull" rather than just null. I posted a small helper class on my blog: http://improve.dk/archive/2007/10/08/handling-dbnulls.aspx
您需要检查值是否“是 DBNull”而不仅仅是 null。我在我的博客上发布了一个小助手类：http: //improve.dk/archive/2007/10/08/handling-dbnulls.aspx
Once you implement the class, you use it like this:
一旦你实现了这个类，你就可以像这样使用它：
DateTime? dt = DBConvert.To<datetime?>(sqldatareader[0]);
回答by Paddy
DateTime? dt = null;
if (sqldatareader[0] != System.DbNull.Value)
{
dt = (DateTime)sqldatareader[0];
}
回答by Nicholas Carey
A while ago, I wrote a bunch of extension methods for a DataRow to do just this sort of downcasting...because I hate writing repetitive drivel. Usage is simple:
不久前，我为 DataRow 写了一堆扩展方法来做这种向下转换……因为我讨厌写重复的胡说八道。用法很简单：
foreach( DataRow dr in someDataTable )
{
DateTime? dt = dr.CastAsDateTimeNullable( "lastUpdated" ) ;
int id = dr.CastAsInt( "transactionID" ) ;
// etc.
}
Here's the piece for DateTime values. Adding implementation for for other data types should be pretty trivial. Wouldn't be difficult to do the same sort of thing to a DataReader if one was so inclined.
这是 DateTime 值的部分。为其他数据类型添加实现应该非常简单。如果一个人如此倾向，对 DataReader 做同样的事情并不难。
I tried to come up with generic methods, but limitations in the way generics are done made it difficult or impossible to do and still get the behavior I wanted (e.g., nullvalues rather than default(T)— getting default values for SQL NULLs that would make differentiating between 0and null...difficult).
我试图想出泛型方法，但是泛型方法的局限性使得它很难或不可能做到并且仍然获得我想要的行为（例如，null值而不是default(T)- 获取 SQL NULL 的默认值，这将区分0和null...难的）。
public static class DataRowExtensions
{
#region downcast to DateTime
public static DateTime CastAsDateTime( this DataRow row , int index )
{
return toDateTime( row[index] ) ;
}
public static DateTime CastAsDateTime( this DataRow row , string columnName )
{
return toDateTime( row[columnName] ) ;
}
public static DateTime? CastAsDateTimeNullable( this DataRow row , int index )
{
return toDateTimeNullable( row[index] );
}
public static DateTime? CastAsDateTimeNullable( this DataRow row , string columnName )
{
return toDateTimeNullable( row[columnName] ) ;
}
#region conversion helpers
private static DateTime toDateTime( object o )
{
DateTime value = (DateTime)o;
return value;
}
private static DateTime? toDateTimeNullable( object o )
{
bool hasValue = !( o is DBNull );
DateTime? value = ( hasValue ? (DateTime?) o : (DateTime?) null ) ;
return value;
}
#endregion
#endregion downcast to DateTime
// ... other implementations elided .. for brevity
}
回答by pikachu
I recently found this trick, it's simple:
我最近发现了这个技巧，很简单：
DateTime? dt = sqldatareader[0] as DateTime?;
回答by MTELab
Just use:
只需使用：
System.Nullable<System.DateTime> yourVariableName;
Make it easy on yourself :)
让自己轻松一点:)
回答by Usman Ali
how about creating helper method
如何创建辅助方法
private static DateTime? MyDateConverter(object o)
{
return (o == DBNull.Value || o == null) ? (DateTime?)null : Convert.ToDateTime(o);
}
Usage
用法
MyDateConverter(sqldatareader[0])
