Rollback for Oracle database : Oracle « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. Oracle

using System;
using System.Data;
using System.Data.OracleClient;

  class Class1{
    static void Main(string[] args)
    {
      string connStr = "User Id=oranetuser; Password=demo; Data Source=oranet";
      OracleConnection oraConn = new OracleConnection(connStr);
      oraConn.Open();
      testRollback(oraConn);
      oraConn.Close();
      oraConn.Dispose();
    }
    private static void testRollback(OracleConnection con)
    {
      string sqlUpdate = "update trans_test ";
      sqlUpdate += "set balance = balance + :amount ";
      sqlUpdate += "where acct_id = :acct_id";

      string sqlSelect = "select acct_id, balance ";
      sqlSelect += "from trans_test ";
      sqlSelect += "where acct_id = :acct_id";

      OracleParameter amount = new OracleParameter();
      amount.DbType = DbType.Decimal;
      amount.Precision = 12;
      amount.Scale = 2;
      amount.Value = 500;
      amount.ParameterName = "amount";

      OracleParameter acct_id = new OracleParameter();
      acct_id.DbType = DbType.Decimal;
      acct_id.Precision = 2;
      acct_id.Value = 2;
      acct_id.ParameterName = "acct_id";

      OracleCommand cmdUpdate = new OracleCommand();
      cmdUpdate.Connection = con;
      cmdUpdate.CommandText = sqlUpdate;

      cmdUpdate.Parameters.Add(amount);
      cmdUpdate.Parameters.Add(acct_id);

      OracleTransaction trans = con.BeginTransaction();
      cmdUpdate.Transaction = trans;

      cmdUpdate.ExecuteNonQuery();

      OracleCommand cmdSelect = new OracleCommand();
      cmdSelect.Connection = con;
      cmdSelect.CommandText = sqlSelect;
      cmdSelect.Transaction = trans;

      OracleParameter acct_id2 = new OracleParameter();
      acct_id2.DbType = DbType.Decimal;
      acct_id2.Precision = 2;
      acct_id2.Value = 2;
      acct_id2.ParameterName = "acct_id";

      cmdSelect.Parameters.Add(acct_id2);

      OracleDataReader reader = cmdSelect.ExecuteReader();

      if (reader.Read())
      {
        Console.WriteLine(reader.GetDecimal(0).ToString());
        Console.WriteLine(reader.GetDecimal(1).ToString());
      }
      trans.Rollback();
      reader.Close();
      reader = cmdSelect.ExecuteReader();

      if (reader.Read())
      {
        Console.WriteLine(reader.GetDecimal(0).ToString());
        Console.WriteLine(reader.GetDecimal(1).ToString());
      }
    }

  }