A function that returns an array of SqlClient.SqlParameter objects

  • Thread starter Thread starter BobRoyAce
  • Start date Start date
B

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.SqlParameter

SQLParam(0) = New System.Data.SqlClient.SqlParameter
With SQLParam(0)
.ParameterName = "@pkUserIDOriginal"
.DbType = DbType.Int32
.Direction = ParameterDirection.Input
.Value = _pkUserID
End With
..
..
..

I then pass this array of SqlClient.SqlParameter 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)?"
 
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.Parameters.Add("@In_ID", SqlDbType.Integer).Value = m_ID
theCommand.Parameters(("@In_ID").Direction =
ParameterDirection.Input

theCommand.Parameters.Add("@In_Name", SqlDbType.String).Value =
m_Name
theCommand.Parameters(("@In_Name").Direction =
ParameterDirection.Input

End Sub




' Read values from a data reader into class fields.

Public Sub Read ( ByVal theReader As SqlDataReader )

m_ID = theReader.GetInt32 ( PositionsEnum.ID )
m_Name = theReader.GetString ( PositionsEnum.Name )

End Sub



End Class
 
Back
Top