C# 表是可为空的 DateTime，但 DataSet 抛出异常？

Table is nullable DateTime, but DataSet throws an exception?

提问by Amy

I'm attempting to use the DataSet designer to create a datatable from a query. I got this down just fine. The query used returns a nullable datetime column from the database. But, when it gets around to this code:

我正在尝试使用 DataSet 设计器从查询创建数据表。我把它记下来就好了。使用的查询从数据库返回一个可为空的日期时间列。但是，当涉及到这段代码时：

DataSet1.DataTable1DataTable table = adapter.GetData();

This throws a StrongTypingException from:

这会从以下位置抛出一个 StrongTypingException：

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
public System.DateTime event_start_date {
    get {
        try {
            return ((global::System.DateTime)(this[this.tableDataTable1.event_start_dateColumn]));
        }
        catch (global::System.InvalidCastException e) {
            throw new global::System.Data.StrongTypingException("The value for column \'event_start_date\' in table \'DataTable1\' is DBNull.", e);
        }
    }
    set {
        this[this.tableDataTable1.event_start_dateColumn] = value;
    }
}

How do I use the designer to allow this column to be Nullable?

如何使用设计器允许此列为 Nullable？

采纳答案by Robert Rossney

Typed data sets don't support nullable types. They support nullable columns.

类型化数据集不支持可为空类型。它们支持可为空的列。

The typed data set generator creates non-nullable properties and related methods for handling null values. If you create a MyDatecolumn of type DateTimeand AllowDbNullset to true, the DataRowsubclass will implement a non-nullable DateTimeproperty named MyDate, a SetMyDateNull()method, and an IsMyDateNull()method. This means that if you want to use a nullable type in your code, you have to do this:

类型化数据集生成器创建不可为空的属性和处理空值的相关方法。如果创建MyDate类型为 的列DateTime并AllowDbNull设置为true，则DataRow子类将实现DateTime名为的不可为空的属性MyDate、SetMyDateNull()方法和IsMyDateNull()方法。这意味着如果你想在你的代码中使用可为空类型，你必须这样做：

DateTime? myDateTime = myRow.IsMyDateNull() ? null : (DateTime?) row.MyDate;

While this doesn't totallydefeat the purpose of using typed data sets, it really sucks. It's frustrating that typed data sets implement nullable columns in a way that's less usable than the System.Dataextension methods, for instance.

虽然这并没有完全违背使用类型化数据集的目的，但它真的很糟糕。例如，令人沮丧的是，类型化数据集以一种比System.Data扩展方法更不可用的方式实现可空列。

Is particularly bad because typed data sets douse nullable types in some places - for instance, the Add<TableName>Row()method for the table containing the nullable DateTime column described above will take a DateTime?parameter.

尤其糟糕，因为类型化数据集在某些地方确实使用了可为空的类型——例如，上述Add<TableName>Row()包含可为空的 DateTime 列的表的方法将采用一个DateTime?参数。

Long ago, I asked about this issue on the MSDN forums, and ultimately the ADO project manager explained that nullable types were implemented at the same time as typed data sets, and his team didn't have time to fully integrate the two by .NET 2.0's ship date. And so far as I can tell, they haven't added new features to typed data sets since then.

很久以前在MSDN论坛上问过这个问题，最终ADO项目经理解释说可空类型与类型化数据集是同时实现的，他的团队没有时间通过​​.NET将两者完全集成2.0 的发货日期。据我所知，从那时起，他们就没有向类型化数据集添加新功能。

回答by Johannes Rudolph

It seems the Designergot the Database type for the column wrong.

似乎Designer列的数据库类型错误。

Open up the xsd Designer, hit F4to get the Properties Windowopen. Select the appropriate column and set Nullable(or something like that, don't remember the exact name) to true.

开拓xsd Designer，命中F4得到Properties Window开放。选择适当的列并将Nullable（或类似的东西，不记得确切名称）设置为 true。

回答by Aaron

The System.DateTime Object is not nullable. To make a DateTime nullable make it a DateTime? (put a ? after DateTime)

System.DateTime 对象不可为空。要使 DateTime 可以为空，使它成为 DateTime？（在 DateTime 后面放一个 ?）

DateTime? nullableDateTime = null;

回答by Rajesh

Thanks this solved my similar issue : Here is the code. In case of this question

谢谢这解决了我的类似问题：这是代码。在这个问题的情况下

Isevent_start_date()

would return whether or not the field is null.

将返回该字段是否为空。

In my case: I had a similar problem and I used the following solution

就我而言：我遇到了类似的问题，我使用了以下解决方案

            //Table's Name is Efforts,
            //Column's name is Target
            //So the dataset would automatically generate a property called IsTargetNull() which can be used to check nullables
            //Create an Adaptor
            EffortsTableAdapter ad = new EffortsTableAdapter();
            ProjectDashBoard.Db.EffortsDataTable efforts = ad.GetData();
            DataColumn targetColumn = new DataColumn();
            targetColumn = efforts.TargetColumn;

            List<DateTime?> targetTime = new List<DateTime?>();
            foreach (var item in efforts)
            {

                //----------------------------------------------------
                //This is the line that we are discussing about : 
                DateTime? myDateTime = item.IsTargetNull() ? null : (DateTime?)item.Target;
                //----------------------------------------------------

                targetTime.Add(myDateTime);

            }

回答by Tequila

I am using the code listed below to handle null cells in an Excel sheet that is read in to a datatable.

我正在使用下面列出的代码来处理读取到数据表中的 Excel 工作表中的空单元格。

if (!reader.IsDBNull(0))                                
{                                    
  row["DateOnCall"] = (DateTime)reader[0];
}

回答by Hyman D Menendez

To make this work with LINQ you will have to go to the Tables properties in your dataset.xsd. First look and make sure that the column is indeed set to nullable. THEN you must look at specific property "NullValue" for the column. Null Value defaults to "Exception", at least in VS 2012. Set it to Nothing for VB such that you can do "IsNot Nothing" in the LINQ Where clause.

要在 LINQ 中进行此操作，您必须转到 dataset.xsd 中的 Tables 属性。首先查看并确保该列确实设置为可为空。然后，您必须查看该列的特定属性“NullValue”。空值默认为“异常”，至少在 VS 2012 中是这样。对于 VB，将其设置为“无”，这样您就可以在 LINQ Where 子句中执行“IsNot Nothing”。

回答by Onur ??alan

It seems different with DataTable and Strongly Typed DataTable... Use Like this.

DataTable 和Strongly Typed DataTable 似乎不同……像这样使用。

DataSet1.DataTable1DataTable table = new DataSet1.DataTable1DataTable();
table.Merge(adapter.GetData().CopyToDataTable());

回答by Fawzy Mokhtar

I did this to insert value NULLin a DateTimecolumn,

我这样做是为了NULL在DateTime列中插入值，

assuming that I have a nullable DateTimecolumn in Database, I retrieved some data from the database in an object called responseand I want to insert nullable DateTimevalue in the DataSet column that called RenewDate:

假设我nullable DateTime在数据库中有一个列，我在一个名为的对象中从数据库中检索了一些数据，response并且我想nullable DateTime在名为的 DataSet 列中插入值RenewDate：

// create anew row of the same type of your table row
var rw = ds.StudentActionPrintDT.NewStudentActionPrintDTRow();

// check for null value
if(!response.RenewDate.HasValue)
{
  // if null, then the let DataSet to set it null by it's own way 
  rw.SetRenewDateNull();
}
else
{
  // if not null set value to the datetime value
  rw.RenewDate = response.RenewDate.Value;
}
// add the created row to the dateset [DataSetName].[ColumnName].Add[ColumnName]Row([The Created Row]);
ds.StudentActionPrintDT.AddStudentActionPrintDTRow(rw);

回答by Programmer

In DataSet Designer, use System.Objectdata type instead of System.DateTimeand set NullValue to (Null)and DefaultValue to <DBNull>and when it is needed, convert it such as:

在System.Object数据System.DateTime集设计器中，使用数据类型代替并将 NullValue 设置为(Null)和 DefaultValue<DBNull>并在需要时进行转换，例如：

var row1 = dateSet1.table1.FirstOrDefault();
if (row1 == null)
    return;
DateTime? date = (DateTime?) row1.ObjectDate;