Avatar of shieldguy
shieldguyFlag for United Kingdom of Great Britain and Northern Ireland asked on

Problem in implementing database transaction in Data Access Layer

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

Open in new window

ASP.NETVisual Basic.NET.NET Programming

Avatar of undefined
Last Comment
Nasser Hamdan

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Nasser Hamdan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
shieldguy

I dont know ho wto explain if you take a look at the code i added

I have a data access layer and I need to control the single transaction between the several call to same save function with in a for loop

For single record insert the life is easy but in my scenario I dont know what should I do

Thanks
Nasser Hamdan

i see ,, then you need to have same transaction on many functions call? ... i think you have to pass the command object to you methods,, means in every sub function you calll send the command to it rather than define new command in each function ,, and in main function that call all of these when all succeed commit

 

ASKER
shieldguy

But what I have done is that I make the connection and transaction object as global in the class and then I call them and assign to and sub procedure call of the same class so the same connection and transaction object are shared among them and then if I got and error in between I just call transaction rollback. Is its a good logic.

One more problem which i am facing is that as soon as I begin the transaction the table become locked and other cant access it. But this table cant be allowed to locked as its acces by many programs
any ideas about to solve this issues ?

I would really appreciate any help on this

Thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Nasser Hamdan

Yes you can declare as global and use it this way? are u still facing an error with this? also you can control the isolation level on the transaction ,, means you can let others access the data while doing transaction ,, read this :

http://msdn.microsoft.com/en-us/library/system.data.isolationlevel.aspx
ASKER
shieldguy

yes i had solved my problem, its just a small logic error.

ASKER
shieldguy

My logic seems to working fine now but i am just afraid that what are the demerits of using the transaction on this table as its a very critical table and lot of processing done on it and its also a very large in size.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
shieldguy

Can you please suggest which type of IsolationLevel  suite this senario

Thanks
Nasser Hamdan

i dont see why u are afraid ? the only effect that i can see here is performance since you are locking reding on it while transactions complete ,, but also you can allow dirty reads by isolationlevel .. otherwise transactions is best solution when you have many tables insertion/update/delete on same logic .... but the drawback of not using transactions is the one you should be afraid of.

I am using transaction on stock market online trading system that should be reliable 100% and very high performance and never faced a problem with.
Nasser Hamdan

i think you should use : ReadUncommitted ... to allow users read from data while applying the transaction. otherwise use  ReadCommitted .. if you want users to wait until all data comitted to allow reading on
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Nasser Hamdan

you should know that ReadCommited is the default and most recommended.