none
TransactionScope RRS feed

  • Question

  • Hello, who can help me? How can I perform a synchronized transaction where I have 2 INSERT functions, but one depends on the other?

       
    I have 2 INSERT the First must start "INSERT IN DOCUMENTS, to create type, number, warehouse
    that matches "INSERT INTO documents_detail
    If I execute them individually, they work correctly
    . I am using them through two functions insertDocument () and insertDocumentDet ()

    insertDocument()  = "INSERT INTO documentos

    insertDocumentDet() = "INSERT INTO documentos_detalle

    asi:

    Public Function facturarDocumentos()

            Try

                Using transScope As New TransactionScope()

                    Using transScope2 As New TransactionScope()

                        insertDocument()

                        insertDocumentDet()

                    End Using

                    transScope.Complete()

                End Using

            Catch ex As ApplicationException ‘ (The transaction was canceled.)

                MsgBox(ex.Message)

            Catch ex As TransactionAbortedException

                MsgBox(ex.Message)

            End Try

        End Function

        Private Sub crear1_Click(sender As Object, e As EventArgs) Handles crear1.Click

           ‘ Totales()

            facturarDocumentos()

        End Sub

    Las Function they run correctly but at the end ‘ (The transaction was canceled.)


    • Edited by bello900 Thursday, March 29, 2018 12:53 AM
    Wednesday, March 28, 2018 9:17 PM

All replies

  • Hello, who can help me? How can I perform a synchronized transaction where I have 2 INSERT functions, but one depends on the other?

    A transaction scope in your example there is nothing that depends upon the other as a transaction. The only thing that maybe is depended is taking the ID of the parent that is inserted  and applying it to the child's foreign-key  when the child is inserted 

    The transaction scope would be the parent and child must be inserted successfully, because otherwise, the parent or parent and child are rolled back if either one fails to insert.

    Wednesday, March 28, 2018 9:49 PM
  • The basic flow is using SqlClient data provider for a SQL-Server database with three tables to insert into.

    • Create a connection object
    • Create a SqlTransAction object and set it to the connection.BeginTransation
    • Create a command object, set the property Transaction to the transaction created above.
    • Create a try/catch
    • Setup parameters for the first command (thinking the SQL INSERT statement has been dealt with)
    • Issue ExecuteScalar to get the new primary key, store it in a local variable.
    • Clear the parameter collection
    • Setup parameters again
    • ExecuteScalar 
    • Clear parameters
    • Repeat.

    After the last insert call Commit on the transaction, if there is an exception call Rollback on the transaction/

    For a complete working example, see my MSDN code sample (search for "For inserts"). Also shown with transactions, update and remove (the remove also has cascading delete set to true). 

    Here is the INSERT from the code sample.

    Public  Function AddNewCustomer(pCustomer As customer) As Boolean 
        mHasException = False 
     
        dim contactInsertStatement =  
                <SQL> 
                INSERT INTO dbo.Contact 
                            (FirstName 
                            ,LastName) 
                        VALUES 
                            (@FirstName 
                            ,@LastName);  
                SELECT CAST(scope_identity() AS int); 
                </SQL>.Value 
     
     
        Dim contactDevicesInsertStatement =  
                <SQL> 
                INSERT INTO dbo.ContactContactDevices 
                            (ContactIdentifier 
                            ,PhoneTypeIdenitfier 
                            ,PhoneNumber 
                            ,Active) 
                        VALUES 
                            (@ContactIdentifier 
                            ,@PhoneTypeIdenitfier 
                            ,@PhoneNumber 
                            ,@ACTIVE); 
                    SELECT CAST(scope_identity() AS int); 
                </SQL>.Value 
     
        Dim customerInsertStatement =  
                <SQL> 
                INSERT INTO dbo.Customers 
                            (CompanyName 
                            ,ContactName 
                            ,ContactIdentifier 
                            ,ContactTypeIdentifier 
                            ,Street 
                            ,City 
                            ,PostalCode 
                            ,CountryIdentfier 
                            ,Phone 
                            ,ModifiedDate) 
                        VALUES 
                            (@CompanyName  
                            ,@ContactName 
                            ,@ContactIdentifier 
                            ,@ContactTypeIdentifier 
                            ,@Street 
                            ,@City 
                            ,@PostalCode 
                            ,@CountryIdentfier 
                            ,@Phone 
                            ,@ModifiedDate); 
                    SELECT CAST(scope_identity() AS int); 
                </SQL>.Value 
     
        Dim contactIdentifier As Integer = 0 
        Dim contactDeviceIdentifier as Integer = 0 
     
        Using cn As New SqlConnection With{.ConnectionString = ConnectionString} 
     
     
            cn.Open() 
     
            Dim sqlTran As SqlTransaction = cn.BeginTransaction() 
                 
            Using cmd As New SqlCommand With  
                { 
                    .Connection = cn,  
                    .CommandText = contactInsertStatement,  
                    .Transaction = sqlTran 
                } 
     
     
                Try 
                    cmd.Parameters.AddWithValue("@FirstName",pCustomer.ContactFirstName) 
                    cmd.Parameters.AddWithValue("@LastName",pCustomer.ContactLastName) 
     
                    contactIdentifier = CInt(cmd.ExecuteScalar()) 
     
                    cmd.CommandText = contactDevicesInsertStatement 
                    cmd.Parameters.Clear() 
     
                    cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier) 
                    cmd.Parameters.AddWithValue("@PhoneTypeIdenitfier",pCustomer.PhoneTypeIdenitfier) 
                    cmd.Parameters.AddWithValue("@PhoneNumber",pCustomer.PhoneNumber) 
                    cmd.Parameters.AddWithValue("@Active",True) 
     
                    contactDeviceIdentifier = CInt(cmd.ExecuteScalar()) 
     
                    cmd.CommandText = customerInsertStatement 
                    cmd.Parameters.Clear() 
     
                    cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName) 
                    cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName) 
                    cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier) 
                    cmd.Parameters.AddWithValue("@ContactTypeIdentifier",pCustomer.ContactTypeIdentifier) 
                    cmd.Parameters.AddWithValue("@Street",pCustomer.Street) 
                    cmd.Parameters.AddWithValue("@City",pCustomer.City) 
                    cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode) 
                    cmd.Parameters.AddWithValue("@Phone",pCustomer.PhoneNumber) 
                    cmd.Parameters.AddWithValue("@CountryIdentfier",pCustomer.CountryIdentifier) 
                    cmd.Parameters.AddWithValue("@ModifiedDate",Now) 
     
                    pCustomer.CustomerIdentifier = CInt(cmd.ExecuteScalar()) 
                    pCustomer.ContactIdentifier = contactIdentifier 
                    pCustomer.DevicesIdentifier = contactDeviceIdentifier 
                    sqlTran.Commit() 
     
                    return True 
     
                Catch sex As SqlException 
     
                    sqlTran.Rollback() 
                    mHasException = True 
     
                    Return False 
     
                Catch ex As Exception 
                    mHasException = True 
                    mLastException = ex 
     
                    Return False 
     
                End Try 
            End Using 
        End Using 
    End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, March 28, 2018 10:32 PM
    Moderator
  • Modify the problem

    Modify the problem
    Thursday, March 29, 2018 12:54 AM
  • Modify the problem

    Modify the problem

    The code you posted before that seems to have vanished was not optimal coding.

    Do you understand foreign-key relationships? Do you understand 1-2-many relationships?

    https://www.techopedia.com/definition/7272/foreign-key

    https://fmhelp.filemaker.com/help/16/fmp/en/index.html#page/FMP_Help/one-to-many-relationships.html

    Your code would look a lot cleaner if using parameterized T-SQL, and  it also  prevents SQL Injection attacks.

    https://www.aspsnippets.com/Articles/Using-Parameterized-queries-to-prevent-SQL-Injection-Attacks-in-SQL-Server.aspx

    <copied>

    Dim strQuery As String
    Dim cmd As SqlCommand
    strQuery = "insert into customers (CustomerID, CompanyName) values(@CustomerID, @CompanyName)"
    cmd = New SqlCommand(strQuery)
    cmd.Parameters.AddWithValue("@CustomerID", "AZNL")
    cmd.Parameters.AddWithValue("@CompanyName", "ABC")

    Also, your code would look a lot cleaner if passing an object such as a DTO into the function.

    https://www.codeguru.com/vb/gen/vb_misc/oop/article.php/c7063/Data-Transfer-Object-Pattern-Goes-VBNET.htm

    However, your DTO would be using auto properties, and not the DTO with public properties using private backing variables as in the above example link, because auto properties have their own private backing variables. 

    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/language-features/procedures/auto-implemented-properties

    Public Function SomeFunc(Byval thedto as DTOCutomer) as Interger
    Dim strQuery As String
    Dim cmd As SqlCommand
    strQuery = "insert into customers (CompanyName) values(@CompanyName)"
    cmd = New SqlCommand(strQuery)
    cmd.Parameters.AddWithValue("@CompanyName", DTOCustomer.Comanyname)
    '' do the insert and return the ID that was auto assigned auto incremented integer as the primary key for the record inserted.
    return ID
    end function

    id is passed into child insert function 

    Public Sub SomeChild(Byval thedto as DTOCustDetail)
    Dim strQuery As String
    Dim cmd As SqlCommand
    strQuery = "insert into custdatail(CustomerID) values(@CutomerID)"
    cmd = New SqlCommand(strQuery)
    cmd.Parameters.AddWithValue("@CustomerID, DTOCustChild.CustomerID)
    
    '' other parms added for child record from DTOChild

    so it's this.

    Start Transaction

     dim id = insertparent(DTOParent)

    dim dto = new DTOChild()

    dto.FK-ID = id

    insertchild(dto)

    Transaction.complete

    Thursday, March 29, 2018 1:00 AM
  • Hi bello900,

    If you want to know how to use Transaction Scope when you have multiple sql statements, you can take a look the msdn example:

    '  This function takes arguments for 2 connection strings and commands to create a transaction 
    '  involving two SQL Servers. It returns a value > 0 if the transaction is committed, 0 if the 
    '  transaction is rolled back. To test this code, you can connect to two different databases 
    '  on the same server by altering the connection string, or to another 3rd party RDBMS  
    '  by altering the code in the connection2 code block.
    Public Function CreateTransactionScope( _
      ByVal connectString1 As String, ByVal connectString2 As String, _
      ByVal commandText1 As String, ByVal commandText2 As String) As Integer
    
        ' Initialize the return value to zero and create a StringWriter to display results.
        Dim returnValue As Integer = 0
        Dim writer As System.IO.StringWriter = New System.IO.StringWriter
    
        Try
        ' Create the TransactionScope to execute the commands, guaranteeing
        '  that both commands can commit or roll back as a single unit of work.
            Using scope As New TransactionScope()
                Using connection1 As New SqlConnection(connectString1)
                    ' 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)
                        ' 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)
                    End Using
                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
        Catch ex As TransactionAbortedException
            writer.WriteLine("TransactionAbortedException Message: {0}", ex.Message)
        Catch ex As ApplicationException
            writer.WriteLine("ApplicationException Message: {0}", ex.Message)
        End Try
    
        ' Display messages.
        Console.WriteLine(writer.ToString())
    
        Return returnValue
    End Function

    https://docs.microsoft.com/en-us/dotnet/framework/data/transactions/implementing-an-implicit-transaction-using-transaction-scope

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Wednesday, April 4, 2018 9:11 AM
    Thursday, March 29, 2018 5:38 AM
    Moderator
  • Hello, who can help me? How can I perform a synchronized transaction where I have 2 INSERT functions, but one depends on the other?


    Why you use two scopes to watch one dependency. Although the first one is only instanced and disposed (canceled).

    Success
    Cor

    Thursday, March 29, 2018 1:19 PM