Using ParamArray in VB.Net passing Object Or Array of Objects as Argument

Question

I'm having a brain fart.

I'm trying to set up a function that can take in an object as its arguments. This function should output a data table. Here is what I have so far and is not functional.

DEFINED INPUT

'Example 1
GetDataTableWithParams("..myStoredProcedure", {333, SqlDbType.Int, "@UserID"})

'Example 2
GetDataTableWithParams("..myStoredProcedure", [{333, SqlDbType.Int, "@UserID"}, {9090, SqlDbType.Int, "@ProfileID"}]

EXPECTED OUTPUT

'Example 1
Dim params(0) As IDataParameter
params(0) = Data.GetDataParameter(333, 8, "@UserID")

'Example 2
Dim params(1) As IDataParameter
params(0) = Data.GetDataParameter(333, 8, "@UserID")
params(1) = Data.GetDataParameter(9090, 8, "@ProfileID")

ACTUAL OUTPUT

'Example 1
params(0) DataAction.GetDataParameter(333
params(1) DataAction.GetDataParameter(8
params(2) DataAction.GetDataParameter(@UserID

My Function

Public Function GetDataTableWithMultipleParams(ByVal _StoredProcedure As String, ByVal ParamArray _Params() As Object) As DataTable

        If _Params.Length <= 1 Then Exit Function

            Dim params(_Params.Length) As IDataParameter

            For i As Integer = 0 To UBound(_Params, 1)
                Console.WriteLine("params(" & i & ") " & "DataAction.GetDataParameter(" & _Params(i), ")")
            Next


        'Dim table As DataTable = DataAction.GetDataTableParametrized(_StoredProcdure, CommandType.StoredProcedure, params)
        'Return table

End Function

This is a basic problem with your algorithm. It's doing exactly what you told it to do, without any surprises. What's not clear is why you would ever expect that code to output the "expected output". — Steven Doggart
– Steven Doggart, Commented Feb 1, 2019 at 20:41
First problem is that you have the parameter set to be an array of objects, but you want to call it, in the second case, with an array of object arrays. — Steven Doggart
– Steven Doggart, Commented Feb 1, 2019 at 20:44
What would be far preferable, I'd think, would be to create a class that has the three properties, then you could just have it take a ParamArray of that class. No 2D array needed. And then you wouldn't need to overload it either. — Steven Doggart
– Steven Doggart, Commented Feb 1, 2019 at 20:46
So from your suggestion. It would make more sense to create a class with this method. I know I may not be explaining as clearly as I can but bear with me. I'm trying to avoid overloading this method as the number of arguments may vary. Also, this is a contrived function as I'm NOT actually expecting the output to be what I posted. — cpt-crunchy
– cpt-crunchy, Commented Feb 1, 2019 at 21:28
This function is suppose to return a DataTable and the for loop is expected to build the parameters and pass that as an argument to the DataAction.GetDataTableParametrized Method. — cpt-crunchy
– cpt-crunchy, Commented Feb 1, 2019 at 21:30

IAmNerd2000 · Accepted Answer · 2019-02-02 08:16:21Z

1

The following is what Steven D is proposing (I assume anyway). I hope you understand it ok.

    Option Explicit On

    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            ''Example2
            myStoredProcedure({New HoldItems(Value:=333, SqlType:=SqlDbType.Int, Name:="@UserID"),
                              New HoldItems(Value:=9090, SqlType:=SqlDbType.Int, Name:="@ProfileID")})
        End Sub

        Public Sub myStoredProcedure(ByVal ParamArray _Params() As Object)
            Dim _class As HoldItems
            Dim intI As Integer

            For intI = 0 To UBound(_Params)
                _class = DirectCast(_Params(intI), HoldItems)

                Debug.Print("Name=" & _class.Name & " Value=" & _class.Value & " SqlType=" & _class.SqlType)
            Next intI
        End Sub
    End Class

    Public Class HoldItems
        Public Value As Integer
        Public SqlType As Integer
        Public Name As String

        Public Sub New(Value As Integer, SqlType As SqlDbType, Name As String)
            Me.Value = Value
            Me.SqlType = SqlType
            Me.Name = Name
        End Sub
    End Class

answered Feb 2, 2019 at 8:16

IAmNerd2000

7711 gold badge4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rbassett Over a year ago

Thanks @IAmNerd2000, I extended your idea by making HoldItems.Value into an object rather than specifically an integer, so that it can be used with any SqlDbType.

Collectives™ on Stack Overflow

Using ParamArray in VB.Net passing Object Or Array of Objects as Argument

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related