I have 2 tables enquiry and details. On save button click I have written
fbsave();
fbsavedetails();
fbsave()
save the data in enquiry table and fbsavedetails()
saves data in details table.
now if error occur in fbsavedetails() then both steps should be rollback.
is it possible?
The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction. For more information, see "Overview of Transaction Processing in PL/SQL". The SQL ROLLBACK statement can be embedded as static SQL in PL/SQL.
The Rollback method is equivalent to the Transact-SQL ROLLBACK TRANSACTION statement. For more information, see ROLLBACK TRANSACTION (Transact-SQL) . The transaction can only be rolled back from a pending state (after BeginTransaction has been called, but before Commit is called).
A commit operation makes permanent all changes made under commitment control since the previous commit or rollback operation. The system also releases all locks related to the transaction. A rollback operation removes all changes made since the previous commit or rollback operation.
You can explicitly create a transaction and pass that around, i.e.
using(var connection = ...)
{
connection.Open();
using (var tran = connection.BeginTransaction())
{
try
{
FBSave(connection, tran);
FBSaveDetails(connection, tran);
tran.Commit();
}
catch
{
tran.Rollback();
throw;
}
}
}
Note that here you must also set the Transaction
on each command, hence why you need to pass it in, and all the commands must be on the same connection object.
Or: you can use TransactionScope
; it is important that the Open()
happens inside the TransactionScope
to get automatic enlistment:
using(var tran = new TransactionScope())
{
FBSave();
FBSaveDetails();
tran.Complete();
}
or:
using(var tran = new TransactionScope())
using(var connection = ...)
{
connection.Open();
FBSave(connection);
FBSaveDetails(connection);
tran.Complete();
}
with the TransactionScope
approach, you don't need to set anything special - most of it is automatic. You can of course optionally pass the connection into the methods, but they could also obtain their own connection, and in most cases it would work fine.
You can use TransactionScope.
using(var scope = new TransactionScope())
{
//Complete the transaction only when both inserts succeed.
scope.Complete();
}
if you don't complete the transactionscope it will be rolled back.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With