none
Procedimento em Transact RRS feed

  • Pergunta

  • Prezados, boa tarde.

    Mais uma vez preciso de uma ajuda de vocês...

    Gostaria de saber se posso chamar procedimentos dentro de uma Transact. Por exemplo, tenho um comando que grava o registro principal, outro que grava o rateio deste registro, outro que grava a contabilizacao deste registro e outro que grava o rateio da contabilização. Criei um para cada etapa porque são utilizados em combinações diferentes. 


    É possível eu chamar o procedimento dentro da transação?

    agradeço a todos....

    Abraço


    sexta-feira, 24 de janeiro de 2014 14:14

Todas as Respostas

  • jkjhon

    é possível sim, segue um método de escopo de transação. , mas consulte também o link abaixo para tirar mais dúvidas.

    http://msdn.microsoft.com/en-us/library/ms172152(v=vs.85).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

    Public Function CreateTransactionScope( _
      ByVal connectString1 As String, ByVal connectString2 As String, _
      ByVal commandText1 As String, ByVal commandText2 As String) As Integer
    
    
        Dim returnValue As Integer = 0
        Dim writer As System.IO.StringWriter = New System.IO.StringWriter
    
        Using scope As New TransactionScope()
            Using connection1 As New SqlConnection(connectString1)
                Try
                    ' Opening the connection automatically enlists it in the 
                    ' TransactionScope as a lightweight transaction.
                    connection1.Open()
    
                    ' Create the SqlCommand object and execute the first command.
                    Dim command1 As SqlCommand = New SqlCommand(commandText1, connection1)
                    returnValue = command1.ExecuteNonQuery()
                    writer.WriteLine("Rows to be affected by command1: {0}", returnValue)
    
                    ' If you get here, this means that command1 succeeded. By nesting
                    ' the using block for connection2 inside that of connection1, you
                    ' conserve server and network resources as connection2 is opened
                    ' only when there is a chance that the transaction can commit.   
                     Using connection2 As New SqlConnection(connectString2)
                        Try
                            ' The transaction is escalated to a full distributed
                            ' transaction when connection2 is opened.
                            connection2.Open()
    
                            ' Execute the second command in the second database.
                            returnValue = 0
                            Dim command2 As SqlCommand = New SqlCommand(commandText2, connection2)
                            returnValue = command2.ExecuteNonQuery()
                            writer.WriteLine("Rows to be affected by command2: {0}", returnValue)
    
                        Catch ex As Exception
                            ' Display information that command2 failed.
                            writer.WriteLine("returnValue for command2: {0}", returnValue)
                            writer.WriteLine("Exception Message2: {0}", ex.Message)
                        End Try
                    End Using
    
                Catch ex As Exception
                    ' Display information that command1 failed.
                    writer.WriteLine("returnValue for command1: {0}", returnValue)
                    writer.WriteLine("Exception Message1: {0}", ex.Message)
                End Try
            End Using
    
            ' The Complete method commits the transaction. If an exception has been thrown,
            ' Complete is called and the transaction is rolled back.
            scope.Complete()
        End Using
    
        ' The returnValue is greater than 0 if the transaction committed.
        If returnValue > 0 Then
            writer.WriteLine("Transaction was committed.")
        Else
           ' You could write additional business logic here, for example, you can notify the caller 
           ' by throwing a TransactionAbortedException, or logging the failure.
           writer.WriteLine("Transaction rolled back.")
         End If
    
        ' Display messages.
        Console.WriteLine(writer.ToString())
    
        Return returnValue
    End Function

    sexta-feira, 24 de janeiro de 2014 16:24
  • Prezado Alexandre, boa noite.

    obrigado pela resposta, mas infelizmente eu não consegui solucionar o meu problema, e nem sei se me expressei bem para descrever o problema de forma clara. É prossível em uma trasaction chamar por exemplo

    call grava_rateio.trasaction

    call grava_contabilizacao.transaction

    ?


    private sub grava_rateio
    
        Dim sql_rateio As String
                For i = 0 To dgv_rateio.Rows.Count - 1
    
                    sql_rateio = "INSERT INTO f_rateio(id, cod_empr, codconta, valor) values(" & _
                        numero_movimento & "," & _
                        Me.cod_empr.Text & ",'" & _
                        dgv_rateio.Item(0, i).Value & "','" & _
                        dgv_rateio.Item(4, i).Value & "')"
    
                    comando = New SqlCommand(sql_rateio, conexao)
                    comando.ExecuteNonQuery()
                Next
    
    end sub
    

    Mais uma vez agradeço pela ajuda

    sábado, 25 de janeiro de 2014 03:25