Inversiones/DAO/DAONotaEstructurada.vb

Imports System.Data.SqlClient


Public Class DAONotaEstructurada


    Function Nuevo(ByVal oNESTCE As NotaEstructuradaCE) As Integer
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        sql = "INSERT INTO [dbo].[NEST0]
           ([CodInv]
           ,[MontoInv]  
           ,[FOper]
           ,[FVenc]
           ,[Tasa]
           ,[Plazo]
           --,[AnBase]
           ,[Periodic])
     VALUES
           (@CodInv
           ,@MontoInv
           ,@FOper
           ,@FVenc
           ,@Tasa
           ,@Plazo
           --,@AnBase
           ,@Periodic)
"

        cmd = New SqlCommand
        cmd.Connection = cn
        cmd.CommandText = sql

        With cmd.Parameters
            .Add("@CodInv", SqlDbType.VarChar).Value = oNESTCE.CodigoInversion
            .Add("@MontoInv", SqlDbType.Float).Value = oNESTCE.MontoDeInversion
            .Add("@FOper", SqlDbType.Date).Value = oNESTCE.FechaOperacion
            .Add("@FVenc", SqlDbType.Date).Value = oNESTCE.FechaVencimiento
            .Add("@Tasa", SqlDbType.Float).Value = oNESTCE.Tasa
            .Add("@Plazo", SqlDbType.Int).Value = oNESTCE.Plazo
            '.Add("@AnBase",SqlDbType.VarChar).Value = oDAPCE.CodigoInversion
            .Add("@Periodic", SqlDbType.VarChar).Value = oNESTCE.Periodicidad
        End With

        retorno = cmd.ExecuteNonQuery

        cmd.Dispose()
        cn.Dispose()

        GuardaIntereses(oNESTCE.CodigoInversion, oNESTCE.dtIntereses)

        Return retorno
    End Function
    Function Nuevo(ByVal oNEST As NotaEstructuradaCE, DocId As Integer, Tipo As String) As Integer
        Dim objCon As New Conexion
        Dim retorno As Integer = -1
        Dim Transaccion As SqlTransaction
        Dim query As String
        Dim Tabla As String = ""
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        If Tipo = "P" Then
            Tabla = "PDAP0"
        End If
        If Tipo = "I" Then
            Tabla = "IDAP0"
        End If

        Dim cmd As SqlCommand = cn.CreateCommand

        Transaccion = cn.BeginTransaction("NuevoDAP")
        cmd.Connection = cn
        cmd.Transaction = Transaccion

        query = "INSERT INTO [dbo].[" & Tabla & "]
           ([DocId]
           ,[MontoInv]
           ,[FOper]
           ,[FVenc]
           ,[Tasa]
           ,[Plazo]
           ,[AnBase]
           ,[Periodic]
           ,[RendBruto]
           ,[RendNeto]
           ,[IngrBruto]
           ,[Impuestos]
           ,[IngrNeto])
     VALUES
           (@DocId
           ,@MontoInv
           ,@FOper
           ,@FVenc
           ,@Tasa
           ,@Plazo
           ,@AnBase
           ,@Periodic
           ,@RendBruto
           ,@RendNeto
           ,@IngrBruto
           ,@Impuestos
           ,@IngrNeto
		  )"

        cmd.CommandText = query

        With cmd.Parameters
            .Add("@DocId", SqlDbType.Int).Value = DocId
            .Add("@MontoInv", SqlDbType.Decimal).Value = oNEST.MontoDeInversion
            .Add("@FOper", SqlDbType.Date).Value = oNEST.FechaOperacion
            .Add("@FVenc", SqlDbType.Date).Value = oNEST.FechaVencimiento
            .Add("@Tasa", SqlDbType.Decimal).Value = oNEST.Tasa
            .Add("@Plazo", SqlDbType.Int).Value = oNEST.Plazo
            .Add("@AnBase", SqlDbType.Int).Value = oNEST.AnioBase
            .Add("@Periodic", SqlDbType.NVarChar).Value = oNEST.Periodicidad
            .Add("@RendBruto", SqlDbType.Decimal).Value = oNEST.RendimientoBruto
            .Add("@RendNeto", SqlDbType.Decimal).Value = oNEST.RendimientoNeto
            .Add("@IngrBruto", SqlDbType.Decimal).Value = oNEST.IngresoBruto
            .Add("@Impuestos", SqlDbType.Decimal).Value = oNEST.Impuestos
            .Add("@IngrNeto", SqlDbType.Decimal).Value = oNEST.IngresoNeto
        End With

        Try
            cmd.ExecuteNonQuery()
            Transaccion.Commit()
            retorno = 0
        Catch ex As Exception
            MsgBox("Error al Guardar DAP : " & ex.GetType.ToString)
            MsgBox("Mensaje: " & ex.Message)
            retorno = 1
            Try
                Transaccion.Rollback()
                retorno = 2
            Catch ex2 As Exception
                MsgBox("Error en Rollback: " & ex2.GetType.ToString)
                MsgBox("Mensaje Rollbak: " & ex2.Message)
                retorno = 3
            End Try
        End Try

        cmd.Dispose()
        cn.Dispose()


        Return retorno
    End Function

    Public Sub CargarRegistro(ByVal oNEST As NotaEstructuradaCE, Tipo As String, IdRegistro As Integer)
        Dim objCon As New Conexion
        Dim drd As SqlDataReader
        Dim cmd As SqlCommand
        Dim sql As String
        Dim Tabla As String
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        If Tipo = "P" Then
            Tabla = "PDAP0"
        End If
        If Tipo = "I" Then
            Tabla = "IDAP0"
        End If

        sql = "SELECT * FROM [dbo].[" & Tabla & "] T0 WHERE T0.[DocId]=@DocId"


        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@DocId", SqlDbType.Int).Value = IdRegistro
        cmd.Connection = cn

        drd = cmd.ExecuteReader

        If drd.HasRows Then
            'SE ENCONTRO EL REGISTRO
            If drd.Read Then
                oNEST.MontoDeInversion = drd.Item("MontoInv")
                oNEST.FechaOperacion = drd.Item("FOper")
                oNEST.FechaVencimiento = drd.Item("FVenc")
                oNEST.Tasa = drd.Item("Tasa")
                oNEST.Plazo = drd.Item("Plazo")
                oNEST.AnioBase = drd.Item("AnBase")
                oNEST.Periodicidad = drd.Item("Periodic")
                'oDAP.RendimientoBruto = drd.Item("RendBruto")
                'oDAP.RendimientoNeto = drd.Item("RendNeto")
                oNEST.IngresoBruto = drd.Item("IngrBruto")
                oNEST.Impuestos = drd.Item("Impuestos")
                oNEST.IngresoNeto = drd.Item("IngrNeto")
            End If
        Else
            'NO SE ENCONTRO EL REGISTRO
        End If
        drd.Close()
        cmd.Dispose()
        cn.Dispose()

    End Sub
    Public Function CargarRegistro(ByVal vCodigoInversion As String) As NotaEstructuradaCE
        Dim objCon As New Conexion
        Dim oNESTCE As New NotaEstructuradaCE
        Dim drd As SqlDataReader
        Dim cmd As SqlCommand
        Dim da As SqlDataAdapter
        Dim sql As String
        Dim dtIntereses As New DataTable
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        'INTERESES
        sql = "SELECT * FROM [dbo].[NEST1] T0 WHERE T0.CodInv=@CodInv"
        da = New SqlDataAdapter(sql, cn)
        da.SelectCommand.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = vCodigoInversion
        da.Fill(dtIntereses)
        oNESTCE.dtIntereses = dtIntereses

        sql = "SELECT * FROM [dbo].[NEST0] T0 WHERE T0.CodInv=@CodInv"
        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = vCodigoInversion
        cmd.Connection = cn

        drd = cmd.ExecuteReader

        If drd.HasRows Then
            'SE ENCONTRO EL REGISTRO
            If drd.Read Then
                oNESTCE.CodigoInversion = vCodigoInversion.ToString
                oNESTCE.MontoDeInversion = drd.Item("MontoInv").ToString
                oNESTCE.FechaOperacion = drd.Item("FOper").ToString
                oNESTCE.FechaVencimiento = drd.Item("FVenc").ToString
                oNESTCE.Tasa = drd.Item("Tasa").ToString
                oNESTCE.Plazo = drd.Item("Plazo").ToString
                oNESTCE.Periodicidad = drd.Item("Periodic").ToString

            End If
            drd.Close()

        Else
            'NO ENCONTRO EL REGISTRO
        End If
        cmd.Dispose()
        cn.Dispose()

        Return oNESTCE

    End Function

    Public Function Actualizar(ByVal oNESTCE As NotaEstructuradaCE) As Integer
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim retorno As Integer
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        sql = "UPDATE [dbo].[NEST0]
   SET [MontoInv] = @MontoInv
      ,[FOper] = @FOper
      ,[FVenc] = @FVenc
      ,[Tasa] = @Tasa
      ,[Plazo] = @Plazo
      --,[AnBase] = @AnBase
      ,[Periodic] = @Periodic
 WHERE [CodInv] = @CodInv"

        cmd = New SqlCommand
        cmd.CommandText = sql



        With cmd.Parameters
            .Add("@MontoInv", SqlDbType.Float).Value = oNESTCE.MontoDeInversion
            .Add("@FOper", SqlDbType.Date).Value = oNESTCE.FechaOperacion
            .Add("@FVenc", SqlDbType.Date).Value = oNESTCE.FechaVencimiento
            .Add("@Tasa", SqlDbType.Float).Value = oNESTCE.Tasa
            .Add("@Plazo", SqlDbType.Int).Value = oNESTCE.Plazo
            .Add("@Periodic", SqlDbType.VarChar).Value = oNESTCE.Periodicidad
        End With

        cmd.Connection = cn
        retorno = cmd.ExecuteNonQuery

        cmd.Dispose()
        cn.Dispose()

        GuardaIntereses(oNESTCE.CodigoInversion, oNESTCE.dtIntereses)

        Return retorno
    End Function
    Private Sub GuardaIntereses(ByVal vCodInversion As String, dt As DataTable)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        Dim res As Integer

        sql = "DELETE FROM [dbo].[NEST1]
      WHERE [CodInv]=@CodInv"

        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = vCodInversion


        cmd.Connection = cn
        res = cmd.ExecuteNonQuery
        sql = String.Empty

        If dt.Rows.Count > 0 Then
            For Each dr In dt.Rows
                cmd = New SqlCommand
                sql = "INSERT INTO [dbo].[NEST1]
           ([CodInv]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]
           ,[Estado])
     VALUES
           (@CodInv
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto
           ,@Estado)"

                cmd.CommandText = sql
                With cmd.Parameters
                    .AddWithValue("@CodInv", vCodInversion)
                    .AddWithValue("@Plazo", dr("Plazo"))
                    .AddWithValue("@Fecha", dr("Fecha"))
                    .AddWithValue("@IngrBruto", dr("IngrBruto"))
                    .AddWithValue("@PorcImp", dr("PorcImp"))
                    .AddWithValue("@MontoImp", dr("MontoImp"))
                    .AddWithValue("@IngrNeto", dr("IngrNeto"))
                    .AddWithValue("@Estado", dr("Estado"))
                End With
                cmd.Connection = cn
                res = cmd.ExecuteNonQuery
            Next
        End If

        cmd.Dispose()
        cn.Dispose()

    End Sub



    Public Function NuevoDato(ByVal Coleccion As Collection, ByVal Diccionario As Dictionary(Of String, Collection), ByVal Codigo As String)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        myTrans = cn.BeginTransaction()
        Try
            sql = "INSERT INTO [dbo].[NEST0](
           [CodInv]
           ,[MontoInv]
           ,[FOper]
           ,[FVenc]
           ,[Tasa]
           ,[Plazo]
           ,[Periodic]
           ,[RendBr]
           ,[RendNet]   
           ,[IngrBrutoTot]
           ,[IngrNetoTot]
           ,[Renta]


) VALUES(

           @CodInv
           ,@MontoInv
           ,@FOper
           ,@FVenc
           ,@Tasa
           ,@Plazo
           ,@Periodic
           ,@RendBr
           ,@RendNet
           ,@IngrBrutoTot
           ,@IngrNetoTot
           ,@Renta
)
    "
            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn






            cmd.Transaction = myTrans
            With cmd.Parameters
                .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@MontoInv", SqlDbType.Decimal).Value = Coleccion(1)
                .Add("@FOper", SqlDbType.DateTime).Value = Coleccion(2)
                .Add("@FVenc", SqlDbType.DateTime).Value = Coleccion(3)
                .Add("@Tasa", SqlDbType.Decimal).Value = Coleccion(4)
                .Add("@Plazo", SqlDbType.Int).Value = Coleccion(5)
                .Add("@Periodic", SqlDbType.VarChar).Value = Coleccion(6)
                .Add("@RendBr", SqlDbType.VarChar).Value = Coleccion(7)
                .Add("@RendNet", SqlDbType.VarChar).Value = Coleccion(8)

                .Add("@IngrBrutoTot", SqlDbType.Float).Value = Coleccion(9)
                .Add("@IngrNetoTot", SqlDbType.Float).Value = Coleccion(10)
                .Add("@Renta", SqlDbType.Float).Value = Coleccion(11)
            End With

            retorno = cmd.ExecuteNonQuery


            sql = "INSERT INTO [dbo].[NEST1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "




            If Not Diccionario Is Nothing Then


                For Each DatosDiccionario In Diccionario
                    Dim Coleccion2 As Collection = DatosDiccionario.Value


                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    With cmd.Parameters
                        .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                        .Add("@Correlativo", SqlDbType.Int).Value = Coleccion2(1)
                        .Add("@Plazo", SqlDbType.Int).Value = Coleccion2(2)
                        .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion2(3)
                        .Add("@IngrBruto", SqlDbType.Decimal).Value = Coleccion2(4)
                        .Add("@PorcImp", SqlDbType.Decimal).Value = Coleccion2(5)
                        .Add("@MontoImp", SqlDbType.Decimal).Value = Coleccion2(6)
                        .Add("@IngrNeto", SqlDbType.Decimal).Value = Coleccion2(7)
                    End With

                    retorno = cmd.ExecuteNonQuery





                Next


            End If





            myTrans.Commit()
            MsgBox("Datos Ingresados")
        Catch ex As Exception
            myTrans.Rollback()
            MsgBox(ex.Message)
        End Try
        cn.Close()

        Return retorno
    End Function










    Public Function NuevoElemento(ByVal Coleccion As Collection, ByVal Codigo As String)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        sql = "INSERT INTO [dbo].[NEST1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "





        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters



            .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
            .Add("@Correlativo", SqlDbType.Int).Value = Coleccion(1)
            .Add("@Plazo", SqlDbType.Int).Value = Coleccion(2)
            .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion(3)
            .Add("@IngrBruto", SqlDbType.Decimal).Value = Coleccion(4)
            .Add("@PorcImp", SqlDbType.Decimal).Value = Coleccion(5)
            .Add("@MontoImp", SqlDbType.Decimal).Value = Coleccion(6)
            .Add("@IngrNeto", SqlDbType.Decimal).Value = Coleccion(7)
        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function



    Public Function ModificarElemento(ByVal Coleccion As Collection, ByVal Codigo As String)
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        sql = "UPDATE  [dbo].[NEST1] SET

           [Plazo]=@Plazo
           ,[Fecha]=@Fecha
           ,[IngrBruto]=@IngrBruto
           ,[PorcImp]=@PorcImp
           ,[MontoImp]=@MontoImp
           ,[IngrNeto]=@IngrNeto
            where  [CodInv]=@CodInv AND [Correlativo]=@Correlativo

    "



        cmd = New SqlCommand
        cmd.CommandText = sql

        With cmd.Parameters



            .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
            .Add("@Correlativo", SqlDbType.Int).Value = Coleccion(1)
            .Add("@Plazo", SqlDbType.Int).Value = Coleccion(2)
            .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion(3)
            .Add("@IngrBruto", SqlDbType.Decimal).Value = Coleccion(4)
            .Add("@PorcImp", SqlDbType.Decimal).Value = Coleccion(5)
            .Add("@MontoImp", SqlDbType.Decimal).Value = Coleccion(6)
            .Add("@IngrNeto", SqlDbType.Decimal).Value = Coleccion(7)
        End With


        cmd.Connection = cn

        retorno = cmd.ExecuteNonQuery

        cn.Close()
        Return retorno

    End Function


    Public Sub EliminarElemento(ByVal Codigo As String)
        Dim objCon As New Conexion
        Dim sql As String
        Dim cmd As SqlCommand
        Dim res As Integer
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If
        sql = "DELETE FROM [dbo].[NEST1]
      WHERE [CodInv]=@CodInv "



        cmd = New SqlCommand
        cmd.CommandText = sql
        cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo

        cmd.Connection = cn

        res = cmd.ExecuteNonQuery
        cn.Close()
    End Sub










    Public Function ModificarDato(ByVal Coleccion As Collection, ByVal Codigo As String, ByVal Diccionario As Dictionary(Of String, Collection))
        Dim objCon As New Conexion
        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        myTrans = cn.BeginTransaction()
        Try
            sql = "UPDATE [dbo].[NEST0] SET
            [MontoInv]=@MontoInv
           ,[FOper]=@FOper
           ,[FVenc]=@FVenc
           ,[Tasa]=@Tasa
           ,[Plazo]=@Plazo
           ,[Periodic]=@Periodic
           ,[RendBr]=@RendBr
           ,[RendNet]=@RendNet
            ,[IngrBrutoTot]=@IngrBrutoTot
            ,[IngrNetoTot]=@IngrNetoTot
            ,[Renta]=@Renta
            where [CodInv]=  @CodInv
    "
            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn






            cmd.Transaction = myTrans
            With cmd.Parameters
                .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                .Add("@MontoInv", SqlDbType.Decimal).Value = Coleccion(1)
                .Add("@FOper", SqlDbType.DateTime).Value = Coleccion(2)
                .Add("@FVenc", SqlDbType.DateTime).Value = Coleccion(3)
                .Add("@Tasa", SqlDbType.Decimal).Value = Coleccion(4)
                .Add("@Plazo", SqlDbType.Int).Value = Coleccion(5)
                .Add("@Periodic", SqlDbType.VarChar).Value = Coleccion(6)
                .Add("@RendBr", SqlDbType.VarChar).Value = Coleccion(7)
                .Add("@RendNet", SqlDbType.VarChar).Value = Coleccion(8)

                .Add("@IngrBrutoTot", SqlDbType.Float).Value = Coleccion(9)
                .Add("@IngrNetoTot", SqlDbType.Float).Value = Coleccion(10)
                .Add("@Renta", SqlDbType.Float).Value = Coleccion(11)
            End With
            retorno = cmd.ExecuteNonQuery


            sql = "INSERT INTO [dbo].[NEST1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "

            sql = "delete from NEST1 Where CodInv='" + Codigo + "'"
            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn
            cmd.Transaction = myTrans
            retorno = cmd.ExecuteNonQuery
            If Not Diccionario Is Nothing Then


                For Each DatosDiccionario In Diccionario
                    Dim Coleccion2 As Collection = DatosDiccionario.Value
                    sql = "INSERT INTO [dbo].[NEST1](
           [CodInv]
           ,[Correlativo]
           ,[Plazo]
           ,[Fecha]
           ,[IngrBruto]
           ,[PorcImp]
           ,[MontoImp]
           ,[IngrNeto]


) VALUES(

           @CodInv
           ,@Correlativo
           ,@Plazo
           ,@Fecha
           ,@IngrBruto
           ,@PorcImp
           ,@MontoImp
           ,@IngrNeto

)
    "

                    cmd = New SqlCommand
                    cmd.CommandText = sql
                    cmd.Connection = cn
                    cmd.Transaction = myTrans
                    With cmd.Parameters
                        .Add("@CodInv ", SqlDbType.VarChar).Value = Codigo.ToString
                        .Add("@Correlativo", SqlDbType.Int).Value = Coleccion2(1)
                        .Add("@Plazo", SqlDbType.Int).Value = Coleccion2(2)
                        .Add("@Fecha", SqlDbType.DateTime).Value = Coleccion2(3)
                        .Add("@IngrBruto", SqlDbType.Decimal).Value = Coleccion2(4)
                        .Add("@PorcImp", SqlDbType.Decimal).Value = Coleccion2(5)
                        .Add("@MontoImp", SqlDbType.Decimal).Value = Coleccion2(6)
                        .Add("@IngrNeto", SqlDbType.Decimal).Value = Coleccion2(7)
                    End With

                    retorno = cmd.ExecuteNonQuery





                Next


            End If





            myTrans.Commit()
            MsgBox("Datos Ingresados")
        Catch ex As Exception
            myTrans.Rollback()
            MsgBox(ex.Message)
        End Try
        cn.Close()

        Return retorno












































    End Function



    'Public Sub EliminarDato(ByVal Codigo As String)
    '    Dim sql As String
    '    Dim cmd As SqlCommand
    '    Dim res As Integer

    '    sql = "DELETE FROM [dbo].[DAP0]
    '  WHERE [CodInv]=@CodInv"



    '    cn = objCon.Conectar

    '    If Not cn.State = ConnectionState.Open Then
    '        cn.Open()
    '    End If

    '    cmd = New SqlCommand
    '    cmd.CommandText = sql
    '    cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo
    '    cmd.Connection = cn

    '    res = cmd.ExecuteNonQuery

    'End Sub



    Public Sub Eliminar(ByVal Codigo As String)

        Dim objCon As New Conexion



        Dim retorno As Integer
        Dim sql As String
        Dim cmd As SqlCommand
        Dim myTrans As SqlTransaction
        Dim cn As SqlConnection = objCon.Conectar
        If cn.State = ConnectionState.Closed Then
            cn.Open()
        End If

        myTrans = cn.BeginTransaction()
        Try

            sql = "DELETE FROM [dbo].[NEST0]
      WHERE [CodInv]=@CodInv"


            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn
            cmd.Transaction = myTrans
            cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


            retorno = cmd.ExecuteNonQuery



            sql = "DELETE FROM [dbo].[NEST1]
      WHERE [CodInv]=@CodInv"


            cmd = New SqlCommand
            cmd.CommandText = sql
            cmd.Connection = cn
            cmd.Transaction = myTrans
            cmd.Parameters.Add("@CodInv", SqlDbType.VarChar).Value = Codigo


            retorno = cmd.ExecuteNonQuery

            myTrans.Commit()
            MsgBox("Datos Eliminados")
        Catch ex As Exception
            myTrans.Rollback()
            MsgBox(ex.Message)
        End Try
        cn.Close()

    End Sub


End Class