ADO.Net Transactions in Oracle Database
While performing database operations there are
instances where we need process a set of statements in a single transaction. While
connecting to Oracle database transactions can be handled in 2 ways.
1. Handle transactions in the PL/SQL Package.
2. Handle transactions using the Connection objects Transaction.
1. Handle transactions in the PL/SQL Package.
2. Handle transactions using the Connection objects Transaction.
Here we shall see on how the 2nd approach is
used to handle transactions. To process a transaction with the connection
object we need to follow the below steps.
1.
Open a connection to the target Oracle
database.
2.
Call the BeginTransaction() method
of the connection object, this method will return a transaction object, get the
Transaction object and assign it to the Command Object.
3.
Now you can call the execute methods of the
Command object.
a.
If there are no exceptions then call the Commit()
method of the Transaction object
b.
If there are any exceptions while
processing any of the commands call the Rollback() method, this will
rollback all the statements which were executed since the connection was
opened.
4.
Close the connection.
TThe following code executes 3 SQL statements in a single transaction.
OracleConnection objCon = null;
OracleCommand objCom = null;
OracleTransaction objTrans = null;
int intQueryOut;
try
{
objCon = new OracleConnection(GetConnectionString());
objCom = new OracleCommand(strQuery1,
objCon);
objCon.Open();
objTrans = objCon.BeginTransaction();
//
objCom.Transaction = objTrans;
objCom.ExecuteNonQuery();
//
objCom.CommandText
= "strQuery2";
objCom.ExecuteNonQuery();
objCom.ExecuteNonQuery();
//
objCom.CommandText
= "strQuery3";
objCom.ExecuteNonQuery();
objCom.ExecuteNonQuery();
//
objTrans.Commit();
objTrans.Commit();
}
catch
{
objTrans.Rollback();
throw;
}
finally
{
objCon.Close();
}
In the above code block, the queries strQuery1, strQuery2
& strQuery3 execute in a single transaction, if there is a expection while
processing any of the queries, then the transactions performed by the other 2 queries
will also get rollbacked leaving the database un affected.
That’s it we have seen how to execute a transaction in an
Oracle database using the ADO.Net Transaction.
1 comment:
Good post!
Here is an example of how to do it with N:N relationship
Oracle 11G transactions with .NET
Greetings!
Post a Comment