About the author

Miron Abramson
Me
Software Engineer,
CTO at PixeliT
and .NET addicted for long time.
Open source projects:
MbCompression - Compression library

Recent comments

Authors

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2014

Creative Commons License

Blog Flux Directory
Technology Blogs - Blog Top Sites

Caution When passing Null or DateTime into Store Procedure

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.

Currently rated 5.0 by 3 people

  • Currently 5/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Posted by Miron on Wednesday, September 26, 2007 7:22 PM
Permalink | Comments (4) | Post RSSRSS comment feed