Monday, April 9, 2012

ADO.Net Transactions in Oracle Database


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.

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.CommandText = "strQuery3";
objCom.ExecuteNonQuery();
//
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.

Search Flipkart Products:
Flipkart.com

1 comment:

Unknown said...

Good post!

Here is an example of how to do it with N:N relationship

Oracle 11G transactions with .NET

Greetings!