.NET Framework
.NET Framework
Yesterday, I posted an article on a problem I was having with trying to insert a null value for a nullable datetime field in sql 2K. Eric Spray sent me an email regarding this issue. He said he couldn't post a comment (thanks to the problems we're having with the content spammers), but wanted to weigh in. I really appreciate his comment and wanted to post it here.
From Eric:
The basic problem is: how to represent a null value when the type does not support null. I have had this debated several times with colleagues, most use your solution by using a designated value to represent null (ie MinValue). I don't like this solution, reason is you are using a valid value. What if another developer or end user wants/needs to use that value, they are SOL. Sometimes the requirements of a project do not allow you to use up a valid entry to represent null. So, my solution is to have a property to manage the null state. I like this solution better because it does not take away valid values. Now lets say you need to restrict the values being entered (ie MyDate <= 1/1/2005). I would still recommend you use the below pattern, that way WHEN the validation requirement changes it will be an easier change.
That's just my $.02
Here is my solution in C#
public class MyClass
{
private DateTime _myDate;
private bool _myDateIsNull = true;//Initialize to true because the value is not set yet
public DateTime MyDate
{
get
{
if(this.MyDateIsNull)
{
throw new NullReferenceException("MyDate is null!");
}
return _myDate;
}
set
{
//Input validation here
if(value <= DateTime.Parse("1/1/2005"))
{
throw new ArgumentOutOfRangeException("Date must be greater than '1/1'2005'");
}
_myDate = value;
_myDateIsNull = false;
}
}
public bool MyDateIsNull
{
get
{
return _myDateIsNull;
}
set
{
_myDateIsNull = value;
}
}
}
<note to self>
I have run into this problem a few times, but for some reason can never seem to remember the resolution. Therefore I decided to note it here for future reference.
When trying to insert a null value for a nullable datetime field in sql 2K, I end up getting the SqlDateTime Overflow error. This appears to be a problem with a difference between the definition of the Date and Datetime datatypes in .net and sql2K.
The way I found to solve this (there may be better and I would love to hear if there are) is:
if myDate = DateTime.MinValue then
myParamater.Value = System.DBNull.Value
else
myParamater.Value = myDate
end if
Please weigh in if you have any additional insight or a better solution, but this does seem to work.
</note to self>