I can define a function in VB.NET with optional parameters that wraps a SQL procedure:
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- 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
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:
Expand|Select|Wrap|Line Numbers
- 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
I want to be able to write something like:
Expand|Select|Wrap|Line Numbers
- 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
I have tried defining Structures that attempt to extend the base types (a bit like
those is System.Data.SqlTypes) 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:
Expand|Select|Wrap|Line Numbers
- Sub Test(Optional ByVal Arg1 As Integer = 0, _
- Optional ByVal Arg2 As Integer = 0, _
- Optional ByVal Arg3 As Integer = 0)
Expand|Select|Wrap|Line Numbers
- 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
Expand|Select|Wrap|Line Numbers
- Sub Test(ByVal Arg2 As Integer, _
- ByVal Arg3 As Integer)
- End Sub
Expand|Select|Wrap|Line Numbers
- Test(Arg2:=3,Arg3:=1)
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