Table is nullable DateTime, but DataSet throws an exception?

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:

DataSet1.DataTable1DataTable table = adapter.GetData();

This throws a StrongTypingException from:

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
public System.DateTime event_start_date {
    get {
        t开发者_StackOverflowry {
            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?

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 MyDate column of type DateTime and AllowDbNull set to true, the DataRow subclass will implement a non-nullable DateTime property 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:

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

While this doesn't totally defeat 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.Data extension methods, for instance.

Is particularly bad because typed data sets do use 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.

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.

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);

            }

It seems the Designer got the Database type for the column wrong.

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

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.

I did this to insert value NULL in a DateTime column,

assuming that I have a nullable DateTime column in Database, I retrieved some data from the database in an object called response and I want to insert nullable DateTime value in the DataSet column that called 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);

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

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

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

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

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

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

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

DateTime? nullableDateTime = null;