How to specify DEFAULT for optional parameter to stored procedure wrapper function

[jamesclose]

My problem is this (apologies if this is a little long ... hang in there):

I can define a function in VB.NET with optional parameters that wraps a SQL procedure:

 Sub Test(Optional ByVal Arg1 As Integer = 0, _
           Optional ByVal Arg2 As Integer = 0, _
           Optional ByVal Arg3 As Integer = 0)

    ' Call my SQL proc with the same signature

  End Sub

And I can test for a missing argument by testing for the default I set:

 If (Arg1 = 0) Then
      ' Arg1 is missing so leave it off the call to the stored proc
    Else
      ' Arg1 is provided so add it to the call to the stored proc
    End If

Great, so in principle I can leave off unspecified optional parameters when calling the stored procedure and let the stored procedure default those parameters based on whatever defaults are specified in the stored procedure.

But hang on, what if my choice of default for the optional parameter is a valid value to send to the stored procedure? What if Arg1 can be 0 and that is different to the default set on the stored procedure?

Maybe I could use "Nothing" as my default for the optional parameter, and test using IsNothing. This works for complex types like String and any Objects. But fails for simple types like Integer, as setting an Integer to Nothing actually sets it to the value 0 and a subsequent call to IsNothing returns False:

Dim i As Integer
    i = Nothing
    If (IsNothing(i)) Then
      ' this arm is never executed
    Else
      ' this will print 0
      Debug.Print(i)
    End If

Okay, so it looks like I need to replicate all the default values I have on the stored procedure into the wrapper VB.NET function. This is unfortunate as it duplicates code and leaves space for the two to become out of sync.

I want to be able to write something like:

Sub Test(Optional ByVal Arg1 As Integer = Default, _
           Optional ByVal Arg2 As Integer = Default, _
           Optional ByVal Arg3 As Integer = Default)

    ' Call my SQL proc with the same signature

  End Sub

And test for this special "Default" value on any type and not pass the stored proc parameter if this is set.

I have tried defining Structures that attempt to extend the base types (a bit like
those is System.Data.Sql Types) but then I hit the problem that Optional parameters cannot be Structures (only classes or simple types).

I have also considered using Overloads rather than Optional parameters, but this doesn't work either as it seems you cannot replicate the same functionality you have with Optional parameters with Overloads. Considering my original function:

Sub Test(Optional ByVal Arg1 As Integer = 0, _
           Optional ByVal Arg2 As Integer = 0, _
           Optional ByVal Arg3 As Integer = 0)

I could re-write this using overloads as:

Sub Test(ByVal Arg1 As Integer, _
           ByVal Arg2 As Integer, _
           ByVal Arg3 As Integer)
  End Sub
	
  Sub Test(ByVal Arg1 As Integer, _
           ByVal Arg2 As Integer)
  End Sub

  Sub Test(ByVal Arg1 As Integer)
  End Sub

But I cannot do:

Sub Test(ByVal Arg2 As Integer, _
           ByVal Arg3 As Integer)
  End Sub

As this has the same signature as one of the previous versions. So although I can happily do this using optional parameters:

Test(Arg2:=3,Arg3:=1)

I cannot seem to achieve the same with an overload.

Can anyone think up a way of setting an optional parameter to some value I can detect as missing that is distinct from any default I may have set in the stored procedure that function wraps?

Thanks

[Plater]

Well, I don't know is VB supports it, but in C# I can do this:
[code=c#]
int? i = null;
if (i == null)
{//this code fires
int b = 4;
}
[/code]
Adding a ? to the end of a value type allows it to be nullable. Maybe look to see if there is a VB equivilent to nullable value types?

[tlhintoq]

If all your real world data is positive numbers then you could use a default of -1 to be your "null"

Microsoft does it all the time. Look at a listbox. The items in the listbox are indexed zero and up. If the selected index is -1 then nothing is selected.

[tlhintoq]

TIP: When you are writing your question, there is a button on the tool bar that wraps the [code] tags around your copy/pasted code. It helps a bunch. Its the button with a '#' on it. More on tags. They're cool. Check'em out.

[jamesclose]

Thanks for the suggestions,

I had considered the "special values" solution, but it feels like a kludge. You never know, one day date.MinValue might be a valid parameter value!

After much casting about I found a sort of solution (not as elegant as I had hoped but does the job) using generics.

I define a generic type that can accept Nothing even for simple intrinsic types (like integer) and then I can test IsNothing on all the inputs to see if they were specified. One nifty thing here is the Widening / Narrowing functions that allow the type to accept integers, strings, dates, etc. without an explicit Newing or type converting.

However I am not 100% happy with this solution, as to my mind "Nothing" means de-reference me. I wanted to use a constant like "DefaultVal ue", but I could not get the compiler to accept it no matter what I tried. So I ended up taking MS at face value:

http://msdn.microsoft.com/en-us/library/0x9tb07z(VS.80).aspx

... "Nothing - Represents the default value of any data type."

For those interested my code is:

'------------------------------------------------------------------------------
' Defaultable(Of dataType) Generic Class
'
' Author: JC
' Created date: 21 Sep 2009
' Description: Generic class which enables simple intrinsic types to
'  accept the Nothing so that IsNothing returns true
'  Providing a means to detect default values for optional parameters
'  Also enables all types to accept System.DB.Null values
'------------------------------------------------------------------------------
Public Class Defaultable(Of dataType)
  Implements IComparable(Of dataType), _
             System.Data.SqlTypes.INullable

  '----------------------------------------------------------------------------
  ' Member variables
  '----------------------------------------------------------------------------
  Private _Value As dataType
  Private _bIsNull As Boolean

  '----------------------------------------------------------------------------
  ' New (Generic Overloaded)
  '----------------------------------------------------------------------------
  Public Sub New(ByVal newvalue As dataType)
    _Value = newvalue
  End Sub

  '----------------------------------------------------------------------------
  ' New (System.DBNull Overloaded)
  '----------------------------------------------------------------------------
  Public Sub New(ByVal newvalue As System.DBNull)
    _bIsNull = True
    _Value = Nothing
  End Sub

  '----------------------------------------------------------------------------
  ' Value 
  '----------------------------------------------------------------------------
  Public Property Value() As dataType
    Get
      Return _Value
    End Get
    Set(ByVal newvalue As dataType)
      _Value = newvalue
    End Set
  End Property

  '----------------------------------------------------------------------------
  ' Widening CType (Overloaded)
  '----------------------------------------------------------------------------
  Public Shared Widening Operator CType(ByVal d As dataType) As Defaultable(Of dataType)
    Return New Defaultable(Of dataType)(d)
  End Operator

  '----------------------------------------------------------------------------
  ' Widening CType (Overloaded)
  '----------------------------------------------------------------------------
  Public Shared Widening Operator CType(ByVal d As System.DBNull) As Defaultable(Of dataType)
    Return New Defaultable(Of dataType)(d)
  End Operator

  '----------------------------------------------------------------------------
  ' Narrowing CType
  '----------------------------------------------------------------------------
  Public Shared Narrowing Operator CType(ByVal d As Defaultable(Of dataType)) As dataType
    Return d.Value
  End Operator

  '----------------------------------------------------------------------------
  ' CompareTo
  '----------------------------------------------------------------------------
  Public Function CompareTo(ByVal other As dataType) As Integer Implements System.IComparable(Of dataType).CompareTo
    If (TypeOf _Value Is IComparable(Of dataType)) Then
      Return CType(_Value, IComparable(Of dataType)).CompareTo(other)
    Else
      Return _Value.GetType.ToString.CompareTo(other.GetType.ToString)
    End If
  End Function

  '----------------------------------------------------------------------------
  ' IsNull
  '----------------------------------------------------------------------------
  Public ReadOnly Property IsNull() As Boolean Implements System.Data.SqlTypes.INullable.IsNull
    Get
      Return _bIsNull
    End Get
  End Property

End Class

I can now do this with my test function:

  Private Sub Test(Optional ByRef int1 As Defaultable(Of Integer) = Nothing, _
                   Optional ByRef int2 As Defaultable(Of Integer) = Nothing, _
                   Optional ByRef int3 As Defaultable(Of Integer) = Nothing)

    Dim strCmd As String = "exec mystoredproc "

    If (Not IsNothing(int1)) Then
      strCmd &= "@int1=" & int1.Value.ToString & ", "
    End If

    If (Not IsNothing(int2)) Then
      strCmd &= "@int2=" & int2.Value.ToString & ", "
    End If

    If (Not IsNothing(int3)) Then
      strCmd &= "@int3=" & int3.Value.ToString & ", "
    End If

    Debug.Print(strCmd)

  End Sub

Still I'd quite like to define my own Keyword "Default" that works like Nothing (i.e. causes no type conversion errors) but is detectable distinct from Nothing as I think MS have rather overloaded the meaning of Nothing here.

[Plater]

Wow VB makes it a long way to get the same as the ? nullable datatypes in C#

[jamesclose]

It is actually easy to define a Nullable type in VB just do:

Dim aVariable As Nullable(Of Integer)

And there you go, aVariable can now be set to Nothing (null) quite happily.

Problem: Nullable(Of T) is a Structure not a Class so you can't use it for optional parameters (not that those exist in c# anyway) and you can't test IsNothing on it you have to do .HasValue instead.

I need a type that can be used as an optional parameter hence the solution above.

[Plater]

Optional parameters exist in C#, using the params object[] notation I believe.
Although, I've not found a use for optional parameters in my own work yet.