A function that returns an array of SqlClient.SqlPa rameter objects

[BobRoyAce]

I have a class that has several Subs that do DB things, some of which
require the same set of parameters to be passed to a stored procedure.
One class has 12 parameters and part of code used to create them is
shown below:

Dim SQLParam(12) As SqlClient.SqlPa rameter

SQLParam(0) = New System.Data.Sql Client.SqlParam eter
With SQLParam(0)
.ParameterName = "@pkUserIDOrigi nal"
.DbType = DbType.Int32
.Direction = ParameterDirect ion.Input
.Value = _pkUserID
End With
..
..
..

I then pass this array of SqlClient.SqlPa rameter objects to the command
to execute the stored procedure. Well, my question is "How do I create
a function that returns this array that I could call from all Subs that
need it (rather than having the same code in multiple places)?"

[RMT]

I decided to have a "Read" and "Write" method in each class that has more or
less got a strong relationship with a record in the database. The read
method takes a data reader and reads the values into class variables, the
write method takes a command object and writes the class values into
parameters. I don't feel comfortable storing and manipulating parameter
collection objects or passing them around.


Public Class MyDBRecord

' Enumerate field positions for records returned

Private Enum PositionsEnum
ID = 0
Name
End Enum

' Example, an integer primary key and string

Private m_ID As Integer
Private m_Name As String

' Write values to the command object, ready for a stored procedure call.

Public Sub Write ( ByVal theCommand As SqlCommand )

theCommand.Para meters.Add("@In _ID", SqlDbType.Integ er).Value = m_ID
theCommand.Para meters(("@In_ID ").Directio n =
ParameterDirect ion.Input

theCommand.Para meters.Add("@In _Name", SqlDbType.Strin g).Value =
m_Name
theCommand.Para meters(("@In_Na me").Directio n =
ParameterDirect ion.Input

End Sub


' Read values from a data reader into class fields.

Public Sub Read ( ByVal theReader As SqlDataReader )

m_ID = theReader.GetIn t32 ( PositionsEnum.I D )
m_Name = theReader.GetSt ring ( PositionsEnum.N ame )

End Sub

End Class


"BobRoyAce" <br**@omegasoft wareinc.com> wrote in message
news:11******** **************@ m38g2000cwc.goo glegroups.com.. .

I have a class that has several Subs that do DB things, some of which
require the same set of parameters to be passed to a stored procedure.
One class has 12 parameters and part of code used to create them is
shown below:

Dim SQLParam(12) As SqlClient.SqlPa rameter

SQLParam(0) = New System.Data.Sql Client.SqlParam eter
With SQLParam(0)
.ParameterName = "@pkUserIDOrigi nal"
.DbType = DbType.Int32
.Direction = ParameterDirect ion.Input
.Value = _pkUserID
End With
.
.
.

I then pass this array of SqlClient.SqlPa rameter objects to the command
to execute the stored procedure. Well, my question is "How do I create
a function that returns this array that I could call from all Subs that
need it (rather than having the same code in multiple places)?"