Passing a parameter Collection

[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
OracleParameter Collection) 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 ParameterCollec tion to the command?

John

[Brian Tkatch]

Re: Passing a parameter Collection


John Wright wrote:[color=blue]
> 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
> OracleParameter Collection) 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 ParameterCollec tion to the command?
>
> John[/color]

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.Paramet ers.Add(Paramet er)

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.Execute Reader

Else

Command.Execute NonQuery()

End If

Catch The_Exception As Exception

Report_error(Th e_Exception)

End Try

End Function

B.