ADO.NET Transaction

 ADO.NET Transaction 

 In this post we see how we can group sql statements together into transactions. The transaction is committed or rolled back as 1 unit.We can use a OracleTransaction object to represent a transaction in ADO.NET with a Oracle database.

 When using a transaction to run two INSERT statements, we can use the following steps:

1)	Create a OracleTransaction object and start the transaction by calling the BeginTransaction() method of our OracleConnection object.
2)	Create a OracleCommand object to hold our SQL statement.
3)	Set the Transaction property for our OracleCommand object.
4)	Create a string containing our first INSERT statement.
5)	Set the CommandText property of our OracleCommand object to the first INSERT string.
6)	Run the first INSERT statement using the ExecuteNonQuery() method of our OracleCommand object.
7)	Create a string contain our second INSERT statement.
8)	Set the CommandText property of our SqlCommand object to the second INSERT string.
9)	Run the second INSERT statement using the ExecuteNonQuery() method of our OracleCommand object.
10)	Commit the transaction using the Commit() method of our OracleTransaction object. This makes the two new rows added by the INSERT statements permanent in the database. We can use the Rollback() method to undo the INSERT statements.
11)	Close the database connection.

 In Blow program illustrates aboves steps. 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=200; Min pool size=0;";
            // 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: