CODEWITHSUNDEEP
vb.net
Nostromo
Nostromo

Reputation: 1274

Strange behaviour of the If() statement

today I stumbled upon a strange behaviour of the VB.net If() statement. Maybe you can explain why it is working like it does, or maybe you can confirm that it is a bug.

So, I have a SQL database with a table "TestTable" with an int column "NullableColumn" that can contain NULL. I'd like to read out the content of this column.

So I declare a variable of type Nullable(Of Integer) for that matter, open a SqlClient.SqlDataReader for "SELECT NullableColumn FROM TestTable" and use the following code to get the content of this column:

Dim content as Nullable(Of Integer)

...

Using reader as SqlClient.SqlDataReader = ...
  content = If(reader.IsDBNull(reader.GetOrdinal("NullableColumn")), Nothing, reader.GetInt32(reader.GetOrdinal("NullableColumn")))
End Using

But after that my variable content has the value 0, not Nothing as I would have expected.

When debugging everything looks alright, so

So, why does my variable has the value 0?

Upvotes: 5

Views: 167

Answers (3)

Konrad Rudolph
Konrad Rudolph

Reputation: 546153

Chris has given the explanation but I dislike the style of assignment he’s chosen because it splits assignment from variable declaration.

By contrast, I recommend initialising variables upon declaration. In this case it’s admittedly slightly convoluted since you need cast either operator of If to the correct type first.

Dim content = If(reader.IsDBNull(reader.GetOrdinal("NullableColumn")),
                DirectCast(Nothing, Integer?),
                reader.GetInt32(reader.GetOrdinal("NullableColumn")))

Actually you can also use the slightly shorter New Integer?() instead of the DirectCast.

Of course now content is declared inside the Using block – this might not be what you want but you should try to make the declaration as local as possible.

Furthermore, this code is complex and will probably be reused. I suggest creating a separate (extension) method to convert database NULL values to nullables:

<Extension> _
Public Shared Function GetNullable(Of T)(SqlClient.SqlDataReader this, String fieldName) As T?
    Dim i = this.GetOrdinal(fieldName)
    Return If(this.IsDBNull(i), New T?(), this.GetFieldValue(Of T)(i))
End Function

Now you can use it as follows:

Dim content = reader.GetNullable(Of Integer)("NullableColumn")

Upvotes: 1

Chris Dunaway
Chris Dunaway

Reputation: 11216

In VB Nothing is not the same as null. The If operator must determine the type of its result based on the arguments passed to it. Nothing, of course, has no type so the only type that the If operator can return in your code is Int32. If the IsDBNull method returns true, then the If operator returns Nothing cast as Int32. In VB, Nothing returns the default value for a type. For an Int32, the default value is 0.

From MSDN on the Nothing keyword:

Nothing represents the default value of a data type. The default value depends 
on whether the variable is of a value type or of a reference type.

For non-nullable value types, Nothing in Visual Basic differs from null in C#. 
In Visual Basic, if you set a variable of a non-nullable value type to Nothing, 
the variable is set to the default value for its declared type. In C#, if you 
assign a variable of a non-nullable value type to null, a compile-time error 
occurs.

I think just a regular If would work best:

If Not reader.IsDBNull(reader.GetOrdinal("NullableColumn")) Then
    content = reader.GetInt32(reader.GetOrdinal("NullableColumn"))
End If

Or to keep it shorter

If Not reader.IsDBNull(reader.GetOrdinal("NullableColumn")) Then content = reader.GetInt32(reader.GetOrdinal("NullableColumn"))

Upvotes: 5

MarcinJuraszek
MarcinJuraszek

Reputation: 125650

But after that my variable content has the value 0, not Nothing as I would have expected.

How do you check the content value?

First of all, you should start with content.HasValue property. It should be False for your case of Nothing and True when correct value was fetched from database.

You should also get InvalidOperationException while accessing content.Value when it hasn't got value.

Upvotes: 1

Related Questions