ADO.NET OracleTransaction

 ADO.NET OracleTransaction 

 In this post we are exploring more about OracleTransaction class. We use an object of the OracleTransaction class to represent a database transaction in a Oracle database..

  OracleTransaction Properties:

Sr.No	Property	Type	Description
1)	Connection	OracleConnection	Gets the connection for the transaction.
2)	IsolationLevel	IsolationLevel	Gets the isolation level for the transaction.

  OracleTransaction Methods:

Sr.No	Method	Return Type	Description
1)	Commit()	void	Performs a commit to permanently record the SQL statements in the transaction.
2)	Rollback()	void	Performs a rollback to undo the SQL statements in the transaction.
3)	Save()	void	Creates a save point in the transaction that can be used to undo a portion of that transaction. The string passed to this method specifies the save point name. You can then roll back the transaction to that save point.

 In Blow program we see how to use Transaction rollback and commit opeartion. Two INSERT statements are performed in the transaction. Each INSERT statement adds a row to the Department table. At the end of the program, the transaction is committed.

  Code: Program.cs 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OracleClient;

namespace Ado_Transaction
{
    class Program
    {
        public static void Main()
        {
           // formulate a string containing the details of the
            // database connection
            string connectionString = "data source=LearnAsp;user id=LearnAsp;password=LearnAsp;"+
            "Pooling=true; Max pool size=10; Min pool size=2;";
            // create a Connection object to connect to the
            // database, passing the connection string to the constructor
            OracleConnection myConn = new OracleConnection(connectionString);

            // open the database connection using the
            // Open() method of the Connection object
            myConn.Open();

            // step 1: create a Transaction object and start the transaction
            // by calling the BeginTransaction() method of the Connection
            // object
            OracleTransaction myTransaction = myConn.BeginTransaction();

            // step 2: create a Command object to hold a SQL statement
            OracleCommand myCommand = myConn.CreateCommand();

            // step 3: set the Transaction property for the Command object
            myCommand.Transaction = myTransaction;

            // step 4: formulate a string containing the first INSERT statement
            string insertString = "INSERT INTO dept (" + " deptno,dname,loc" +
            ") VALUES (" + " '80', 'Test3', 'Test3'" + ")";

            // step 5: set the CommandText property of the SqlCommand object to
            // the INSERT string
            myCommand.CommandText = insertString;

            // step 6: run the first INSERT statement
            Console.WriteLine("Running first INSERT statement");
            myCommand.ExecuteNonQuery();

            // step 7: formulate a second INSERT statement
            insertString = "INSERT INTO dept (" + " deptno,dname,loc" +
             ") VALUES (" + " '90', 'Test4', 'Test4'" + ")";
            
             // step 8: set the CommandText property of the SqlCommand object to
            // the second INSERT string
            myCommand.CommandText = insertString;

            // step 9: run the second INSERT statement
            Console.WriteLine("Running second INSERT statement");
            myCommand.ExecuteNonQuery();

            // step 10: commit the transaction using the Commit() method
            // of the SqlTransaction object
            Console.WriteLine("Committing transaction");
            myTransaction.Commit();

            // close the database connection using the Close() method
            // of the Connection object
            myConn.Close();
        }
    }
}

  Output: