Problem with TransactionScope .Net SqlServer

0

Usually I use TransactionScope for my developments but in this case I find something strange. I have to update in the tables of Order headers and order lines the delivery date when the user makes a reading of the barcode of the document in question.

                Dim ts1 As New TimeSpan(0, Configuracion.TiempoTransaccion, 0)
                Dim Result As Boolean = True
                Using scope As New TransactionScope(TransactionScopeOption.RequiresNew, ts1)
                    Using CnxWriter As New SqlConnection(CnxConnectionString)
                        Try
                            CnxWriter.Open()

                            ''9 - Actualizo la fecha de entrega del Documento Sage 
                            If F_DOCENTETE.Update_DO_DateLivr(Now, DO_Piece, eDO_type.Pedido, eDO_Domaine.Ventas, CnxWriter, Log) Then
                                addLog(String.Format("Actualizada la fecha de envio en el documento de Sage {0} referente al documento Web {1} por la impresora {2}.", DO_Piece, IdDocWeb, Configuracion.ImpresoraFacturaWeb), eT_TIpos.Ok, , Log)
                                If F_DOCLIGNE.Update_DO_DateLivr(Now, DO_Piece, eDO_type.Pedido, eDO_Domaine.Ventas, CnxWriter, Log) Then
                                    addLog(String.Format("Actualizada la fecha de envio en las lineas del documento de Sage {0} referente al documento Web {1} por la impresora {2}.", DO_Piece, IdDocWeb, Configuracion.ImpresoraFacturaWeb), eT_TIpos.Ok, , Log)
                                    If ADV_WSYNC_ESTADO_IMPRESION_ETQ_FAC.Update_UpdFchEntrega(IdDocWeb, True, CnxWriter, Log) Then
                                        Result = True
                                    Else
                                        Result = False
                                        addLog(String.Format("No se ha actualizado el check de Actualizada Fecha de Envio del documento de Sage {0} referente al documento Web {1}.", DO_Piece, IdDocWeb), eT_TIpos.Err, , Log)
                                    End If
                                Else
                                    ''La fecha de entrega no se actualiza correctamente en la cabecera del documento
                                    Result = False
                                    addLog(String.Format("No se ha actualizado la fecha de envio en las lineas del documento de Sage {0} referente al documento Web {1} por la impresora {2}.", DO_Piece, IdDocWeb, Configuracion.ImpresoraFacturaWeb), eT_TIpos.Warning, , Log)
                                End If
                            Else
                                ''La fecha de entrega no se actualiza correctamente en la cabecera del documento
                                Result = False
                                addLog(String.Format("No se ha actualizado la fecha de envio en el documento de Sage {0} referente al documento Web {1} por la impresora {2}.", DO_Piece, IdDocWeb, Configuracion.ImpresoraFacturaWeb), eT_TIpos.Warning, , Log)
                            End If


                            If Result Then
                                scope.Complete()
                            End If

                        Catch ex As Exception
                            ''Funciones.LanzarNotificador(ex.Message, eAccionNotificacion.Err)
                            addLog(ex.Message, eT_TIpos.Err, False, Log)
                        End Try
                    End Using
                End Using

I create my transaction with a time of 5 minutes (Configured by the user in Configuration.TimeTransaction). I open the connection and do three updates. The first F_DOCENTETE.Update_DO_DateLivr updates the delivery date of the order header. To this function I pass the unique identification data of the record and the open connection with the transactionscope. This works perfectly. The second F_DOCLIGNE.Update_DO_DateLivr updates the delivery date in the order lines. To this function I pass the identification data of the order (there may be several lines per order) and also the open connection with the transactionscope. In this function I already have the problem. The error is given by the execution of the command by waiting time even if it increases to 10 minutes for example. An update directly on the table in Sql works perfectly and using the database connection of the application without the transactionscope also works perfectly. They are also functions copied from other functions that I use daily. The only difference is the large number of records that the order line table has.

I tried to do it with pinion and it also fails. The DataTable load works perfectly. The Update fails.

                Dim ts1 As New TimeSpan(0, Configuracion.TiempoTransaccion, 0)
                Dim Result As Boolean = True
                Using scope As New TransactionScope(TransactionScopeOption.RequiresNew, ts1)
                    Using CnxWriter As New SqlConnection(CnxConnectionString)
                        Try
                            CnxWriter.Open()

                    Dim da As New SqlDataAdapter("SELECT DL_No FROM F_DOCLIGNE WHERE DO_Piece = 'pew079157' and DO_Type = 1 and DO_Domaine = 0", CnxWriter)
                                Dim tabla As New DataTable("Tabla")
                                da.Fill(tabla)

                                For Each _fila As DataRow In tabla.Rows
                                    Dim cmd As New SqlCommand("UPDATE F_DOCLIGNE SET DO_DateLivr = '22/02/2017' WHERE DO_Piece = 'pew079157' and DO_Type = 1 and DO_Domaine = 0 and dl_no = " + _fila(0).ToString, CnxWriter)
                                    cmd.CommandTimeout = 60
                                    cmd.ExecuteNonQuery()
                                Next

                                scope.Complete()

                        Catch ex As Exception
                            ''Funciones.LanzarNotificador(ex.Message, eAccionNotificacion.Err)
                            addLog(ex.Message, eT_TIpos.Err, False, Log)
                        End Try
                    End Using
                End Using
    
asked by Jaime Capilla 22.02.2017 в 08:09
source

0 answers