none
Insertar registros en bloques a distintas tablas con procedimientos almacenados y controlados por transacciones desde vb.net

    Question

  • Cordial Saludo.

    La verdad es que no se si la pregunta que voy a hacer es sencilla o complicada, he navegado por muchos foros buscando información acerca de mi caso y la verdad es que parece que a nadie en este mundo se le ha presentado mi situación porque todos dan respuestas de como usar procedimientos almacenados pero insertando un solo registro a la vez y yo necesito una inserción en bloque o en lote en distintas tablas 

    Estoy haciendo una aplicación visual basic.net, la aplicación se conecta a una base de datos de sql server 2008 r2 y necesito insertar varios registros a distintas tablas a través de procedimientos almacenados pero controlados por transacciones. les explico mas a fondo.

    Tengo una tabla de clientes, una tabla ventas, una tabla productos vendidos

    la relación de las tablas es la siguiente

    clientes --- ventas (a través del campo IdCliente)

    ventas --- productos vendidos(a través del campo IdVenta)

    el problema consiste en que si un cliente NUEVO realiza una compra de 250 distintos productos entonces la lógica debería ser así

    1. Inserto el registro del nuevo cliente en la tabla clientes

    2.Inserto el registro de la compra en la tabla ventas

    3. inserto los 250 distintos productos que el cliente compro en la tabla productos vendidos

    tengo un datatable en memoria con los 250 distintos productos comprados por el cliente, listos para ser guaradados en la tabla productos vendidos

    antes tenia una función que me ejecutaba un arraylist lleno de instrucciones sql y lo controlaba con transacciones, es decir hacia algo parecido a esto

    Dim Sentencias as new arraylist

    sentencias.add (insert into clientes (campos .....) values (valores...)

    sentencias.add (insert into ventas (campos .....) values (valores...)

    for i = 0 to datatable.rows.count -1

    sentencias.add (insert into productosvendidos (campos ...) values (datatable.rows(i).item("campo"), .....)

    next 

    después que tenia el arraylist lleno, llamaba a una funcion ejecutar que recorria todo el arraylist que le pasaba por valor y ejecutaba cada linea de sentencia sql y las controlaba con una transaccion, o se ejecutaban todas o ninguna.

    if ejecutar(sentencias) then

    mensaje (Todo correcto)

    end if 

    Pero ahora con procedimientos almacenados no se como, tengo los siguientes procedimientos almacenados 

    insertarcliente

    insertarventa

    insertarproductosvendidos

    pero como llamo a estos tres procedimientos almacenados y que se ejecuten en bloques y que sean controlados por una transacción desde visual basic, sobre todo cuando inserto los 250 productos en la tabla de productos vendidos, si llega a fallar algo, se deben deshacer todos los registros que hayan sido agregados a las distintas tablas, y no puedo colocar una transacción dentro de los procedimientos almacenados ya que cada vez que los llame van a confirmar la transacción, y si llegar a ocurrir un fallo van a quedar los registros incompletos en las tablas y eso NO DEBE SER ASI. la transacción solo se debe confirmar cuando las 252 lineas de sentencias sql estén ejecutadas y sin errores

    Por favor les pido que me ayuden, la verdad no se que hacer

    Gracias





    Sunday, November 18, 2012 5:21 AM

All replies

  • hola

    podrias crear una estructura como ser

    Using conn As New SqlConnection("<connection string>")
    	conn.Open()
    
    	Dim query As String = "insertarcliente"
    	Using cmd As New SqlCommand(query, conn)
    		cmd.CommandType = SqlCommandType.StoredProcedure 
    
    		cmd.Parameters.AddWithValue("@nombre", textbox1.Text)
    		cmd.Parameters.AddWithValue("@apellido", textbox2.Text)
    
    		cmd.ExecuteNonQuery()
    
    	End Using
    	
    	Dim query2 As String = "insertarventa"
    	Using cmd As New SqlCommand(query2, conn)
    		cmd.CommandType = SqlCommandType.StoredProcedure 
    		
    		cmd.Parameters.AddWithValue("@param1", textbox1.Text)
    		cmd.Parameters.AddWithValue("@param2", textbox2.Text)
    
    		cmd.ExecuteNonQuery()
    
    	End Using
    	
    	Dim query3 As String = "insertarproductosvendidos"
    	Using cmd As New SqlCommand(query3, conn)
    		cmd.CommandType = SqlCommandType.StoredProcedure 
    		
    		For Each row As DataGridViewRow In dataGridView1.Rows
    			cmd.Parameters.Clear()
    
    			cmd.Parameters.AddWithValue("@param1", Convert.ToString(row.Cells("Nombre").Value))
    			cmd.Parameters.AddWithValue("@param2", Convert.ToInt32(row.Cells("Apellido").Value))
    
    			cmd.ExecuteNonQuery()
    		Next
    	End Using
    	
    End Using

    como veras dentro de uan misma conexion puedes crear varios bloques para insertar en cada tabla, invocando a cada procedure

    saludos


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    Sunday, November 18, 2012 6:11 AM
  • Cordial Saludo Leandro.

    Gracias por responder tan pronto. 

    Revisando tu código, me doy cuenta que creas un nuevo comando por cada store procedure, le cargas los valores de los parámetros y ejecutas bajo la misma conexión abierta, y creo que si a esa conexión activa le creo una transacción pues funcionaria lo que quiero, el problema es que yo tengo creada una clase por cada tabla de la base de datos, y cada clase tiene sus propios métodos como es lógico. Lo que relaciona todos los métodos guardar de todas las clases es que heredan una función que se llama EJECUTAR, esta función lo que hace es que ella misma obtiene la colección de parámetros de cualquier store procedure a través de un sqlcommandbuilder.deriverparameters, y los métodos que heredan esta función lo único que hacen es que pasan los valores de esos parámetros a la función EJECUTAR y la misma función ejecutar es la que se encarga de ejecutar las sentencias sql. Te coloco el código los códigos y la secuencia de ejecución de los métodos 

    1. En la capa de negocios creo una nueva instancia a la clase clientes y le paso los datos a las propiedades de la clase

    Dim C As New Clientes
            C.Nit = Trim(TxtNit.Text).ToString
            C.NombreCliente = Trim(TxtNombreCliente.Text)
           C.Direccion = Trim(TxtDireccion.Text).ToString
            C.Telefonos = Trim(TxtTelefonos.Text).ToString
           ......

    Después que pase los datos llamo el método guardar

           If C.Guardar("Agregar") Then


            End If

    el método guardar consiste en esto

    Public Function Guardar(ByVal Accion As String) As Boolean
            Dim Band As Boolean = False
            Try
                If Accion = "Agregar" Then
                    Ejecutar("InsertarCliente", vNit, vNombreCliente, vDireccion, vTelefonos, ..... y asi todos los valores de los parametros del store procedure)

                End If
                If Accion = "Modificar" Then
                        Ejecutar("ModificarCliente", vNit, vNombreCliente, vDireccion, vTelefonos, ..... y asi todos los valores de los parametros del store procedure)
                End If
                Band = True
            Catch ex As Exception
                Band = False
            End Try
            Return Band
        End Function

    Al llamar al método guardar internamente el llama a la función Ejecutar

    Protected Function Ejecutar(ByVal Procedimiento As String, ByVal ParamArray Parametros() As Object) As DataTable
            Dim Tabla As DataTable = New DataTable
            Dim C As New Conexion
            Dim I, J As Integer
            Try
                If C.Conectar Then
                    Comando = New SqlCommand
                    Comando.CommandText = Procedimiento
                    Comando.CommandType = CommandType.StoredProcedure
                    Comando.Connection = Cn

                    SqlCommandBuilder.DeriveParameters(Comando)
                    J = 1
                    For I = 0 To Parametros.Length - 1
                        CType(Comando.Parameters(J), SqlParameter).Value = Parametros(I)
                        J = J + 1
                    Next
                    Comando.ExecuteNonQuery() 'Aqui ejecuto en instrucciones de inserccion actualizacion y eliminacion

                    'Esto codigo es para cuando realizo consultas me devuelva datos en forma de tabla para poder manipularlos en la capa de negocios o la clase que lo necesite

                    'Adaptador = New SqlDataAdapter
                    'Adaptador.SelectCommand = Comando
                    'Adaptador.Fill(Tabla)
                End If
            Catch ex As Exception

                Return Nothing
            End Try
            Return Tabla
        End Function

    esto funciona correctamente para cualquier clase y store procedure, la ventaja es que me evita estar colocando todos los parametros de todos los store procedure cada vez que quiera hacer alguna operación.  funciona pero para inserción o actualización o eliminación de un registro ala vez, pero lo que quiero es que funcione en bloque y lógicamente controlado por transacciones. 

    Gracias por tu tiempo

    Sunday, November 18, 2012 6:01 PM
  • Estamos a 19 de febrero de 2013 y aún estoy con la misma duda y parece que a nadie se le ocurrido nada, o es que no se puede hacer lo que pienso?.... la verdad es que me las he arreglado pero de otra forma que no es muy técnica, pero quisiera hacer lo que digo ya que es un poco mas profesional... Gracias
    • Proposed as answer by Dash34_12 Tuesday, January 07, 2014 4:02 AM
    • Unproposed as answer by Dash34_12 Tuesday, January 07, 2014 4:02 AM
    Tuesday, February 19, 2013 2:28 PM