Sqlclient transaction and .Commit()

Question

In this code will the SomeFunction() after the .commit() be considered part of the transaction? Would it rollback if something blew up? I need to do further processing after dynamic records are inserted and would prefer to do it all in one big lump.

command.Transaction = transaction
Try
    command.CommandText = _
    "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
    command.ExecuteNonQuery()
    transaction.Commit()
    'do a function call here
    SomeFunction()
Catch ex As Exception
    transaction.Rollback()
End Try

score 0 · Accepted Answer · answered Apr 22 '12 at 00:44

No it wont rollback since by the time Somefunction() is called the transaction is already Commited.

However if SomeFunction throws any exception your catch block will still throw an exception in the transaction.Rollback() method as there is no active transaction to rollback.

You should move your Somefunction() call below your Exception block and if possible put it in another try catch block.

command.Transaction = transaction
Try
    command.CommandText = _
    "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
    command.ExecuteNonQuery()
    transaction.Commit()

Catch ex As Exception
    transaction.Rollback()
End Try

    'do a function call here
    SomeFunction()

Yeh, I kind of figured. I'll to put it all in a sp like I wanted to avoid. Thanks for confirming! — Somejerk, Apr 22 '12 at 01:23

Sqlclient transaction and .Commit()

1 Answers1