Search code examples
.nettransactionsconnectionusing

Multiple using statement with same connection/transaction... - .NET


I want to do 2 methods, one to do the insert of only one register, and other to do the insert os many registers (a list).

But i want the second method to call the first method, just to use the same INSERT code.

Is there a way to use the connection opened in the second method, in the first method, so i can call a rollback for all?

Shared Sub Gravar(ByRef Pessoa As Pessoa)
    Try
        Using con As New myConnection
            con.Open()
            If Pessoa.Id = -1 Then
                Insert(Pessoa, con)
            Else
                Update(Pessoa, con)
            End If
        End Using
    Catch ex As Exception
        Throw
    End Try
End Sub

Shared Sub Gravar(ByRef Pessoas As List(Of Pessoa))
    Try
        Using con As New myConnection
            con.Open()
            con.OpenTransaction()
            For Each Pessoa As Pessoa In Pessoas
                Gravar(Pessoa)
            Next
            con.Commit()
        End Using
    Catch ex As Exception
        Throw
    End Try
End Sub

Thanks


Solution

  • Does something like this work - the single and multiple methods both create their connections and pass the connection to a 3rd method that does the work:

    Shared Sub Gravar(ByRef Pessoa As Pessoa)
        Try
            Using con As New myConnection
                con.Open()
                _Gravar(Pessoa, con)
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub
    
    Shared Sub Gravar(ByRef Pessoas As List(Of Pessoa))
        Try
            Using con As New myConnection
                con.Open()
                con.OpenTransaction()
                For Each Pessoa As Pessoa In Pessoas
                    _Gravar(Pessoa, con)
                Next
                con.Commit()
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Sub
    
    Private Shared Sub _Gravar(ByRef Pessoa As Pessoa, ByVal con As myConnection)
        Try
            If Pessoa.Id = -1 Then
                Insert(Pessoa, con)
            Else
                Update(Pessoa, con)
            End If
        Catch ex As Exception
            Throw
        End Try
    End Sub