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());
}
}
}