Using Transaction in vb.net 2008

vj_mi
vj_mi used Ask the Experts™
on
Hello,

I have a windows app. Following is the scenario:

a. In Form1, I have Save button. This creates a sql connection and sets the transaction.
b. It then creates hashtable of fields to be updated in database.
c. This hash table is then passed to Class file (in another project but in same solution). Say the method in the class file is called UpdateTable. I also pass the sql connection and the transaction object to the method UpdateTable.
d. This method UpdateTable checks the parameters. If one of the paramter has value 0, it means Add new record and it calls another method InsertRecord. This insert record is in another DLL (CommonFunc.dll)  that is not part of this solution. I also pass the connection object and transaction object. If one of the parameter is 1, it means the existing record is edited and it calls method UpdateRecord from CommonFunc.Dll.
e. The function InsertRecord takes this connection and assigns it to local variable as follows:
Public Function InsertReecord(xxxxx, objconn, objTrans) as xxxx
  'Note that objconn and objTrans is both optional parameters
     Dim sqlcon as new sqltransaction
    if objConn is nothing = false then
        sqlconn = objconn
       'Open method not requiired since objConn is already open
   else
    ' regular code to create connection
 endif

'Now we set culture of date on this sql connection.
 Dim sqlcmd As New SqlCommand
        Try
            With sqlcmd
                .Connection = sqlconn
                .CommandText = "Set DateFormat dmy"
                .ExecuteNonQuery()
            End With
            Return True

        Catch

            Return False

        Finally
            sqlcmd.Dispose()

        End Try
End Function

Now the above code where I execute nonquery gives error saying that for pending local transaction, I need to set the transaction. So my question is how do I set the transaction objTrans in the InsertRecord method?

Regards,
MI
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Lead Software Engineer
Commented:
The SQLCommand object has a transaction property. You can perform a similar check for the objTrans object as you do for the objConn object. After you validate/create the transaction object, just set it to the transaction property of your sqlcmd.
Shaun KlineLead Software Engineer

Commented:
One other thing, if you create the transaction object in the insert method, make sure you commit it in the Finally portion of your try catch (and similarly close the connection object if you created it in the method).
Most Valuable Expert 2012
Top Expert 2014
Commented:
The connection has a transaction associated with it. Any command that uses the connection also need to use that transaction. I have included the modified code below. But i think you should not pass the connection and transaction objects around in so many functions especially across multiple DLLs.



Dim Success as boolean
Dim sqlcmd As New SqlCommand
Try
   With sqlcmd
    .Connection = sqlconn
    .CommandText = "Set DateFormat dmy"
    .Transaction = objTrans
    .ExecuteNonQuery()
   End With
   Success = True
Catch
   Success = False
Finally
   sqlcmd.Dispose()
   If Success Then
      objTrans.Commit()
      Return True
   Else
      objTrans.RollBack()
      Return False
End Try

Open in new window

Author

Commented:
One general comment:
Any reason why Commit is done in Finally? I did commit as the last line in Try and rollback in Catch. Pl. let me know your thoughts.
Regards,
MI
Shaun KlineLead Software Engineer

Commented:
I misspoke. The Commit should be done just prior to the Catch. The Rollback (as you mentioned) is done in the Catch statement. What I meant to say was make sure you do any cleanup in the Finally statement, which would include closing any open connections. I personally like to kill connection and transaction objects in the finally statement by setting them equal to nothing, but that is more preference that need. The framework should handle the disposal of the objects.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial