JimmyJimm JimmyJimm - 6 months ago 62
Vb.net Question

Two functions queries in one transaction

Have two functions which are used for application purposes where they are consume many times. Currently there is purpose to use them as one within one transaction but i am not sure if i can put them in the way as below. Take look below on them:

First one:

Public Function Delete(varId As Integer) As Boolean
Dim result As Boolean = False
Using con As New SqlConnection(strcon)
Using cmd As New SqlCommand("DELETE FROM T_Variation WHERE Id=@Id", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Id", varId)
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
result = True
End Using
End Using
Return result
End Function


Second one:

Public Function DeleteAllWhereVarId(pVarId As Integer) As Boolean
Dim result As Boolean = False
Using con As New SqlConnection(strcon)
Using cmd As New SqlCommand("DELETE FROM T_Variation_Attribute WHERE FK_Variation_ID=@FK_Variation_ID", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@FK_Variation_ID", pVarId)
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
result = True
End Using
End Using
Return result
End Function


Both to transaction:

Now i would like to make function with transaction and use above functions as one. The problem is both first and second function already open their own connection, and here has to be only one if am not mistaken. This is what i don't know how to do.

Public Function DeleteWithAttributes(varId As Integer) As Boolean
Dim result as Boolean = true
Using connection As New SqlConnection(strcon)
'-- Open generall connection for all the queries
connection.Open()
'-- Make the transaction.
Dim transaction As SqlTransaction
transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)

Try
Call New DALVariation_Attribute().DeleteAllWhereVarId(varId)
Delete(varId)

transaction.Commit()
Catch ex As Exception
result = False
'-- Roll the transaction back.
transaction.Rollback()
End Try
End Using

Return result
End Function

Answer

You can create overloads for your functions to accept a transaction. Something like:

    Public Function Delete(tran As SqlTransaction, varId As Integer) As Boolean
        Dim result As Boolean = False

        If tran Is Nothing Then
            Using con As New SqlConnection(strcon)
                Using cmd As New SqlCommand("DELETE FROM T_Variation WHERE Id=@Id", con)
                    cmd.CommandType = CommandType.Text
                    cmd.Parameters.AddWithValue("@Id", varId)
                    con.Open()
                    Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                    con.Close()
                    result = True
                End Using
            End Using
            Return result
        Else
            Using cmd As New SqlCommand("DELETE FROM T_Variation WHERE Id=@Id", tran.Connection)
                cmd.Transaction = tran
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@Id", varId)
                Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                result = True
            End Using
        End If    
    End Function

    Public Function Delete(varId As Integer) As Boolean
        Return Delete(Nothing, varId)
    End Function

and

Public Function DeleteAllWhereVarId(tran As SqlTransaction, pVarId As Integer) As Boolean
        Dim result As Boolean = False

        If tran Is Nothing Then
            Using con As New SqlConnection(strcon)
                Using cmd As New SqlCommand("DELETE FROM T_Variation_Attribute WHERE FK_Variation_ID=@FK_Variation_ID", con)
                    cmd.CommandType = CommandType.Text
                    cmd.Parameters.AddWithValue("@FK_Variation_ID", pVarId)
                    con.Open()
                    Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                    con.Close()
                    result = True
                End Using
            End Using
        Else
            Using cmd As New SqlCommand("DELETE FROM T_Variation_Attribute WHERE FK_Variation_ID=@FK_Variation_ID", tran.Connection)
                cmd.Transaction = tran
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@FK_Variation_ID", pVarId)
                Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
                result = True
            End Using
        End If

        Return result
    End Function
    Public Function DeleteAllWhereVarId(pVarId As Integer) As Boolean
        Return DeleteAllWhereVarId(Nothing, pVarId)
    End Function

so from the caller routine you can do something like:

Public Sub DoSomeWOrk()
        Using con As New SqlConnection(strcon)
            Dim commit As Boolean = True

            con.Open()
            Dim tran As SqlTransaction = con.BeginTransaction

            If commit Then commit = commit And Delete(tran, 1)
            If commit Then commit = commit And DeleteAllWhereVarId(tran, 1)

            If commit Then
                tran.Commit()
            Else
                tran.Rollback()
            End If
        End Using
    End Sub
Comments