Converting DateTime Nulls for queries

[larrydhunt]

I have a MySQL database which is used by a MS Access application and a .NET application. The Access application can create Nulls in DateTime columns but the .NET application cannot handle Nulls in DateTime columns. In LINQ to SQL is there a way to convert DateTime Nulls to 1/1/1753 when the data is loaded from the database?

Thanks,
Larry

[AndreyR]

Could you please provide a more detailed description of the problem?
So far I can only recommend to use Nullable types, like System.Nullable

[larrydhunt]

Thanks for your quick reply. Here is more detail. The .NET application is using Microsoft Enterprise Library to load the MySQL data into a untyped dataset. Then the code runs through the data and converts all DateTime Nulls to 1/1/1753. Then the code copies the untyped dataset to a typed dataset. This typed dataset doesn't allow DateTime Nulls. This all happens in the Data Access layer. The Business Logic and Client layers cannot handle Nulls. The Window form controls are coded to convert 1/1/1753 to "" when displaying dates.

Here is a sample code snippet from the Data Access layer:

Code: Select all

' Load info into untyped dataset using Microsoft.Practices.EnterpriseLibrary.Data.Database.
MyDatabase.LoadDataSet(myQuery, MyDataSet, MyTableNames)

' Loop through DataTable converting all DBNulls to #1/1/1753#.
For Each toRow As System.Data.DataRow In MyDataSet.Tables(0).Rows
	For Each toColumn As DataColumn In MyDataSet.Tables(0).Columns
		If Convert.IsDBNull(toRow(toColumn.Ordinal)) AndAlso Not toColumn.ReadOnly AndAlso Not toColumn.AutoIncrement Then
			Select Case toColumn.DataType.Name
				Case "DateTime"
					toRow(toColumn.Ordinal) = #1/1/1753#
			End Select
		End If
	Next toColumn
Next toRow

MyDataSet.AcceptChanges()

' Later on the data in this untyped dataset is copied to a type datset which doesn't allow nulls.

In LINQ to SQL (or the Entity Framework - we have not decided which technology to use) is there a way to do this DateTime Null conversion? Maybe I can do something on the PropertyChanged or PropertyChanging event?

Any suggestions?

Thanks,
Larry

[AndreyR]

You can use the following code

Code: Select all

  partial class null_datetime {
    partial void OnfieldChanged () {
        if(_field == null)
          _field = new DateTime(1753,1,1);
    }

both in LINQ to MySQL and Entity Framework.