"But I had a lot of tables need to be updated (eg. summary1, summary2, summary3, detail1, detail2, detail3) and they are in separate function, should I open and close the connection in each function? if so, does the rollback function will work when it required? I need advice .. many thanks in advance .."

Part of that would be drive by the business logic/requirements...but it's possible that you could still do it with one connection.

Let's say that all 6 tables belong to one transaction...that if one update fails, then they all fail. You could set up a single transaction after opening the connection. You do your updates to all 6 tables, and then either commit or rollback, and then close.

But let's say that there are actually 2 tranactions...one for tables 1 and 2, and a 2nd tranaction for tables 3 through 6. One is not 'dependent' on the other. You could open the connection, do your first transaction and commit/rollback...then do your 2nd transaction and then commit/rollback. And then close when you're done.

You can certainly do your updates in separate functions and have the rollback work properly....though I think you'd need to correctly pass the command object as a parameter (since you need to associate the command object's transaction property to the transaction you're working with).

Kevin