System.Data.SqlTypes and NULL

Question

If I have a variable, X, of type SqlDecimal, that was instantiated without providing a decimal value parameter to the constructor, then the .IsNull property is True:

Dim X As New SqlDecimal '.Value = Null

If I have a Stored Procedure that acepts a single parameter, @SqlDecimalParameter, of type Decimal, the value of this this parameter could be NULL or a decimal value. This is how I would normally call the stored procedure:

Dim Cmd As New SqlClient.SqlCommand

 If X.IsNull Then
    Cmd.Parameters.Add(New SqlClient.SqlParameter(parameterName:="@SqlDecimalParameter", value:=DBNull.Value))
Else
    Cmd.Parameters.Add(New SqlClient.SqlParameter(parameterName:="@SqlDecimalParameter", value:=X.Value))
End If

However, I would expect the following to work, but it does not when the X.IsNull is true.

Cmd.Parameters.Add(New SqlClient.SqlParameter(parameterName:="@SqlDecimalParameter", value:=X))

It seems like the 1st example should work. Am I missing something? after all, the Sql datatypes are designed to work with SQL Server, I thought.

When X is set to the following

commissionDue = New SqlDecimal(3.2)

I get the following error:

Failed to convert parameter value from a SqlDecimal to a Decimal

It works when X.IsNull is True.

How do you set the value of the PARAM object when it can be NULL?

Answer 1

First idea:
You need to specify the type explicitly when creating a new instance of a parameter. Otherwise the framework will try to infer the type, which is never something you should count on going the right way.

Dim parameter As New SqlParameter("@SomeName", SqlDbType.[Decimal])
parameter.Value = 3.2

Second idea:
Instead of creating a SqlDecimal value try using a simple nullable decimal. This way it does not have to be converted and you might be able to avoid the error.

Dim test As System.Nullable(Of Decimal) = 3.2