CODEWITHSUNDEEP
asp.net.netsqlsql-servervb.net
user1732407
user1732407

Reputation: 63

Evaluation differences between IF operator and If, then, else

I have come across a scenario that I did not expect and need help answering. Our current system makes calls to SQL stored procs and as such creates SQL commands, adds the parameters and adds the values of those parameters...say for an Insert.

We had code like what is below...

cmd.Parameters.Add("@MyTimestamp", SqlDbType.DateTime)
If MyObject.MyTimestamp <> Nothing Then
   cmd.Parameters.Item("@MyTimestamp").Value = MyObject.MyTimestamp
Else
   cmd.Parameters.Item("@MyTimestamp").Value = Nothing
End If

Now, when I first saw this I was a little surprised that the MyObject.MyTimestamp ever evaluated to Nothing, but it had been there for years with no issue. During a clean-up effort the code to add a parameter and set its value was combined and as such the code above became...

cmd.Parameters.Add("@MyTimestamp", SqlDbType.DateTime).Value = If(MyObject.MyTimestamp <> Nothing, MyObject.MyTimestamp, Nothing)

To me, this looked equivalent to what the code was doing originally, however that is not what was found during testing. While testing I received a SqlTypeException:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

That is because using the If Operator evaluated the MyObject.MyTimestamp as not being Nothing, which it isn't Nothing it is DateTime.MinValue and it tried to insert it into sql and it blew up. The way the original code was written evaluates the same Timestamp as being Nothing (not sure how/why) and does the false part. So my question is what is the difference and should I be concerned about other datatypes...?

Upvotes: 4

Views: 149

Answers (1)

GarethD
GarethD

Reputation: 69789

Remembering (as I often don't) that Nothing is not equivalent to null in C#, but more like Default(T), the following does not check if MyObject.MyTimestamp has been set:

MyObject.MyTimestamp <> Nothing

But is acutally checking if it is the default datetime (DateTime.MinValue), which it presumably always is.

In addition, when you use:

If(MyObject.MyTimestamp <> Nothing, MyObject.MyTimestamp, Nothing)

The two return types (MyObject.MyTimestamp, Nothing) have to be of the same type, so since the compiler knows MyObject.Timestamp should be a datetime, it converts nothing to a default datetime.

When you use this, however:

If MyObject.MyTimestamp <> Nothing Then
   cmd.Parameters.Item("@MyTimestamp").Value = MyObject.MyTimestamp
Else
   cmd.Parameters.Item("@MyTimestamp").Value = Nothing
End If

It recognises that the parameter for "@MyTimestamp" is of the type SqlDbType.DateTime, so creates the default for this. I don't know whether this is DbNull or an actual value off the top of my head, but either way it will be an allowed value so won't throw an error.

As an aside you probably want to be setting the parameter to DbNull.Value and not Nothing:

If MyObject.MyTimestamp <> Nothing Then
   cmd.Parameters.Item("@MyTimestamp").Value = MyObject.MyTimestamp
Else
   cmd.Parameters.Item("@MyTimestamp").Value = DbNull.Value
End If

Upvotes: 3

Related Questions