troubleshooting Question

Problem in implementing database transaction in Data Access Layer

Avatar of shieldguy
shieldguyFlag for United Kingdom of Great Britain and Northern Ireland asked on
.NET ProgrammingASP.NETVisual Basic.NET
11 Comments1 Solution1055 ViewsLast Modified:
I am having problem that my logic for implementing the database transaction Dala Access Layer which is not working as intended. I am using the asp.net with VB.Net

All I am doing is that I am calling a processorder function from business layer class,
then I start transaction and loop through records and call addsales functiontions in the data Access Layer and if I found any error I just roll back the transaction

I dont that Is it a good practice to have a global transaction variable and cotrol the data trasaction from the business layer

Please tell me why I am not getting the transaction working if error occurs

Thanks



This function is called from the class in a Business Access Layer
 
Namespace BLL
 
    Public Class OrderManager
 
        Dim aStockManager As New StockManager
 
        Public Function ProcessOrder(ByVal anOrder As BatchOrder) As String
 
                    Try
 
                        'BEGIN THE DATABASE TRANSACTION
                        aStockManager.BeginTransaction()
 
                        For Each anOrderLine As OrderLine In anOrder.Lines
 
 
                                strResult = aStockManager.AddStockSalesTransaction(anOrderLine.QuantityOrdered, anOrderLine.ProductID, anOrder.ID)
 
                            If Trim(strResult) <> "" Then
                                bolIsError = True
 
                                Exit For
                            Else
                                'NOW BECAUSE THEIR IS NO ERRO ON THE GIVEN ORDERLINE SO WE ARE GOING TO 
                                anOrderLine.QuantityDespatched = anOrderLine.QtyAllocated
                            End If
 
                        Next
 
                        If bolIsError = False Then
 
                            'COMMINTING ALL THE CHANGES WE MADE TO THE STOCK
                            aStockManager.SetTransaction("COMMIT")
 
                        ElseIf bolIsError = True Then
 
                            'COMMINTING ALL THE CHANGES WE MADE TO THE STOCK
                            aStockManager.SetTransaction("ROLLBACK")
 
                        End If
 
                    Catch ex As Exception
                        Console.WriteLine("Commit Exception Type: {0}", ex.GetType())
                        Console.WriteLine("  Message: {0}", ex.Message)
 
                        ' Attempt to roll back the transaction.
                        Try
                            aStockManager.SetTransaction("ROLLBACK")
 
                        Catch ex2 As Exception
                            ' This catch block will handle any errors that may have occurred
                            ' on the server that would cause the rollback to fail, such as
                            ' a closed connection.
                            Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType())
                            Console.WriteLine("  Message: {0}", ex2.Message)
                        End Try
                    End Try
 
            Return ""
 
        End Function
    End Class
End Namespace
 
 
 
This function exists in a another class in the Business Access Layer
 
Namespace BLL
 
    Public Class StockManager
 
     Dim aStockDb As StockDB
 
        Public Function AddStockSalesTransaction(ByVal intQtyDespatched As Integer, ByVal lngProductID As Long, ByVal lngOrderID As Long) As String
 
            Dim strResult As String = ""
 
                'UPDATING THE STOCK LINES DETAILS
                strResult = UpdateSalesStockLines(availableStock, lngOrderID)
 
                If strResult = "" Then
                    'UPDATING THE STOCK OF THE PRODUCT IN PRODUCT TABLE
                    strResult = ProductDB.UpdateProductStockQty(lngProductID, GetAvaialableStock(availableStock))
                End If
 
                Return strResult
            Else
                Return "No Stock Available"
            End If
        End Function
 
        Private Function UpdateSalesStockLines(ByVal StockToUpdate As List(Of StockTransInLine), ByVal lngOrderID As Long) As String
 
            Dim strResult As String = ""
 
            For Each stktrans As StockTransInLine In StockToUpdate
 
                If stktrans.StockUsedByTransaction > 0 Then
 
                    'ADD TO SALES ENTRY TO STOCK TRANS
                    strResult = aStockDb.AddToStockTrans(stktrans, lngOrderID, "Sale", "test")
 
                    If strResult <> "" Then
                        Exit For
                    End If
 
                    'AMEND THE GI/AI STOCK LINE
                    strResult = UpdateStockUsedQty(stktrans)
 
                    If strResult <> "" Then
                        Exit For
                    End If
 
                End If
            Next
 
            Return ""
 
        End Function
 
        'BEGIN THE DATABASE TRANSACTION 
        Public Sub BeginTransaction()
            aStockDb = New StockDB("TRANSACTION")
            aStockDb.BeginTransaction()
        End Sub
        'SET STATUS OF THE TRANSACTION
        Public Function SetTransaction(ByVal aStatus As String) As String
            Return aStockDb.SetTransaction(aStatus)
        End Function
 
        Private Function AddToStockTrans(ByRef objStockLine As StockTransInLine, ByVal strOrderId As String, ByVal strType As String, ByVal strReference As String) As String
            aStockDb = New StockDB()
            Return aStockDb.AddToStockTrans(objStockLine, strOrderId, strType, strReference)
        End Function
 
     Private Function UpdateStockUsedQty(ByRef objStockLine As Wiggle.BatchProcessing.Domain.StockTransInLine) As String
            Return aStockDb.UpdateStockUsedQty(objStockLine)
        End Function
    End Class
End Namespace
 
 
Data access layer
Namespace DAL
 
    Public Class StockDB
 
        'THIS WILL TAKE CARE OF THE DATABASE TRANSACTION
        Private mTransaction As SqlTransaction
 
        Private mCn As SqlConnection
 
        Sub New()
 
        End Sub
 
        Sub New(ByVal aConnType As String)
 
            If UCase(aConnType) = "TRANSACTION" Then
 
                Dim connString As String = ""
                Dim mySettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("WiggleSqlConnection")
                If (mySettings IsNot Nothing) Then
                    connString = mySettings.ConnectionString
                End If
                mCn = New SqlConnection(connString)
                mCn.Open()
            End If
 
        End Sub
      Public Sub BeginTransaction()
            ' Start a local transaction
            mTransaction = mCn.BeginTransaction("StockTransaction")
        End Sub
 
 
 
 
       'TAKE THE PRODUCTID AND UPDATE THE QTY IN PRODUCT AND STOCKTRANS TABLE
        Public Function AddToStockTrans(ByVal objStockLine As StockTransInLine, ByVal strOrderID As String, ByVal strType As String, ByVal strReference As String) As String
 
            Dim strSQL As String
            Dim intQty As Integer = 0
 
            'IN THE CASE OF SALES THE QUANTITY WILL BE TAKEN FROM THE AMOUNT OF STOCKUSED BY THE ORDER IN THE THIS STOCK ORDER LINE OBJECT
            If UCase(strType) = "SALE" Then
                intQty = objStockLine.StockUsedByTransaction
            ElseIf UCase(strType) = "AI" Or UCase(strType) = "AO" Then
                intQty = objStockLine.Qty
            End If
 
            strSQL = "sAddToStockTrans"
 
            Dim dbCmd As SqlCommand = New SqlCommand(strSQL, mCn)
 
            dbCmd.Transaction = mTransaction
 
            dbCmd.CommandType = CommandType.StoredProcedure
 
            Dim inputProductID As SqlParameter = New SqlParameter("@ProductID", DbType.Int64, 64)
            inputProductID.Value = objStockLine.ProductID
            dbCmd.Parameters.Add(inputProductID)
 
            Dim inputQuantity As SqlParameter = New SqlParameter("@Quantity", DbType.Int64, 64)
            inputQuantity.Value = intQty
            dbCmd.Parameters.Add(inputQuantity)
 
           dbCmd.Parameters.Add(inputReference)
 
            dbCmd.ExecuteNonQuery()
 
            Return ""
 
        End Function
 
 
        'SET THE OUTCOME OF THE TRANSACTION AND ALSO CLOSE THE OPEN DATABASE CONNECTION
        Public Overridable Function SetTransaction(ByVal aStatus As String) As String
            Try
                If UCase(aStatus) = "COMMIT" Then
                    mTransaction.Commit()
                ElseIf UCase(aStatus) = "ROLLBACK" Then
                    mTransaction.Rollback()
                End If
                Return ""
            Catch ex As Exception
                Return ex.ToString()
            Finally
                If mCn.State <> ConnectionState.Closed Then mCn.Close()
            End Try
        End Function
 
 
 
        'UPDATE THE USED QTY OF STOCK OF THE GIVEN STOCKLINE FOR THE GI AND AI
        Public Function UpdateStockUsedQty(ByVal objStock As StockTransInLine) As String
 
            Dim strSQL As String
 
            strSQL = "sUpdateStockUsedQty"
 
            Dim dbCmd As SqlCommand = New SqlCommand(strSQL, mCn)
 
            dbCmd.Transaction = mTransaction
 
            dbCmd.CommandType = CommandType.StoredProcedure
 
            Dim inputStockTransID As SqlParameter = New SqlParameter("@StockTransID", SqlDbType.Int, 64)
            inputStockTransID.Value = objStock.StockTransID
            dbCmd.Parameters.Add(inputStockTransID)
 
            Dim inputUsed As SqlParameter = New SqlParameter("@Used", SqlDbType.Int, 64)
            inputUsed.Value = objStock.Used
            dbCmd.Parameters.Add(inputUsed)
 
            dbCmd.ExecuteNonQuery()
 
            Return ""
 
        End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros