Passing a parameter Collection

20 Jun 2006 5:19 PM

John Wright

I want to create a generic data layer that uses Oracle as the back end. By
generic, I just want a couple of procedures. All access will be done with
stored procs, and I want one that returns a dataset and one that returns a
datareader. I want to pass in the commandName, and a ParameterArray that
holds the parameters I want to populate:

Public Shared Function ExeCmd(ByVal CommandName As String, ByVal CmdParam as
OracleParameterCollection) as DataSet
        Using z as new OracleCommand
            with x
                .CommandType = StoredProc
                .CommandText = CommandName

                'I want to iterate through the parameter collection and add
each parameter here
            end with

        End Using

End Function

How can I apply the ParameterCollection to the command?

John

21 Jun 2006 2:16 PM

Brian Tkatch

John Wright wrote:
Show quoteHide quote

> I want to create a generic data layer that uses Oracle as the back end. By
> generic, I just want a couple of procedures. All access will be done with
> stored procs, and I want one that returns a dataset and one that returns a
> datareader. I want to pass in the commandName, and a ParameterArray that
> holds the parameters I want to populate:
>
> Public Shared Function ExeCmd(ByVal CommandName As String, ByVal CmdParam as
> OracleParameterCollection) as DataSet
>         Using z as new OracleCommand
>             with x
>                 .CommandType = StoredProc
>                 .CommandText = CommandName
>
>                 'I want to iterate through the parameter collection and add
> each parameter here
>             end with
>
>         End Using
>
> End Function
>
> How can I apply the ParameterCollection to the command?
>
> John

I'm not sure how to do it in Oracle, but here's what i do for DB2.

Public Function Execute_Command(ByVal Command As DB2Command, ByVal
Parameters() As DB2Parameter, ByVal With_Return As Boolean) As
DB2DataReader

        Dim Parameter As DB2Parameter

        If Not Parameters Is Nothing Then

            For Each Parameter In Parameters

                ' Only add it if something is actually there.
                If Not Parameter Is Nothing Then
Command.Parameters.Add(Parameter)

            Next Parameter

        End If ' Not Parameters Is Nothing

        ' The command is ready. so, execute it, and grab the output, if
any.
        ' The information cannot be controlled by this statement, so
catch errors.
        Try

            If With_Return Then

                Return Command.ExecuteReader

            Else

                Command.ExecuteNonQuery()

            End If

        Catch The_Exception As Exception

            Report_error(The_Exception)

        End Try

    End Function

B.