I have a sql transaction inside a transactionscope and that transaction is doing a commit or a rollback. But from what I've read, that commit/rollback will have no meaning as it's controlled by the transactionscope. However if I leave the transaction object inside the dosomething method without the commit or rollback, my webservice method, which is doing a save into a database, does not work as the individual transaction object has obtained the lock. Could someone please explain how is that possible? Or is this the usual behaviour and if so why?
public sub test()
Using ts As New TransactionScope()
Save_CustomerDetails()
Save_AddressDetails()
'cannot show the code for security reasons
CallToWebServiceFunctionWhichSavesTheDataInDatabase()
ts.Complete()
End Using
end sub
Private Sub Save_CustomerDetails()
Dim ExistingData As DataSet
Dim DB_CustomerDetails As New CustomerDetails(objDBconn.Connection, objDBconn.Transaction)
Try
ExistingData = DB_CustomerDetails.CustomerDetails_Set()
If DB_CustomerDetails.ReturnCode<> 0 Then
Throw New ReturnException(DB_CustomerDetails.ReturnCode)
End If
Catch lex As ReturnException
JavaOnload = lex.AlertMessage
Throw
Finally
DB_CustomerDetails = Nothing
If Not(objDBCommon Is Nothing) Then
objDBCommon.CleanUp()
End If
End Try
End Sub
Public Sub Save_AddressDetails()
Dim DB_AddressDetails As New AddressDetails(objDBCommon.Connection, objDBCommon.Transaction)
Try
ExistingData = DB_AddressDetails.AddressDetails_Set()
If DB_AddressDetails.ReturnCode<> 0 Then
Throw New ReturnException(DB_AddressDetails.ReturnCode)
End If
Catch lex As ReturnException
JavaOnload = lex.AlertMessage
Throw
Catch lex As System.Exception
Throw
Finally
DB_AddressDetails = Nothing
End Try
End Sub
Function CustomerDetails_Set(Optional ByVal inFunction As String = "")
Dim Params() As SqlClient.SqlParameter
Try
Parameters.Clear()
Params = MyBase.GetParameters(Me.Connection, "sprCustomerDetails_Modify", True)
AddParam(Parameters, Params(0), ParameterDirection.ReturnValue)
AddParam(Parameters, Params(1), ParameterDirection.Input, inFunction)
AddParam(Parameters, Params(2), ParameterDirection.Input, m_Customer_ID)
MyBase.CommandType = CommandType.StoredProcedure
MyBase.CommandText = "sprCustomerDetails_Modify"
MyBase.Transaction = Me.Transaction
MyBase.Connection = Me.Connection
MyBase.Load(m_oDS)
ReturnCode = Parameters.Item(0).Value
Return m_oDS
Catch ex As SqlClient.SqlException
If ex.Number = eReturnCodes.SaveSinceLastUpdate Then
ReturnCode = ex.Number
Else
Throw
End If
Finally
End Try
End Function
Function AddressDetails_Set(Optional ByVal inFunction As String = "") As DataSet
Dim Params() As SqlClient.SqlParameter
Try
Parameters.Clear()
Params = MyBase.GetParameters(Me.Connection, "sprAddressDetails_Modify", True)
AddParam(Parameters, Params(0), ParameterDirection.ReturnValue)
AddParam(Parameters, Params(1), ParameterDirection.Input, inFunction)
AddParam(Parameters, Params(2), ParameterDirection.Input, m_Customer_ID)
MyBase.CommandType = CommandType.StoredProcedure
MyBase.CommandText = "sprAddressDetails_Modify"
MyBase.Transaction = Me.Transaction
MyBase.Connection = Me.Connection
MyBase.Load(m_oDS)
ReturnCode = Parameters.Item(0).Value
Return m_oDS
Catch ex As SqlClient.SqlException
If ex.Number = eReturnCodes.SaveSinceLastUpdate Then
ReturnCode = ex.Number
Else
Throw
End If
Catch ex As System.Exception
Throw
Finally
End Try
End Function
Protected Overridable Function GetParameters(ByRef inConnection As SqlClient.SqlConnection _
, ByVal inStoredProcedureName As String _
, ByVal inIncludeReturnValue As Boolean) As SqlClient.SqlParameter()
Return SqlHelperParameterCache.GetSpParameterSet(inConnection, inStoredProcedureName, inIncludeReturnValue)
End Function
Public Sub CleanUp()
Try
If Not Me.Transaction Is Nothing Then
If m_ReturnCode< 0 Then
Try
Me.Transaction.Rollback()
Catch ex As Exception
End Try
Else
Try
Me.Transaction.Commit()
Catch ex As Exception
End Try
End If
End If
If Me.Connection.State = ConnectionState.Open Then
Me.Connection.Close()
End If
Catch ex As Exception
Finally
If Not Me.Transaction Is Nothing Then
Me.Transaction.Dispose()
Me.Transaction = Nothing
End If
If Not Me.Connection Is Nothing Then
Me.Connection.Dispose()
Me.Connection = Nothing
End If
End Try
End Sub