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