transactions...

[Anti-Rich]

hi all,

im just about to start an application for my company, and i want to use transactions rather than the normal way that i use (nonquerys and suchlike with no transactions) and i was wondering if anyone knew any good transaction tutorials?

also, what are the advantages of using transactions over normal insertion/updating methods?

i will be using vb.net 2005 express edition, so any help from either .net 2003/2005 people would be great.

cheers
adam

 

[cjard]

transactions are not a replacement to normal I/U/D statements, they are a supplement to them.

The notion of a transaction is that the database preserves an undo-list of every change you make while in a transaction. If at any point you wish to undo what you have done, you roll back the transaction.

Picture the oft quoted example of a bank account transfer:

UPDATE accounts SET balance = balance - transfer WHERE account_number = x
UPDATE accounts SET balance = balance +transfer WHERE account_number = y


Naturally we want these to succeed as a unit, or fail as a unit. Taking from one and not adding to the other will cause financial problems. A transaction is used to ensure the entire operation succeeds or fails:

'MTS method
Using ts as New TransactionScope()

 [INDENT]'UPDATE accounts SET balance = balance - transfer WHERE account_number = x[/INDENT]

[INDENT]'UPDATE accounts SET balance = balance +transfer WHERE account_number = y
'every other db command that this local thread runs will join the transaction (even 
'in different methods) untilthe line of code below is called:
[/INDENT]
 

[INDENT]ts.Complete() 'if we dont reach here the changes are not committed[/INDENT]

Catch Exception[INDENT]Messagebox.Show("The transfer failed and was rolled back")[/INDENT]
End Using

Modern ADO.NET makes transactions rather painless, as you can see. All you need is a database that supports MTS - Oracle does as of client 9i. SQL Server does

 

[Anti-Rich]

ah i see. i understand that now, and as my app wont have any sort of transaction like that ill stick to the normal method. thanks heaps cjard much appreicated.


regards
adam

 

[cjard]

It's unusual to have a database system where the tables are not related at all - if you have relations you should employ transactions purely to ensure that all related data is properly persisted to the database, or not at all. this avoids parent records having no children and/or (if you have no Foreign Keys set up) orphaned children

 

[Anti-Rich]

yeah i suppose you have a point. well, i will try it when i get to that point (not for a looooong while... still in the design phase). adn there are tables that relate to each other, but its not as do or die as a bank transfer, tahts what i meant. but i will think about implementing it anyway, for future knowledge i suppose.

cheers mate

regards
adam