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






Show quoteHide quote

Set PrivateFont in browser
richtextbox changing fontstyles for selected text
is it possible to get delegates from properties directly?
control types
"Global" objects
Parsing the appended data of log file in real time
Copying A Control Not As A Reference
XML-RPC
Tabindex is driving me nuts!!!
Screen coordinates of selected datagridview cells left and top borders