Every small to big web application is using DataBase, and I don't neet to explain the importance of using Store Procedures.
If we use directly the SqlCommand, or using any DAL component, we need to take care when trying to send null or DateTime to the Store Procedure:
SQL-Server null value is not the same as null in .NET. Null in .NET
means that a reference variable does not reference an object instanc. That is, a reference variable can reference an object instance OR be "null", not both.
DBNull.Value is a reference to an object instance of type DBNull. Therefore DBNull.Value is not "null".
But, when we are sending null to the Store Procedure we actualy mean that we want the value of the parameter to be empty
and to be 'null' in the DataBase. If we set a SqlParameter value to 'null', we will get an Exception,
so we need to convert it to DBNull.Value before sending it to the Store Procedure.
Another thing we need to be aware of, that the minimum and the maximum value of DateTime in .NET and in SQL-Server are not the same.
.NET DateTime minimum value is 1/1/0001 00:00:00 and the maximum is 12/31/9999 23:59:59.999, while Sql minimum value is 1/1/1753 00:00:00.003
and the maximum is 12/31/9999 23:59:59.997
If we will try to send to the SP a datetime that is less then the minimum of the Sql or DateTime.MaxValue, we will get an Exception, so we need to
currect it before.
Here is a simple method to cover both cases - null & DateTime:
private static void DoSafeParameter(ref SqlParameter dbParameter)
{
if (dbParameter.Direction == ParameterDirection.Input || dbParameter.Direction == ParameterDirection.InputOutput)
{
if (dbParameter.Value == null)
{
dbParameter.Value = DBNull.Value;
}
else if (dbParameter.SqlDbType == SqlDbType.DateTime)
{
if (((DateTime)dbParameter.Value) < System.Data.SqlTypes.SqlDateTime.MinValue.Value)
{
dbParameter.Value = System.Data.SqlTypes.SqlDateTime.MinValue.Value;
}
else if (((DateTime)dbParameter.Value) > System.Data.SqlTypes.SqlDateTime.MaxValue.Value)
{
dbParameter.Value = System.Data.SqlTypes.SqlDateTime.MaxValue.Value;
}
}
}
}
(I choosed to convert any DateTime that is less then the Sql value to SqlDateTime.MinValue in any cases)
Note
All this is not relevant when using Enterprise Library. It do this for you.