Dates always seem to give me a headache when working with business apps. Trying to come up with framework level support for handling them is not always easy. In this post I will outline an approach to dealing with dates that we are using with some success in my current project. Much praise to Matt Lindstrom for the actual implementation of what I am going to outline here.
ORM Mapping
In most databases columns defined as date types can be built to allow nulls. This makes sense when you consider the implications for reporting etc. In .Net the native DateTime object is a value type, thus it cannot represent null. To correct this inconsistency we leverage Nullable<DateTime> for all date properties. And to prevent all the nasty ‘HasValue’ checking in our ORM methods we use an extension method on Nullable<DateTime> as follows.
/// <summary>
/// Prepares for database.
/// </summary>
/// <param name="dateTime">The date time.</param>
/// <param name="includeTimeStamp">if set to <c>true</c> [include time stamp].</param>
/// <returns></returns>
public static object PrepareForDatabase(this Nullable<DateTime> dateTime)
{
if (dateTime.HasValue)
{
return dateTime.Value;
}
return DBNull.Value;
}
This method could be used the following way.
/// <summary>
/// Method used to build command
/// </summary>
/// <param name="database">Database to run command against</param>
/// <returns>Fully loaded command object ready to execute</returns>
public override DbCommand GetCommand(Database database)
{
DbCommand command = database.GetStoredProcCommand("MyInsertCommand");
database.AddInParameter(command, "DATE_in", DbType.DateTime, SomeNullableDate.PrepareForDatabase());
return command;
}
Of course this code only covers the mapping to the data store some additional work is needed when saturating objects with data. Again to support this we added an extension method on the CSLA SafeDataReader. Here we also account for the MinDate assuming that it is the same as a null date.
/// <summary>
/// Gets the nullable date time.
/// </summary>
/// <param name="data">The data.</param>
/// <param name="i">The i.</param>
/// <returns></returns>
public static DateTime? GetNullableDateTime(this SafeDataReader data, int i)
{
if (data.GetDateTime(i).Equals(DateTime.MinValue))
{
return null;
}
return data.GetDateTime(i);
}
And in practice
/// <summary>
/// Method used for ORM mapping of CompRehabCase
/// </summary>
/// <param name="data">SafeDataReader</param>
private void Populate(SafeDataReader data)
{
LoadProperty<Nullable<DateTime>>(SomeNullableDateTime, data.GetNullableDateTime(data.GetOrdinal("SOME_DATE")));
}
And that all folks.
Nullable<DateTime>
I am Felling a bit lazy tonight!
