Send parameters for SQL command as parameterized variable

Question

Send parameters for SQL command as parameterized variable

Navigation

#1 by (2 votes)

0

I need to send the parameters that I will use in a sql command, I know that it can be done with Dim paramater As New SqlParameter("@fptemp", serializedTemplate) but now the problem comes when I use a parameterizable function to execute the command, in this function I use the following:

Public Function StrInsert_and_Update_With_Parameters(ByVal Query As String, ByVal Parameters As SqlParameter)
Dim vl_S_processUpdate As String

Try
            'Iniciamos la conexión a la BD
            Dim objConexBD As New SqlConnection(ConectionString)

            'Abrimos conexión
            objConexBD.Open()
            'Creamos el comando a ejecutar y seleccionamos tipo de comando
            Dim objcmd As New SqlCommand(Query, objConexBD)
            objcmd.CommandType = CommandType.Text
            'Montamos los parametros el comando
            objcmd.Parameters.Add(Parameters)
            'Ejecutamos el CMD
            objcmd.ExecuteNonQuery()
            'Cerramos conexiones
            objConexBD.Close()

            vl_S_processUpdate = "Exito"

        Catch ex As Exception

            vl_S_processUpdate = "Error"
        End Try
        Return vl_S_processUpdate

    End Function

But from my function where I set up the command I do not know how to send it, here I have

 Dim sql As New StringBuilder

 sql.AppendLine("INSERT PERSONA (" & _
                "P_Documento, " & _
                "P_Nombre, " & _
                "P_Apellido, " & _
                "P_Usuario_Creacion, " & _
                "P_FechaCreacion, " & _
                "P_Usuario_Actualizacion, " & _
                "P_FechaActualizacion" & _
                ")")
sql.AppendLine("VALUES (")
        sql.AppendLine("@Documento, ")
        sql.AppendLine("@Nombre, ")
        sql.AppendLine("@Apellido, ")
        sql.AppendLine("'" & vp_Obj.UsuarioCreacion & "', ")
        sql.AppendLine("'" & vp_Obj.FechaCreacion & "', ")
        sql.AppendLine("'" & vp_Obj.UsuarioActualizacion & "', ")
        sql.AppendLine("'" & vp_Obj.FechaActualizacion & "' ) ")

Dim paramater As New SqlParameter("@Documento", DocumentPerson) <-- ESTO ES LO QUE NO SÉ CÓMO SE PUEDE ARMAR PARA ENVIARLO COMO PARÁMETRO

Query = sql.ToString

Result = conex.StrInsert_and_Update_With_Parameters(Query, paramater)

How could I do that ?, that is, put together a file of type SqlParameter to be able to send it as a parameter and use it on the other side.

sql sql-server vb.net ado.net

asked by Fabian Montoya 28.02.2017 в 21:20

source

1 answer

Help with basic Java function ggplot2 shows nothing or gives error

score 2 · Accepted Answer

You can accumulate your parameters in a list and pass the list to StrInsert_and_Update_With_Parameters :

Dim parametros As List(Of SqlParameter) = New List(Of SqlParameter)
parametros.Add(New SqlParameter("@Documento", DocumentPerson))
' agregar otros parámetros aquí

Query = sql.ToString

Result = conex.StrInsert_and_Update_With_Parameters(Query, parametros)

You should modify the declaration of the StrInsert_and_Update_With_Parameters method to accept a list of parameters, for example:

Public Function StrInsert_and_Update_With_Parameters(Query As String, parametros As IEnumerable(Of SqlParameter)) As String

And within the method, instead of:

objcmd.Parameters.Add(Parameters)

... you can do:

objcmd.Parameters.AddRange(parametros.ToArray())