Help with SQL query - inserts with OracleTransaction (from C#/asp.net)

Question

Hi I need advice how to accomplish example as described below. I have one table INVOICE_HEADER and one table INVOICE_ITEM. I want to first insert data into table INVOIC_HEADER (data received from SELECT sql query). Then I want to insert data into table INVOICE_ITEM (data received from SELECT sql query). I want that no commit of table INVOICE_HEADER should be done if input data is not correct of some reason. I also want that inserted data into table INVOICE_HEADER not should be commited if insert of data in table INVOICE_ITEM fails of some reason. With other/shorter words I just want that inserts for both tables should be commited if inserts in both tables are successful. Any kind of advice/hints are highly appreciated! I have made a search of how to solve this and think that the best solution is to use OracleTransaction (C#) in some way but I can't get it to work. Thx in advance!

Amit Choudhary · Answer

hi friend you can achieve it using Transactions in Pl/Sql statement in Oracle : BEGIN ... UPDATE accts SET bal = my_bal - debit WHERE acctno = 7715; ... UPDATE accts SET bal = my_bal + credit WHERE acctno = 7720; COMMIT WORK; END; This code is the example of band transaction where account is credited and debited from two accounts. This code will commit only if both table will be saved. Hope it helps you. Please mark as answer if it helps you.

Sam Hobbs · Answer

Look at the Transaction class in the System.Transactions namespace. Also the following articles. Transactions in ASP.NET Create transaction using the DataAccess objects in ASP.NET Transactions and Concurrency Control using ADO.NET Kiss your databse only once!!!