Performing an Oracle Transaction using C# and ODP.

Question

I'm confused. On the face of it, performing a transaction in C# seems simple. From here:

http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleTransactionClass.htm

string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = new OracleConnection(constr);
con.Open();

OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM MyTable";

// Start a transaction
OracleTransaction txn = con.BeginTransaction(
  IsolationLevel.ReadCommitted);

try
{
  // Insert the same row twice into MyTable
  cmd.CommandText = "INSERT INTO MyTable VALUES (1)";
  cmd.ExecuteNonQuery();
  cmd.ExecuteNonQuery(); // This may throw an exception
  txn.Commit();
}....

So, create a connection, begin a transaction on that connection, and then off you go until you want to commit or rollback.

However, other sources, such as here:

https://forums.oracle.com/thread/319121

advocate setting the Transaction property of the OracleCommand object itself. e.g.

cmd.Transaction = txn;

Yet other sources say that this property is read only. It's not actually read only, but nowhere appears to clearly say what it does.

My confusion, therefore, is that the existence of the Transaction property on the OracleCommand object seems to suggest that it should be used to perform that command as part of a transaction, and yet Oracle's own documentation does not use this property. So what is it for?

So my questions are:

1. do I need to set the Transaction property of my OracleCommand, and if so, what exactly does this do?
2. If I've started a transaction on a connection, are ALL subsequent commands performed on that connection (until a commit or rollback) part of that transaction, even if I don't set the Transaction property on those commands?

Answer 1

1) do I need to set the Transaction property of my OracleCommand,

No.

and if so, what exactly does this do?

It's a no-op.

The OracleCommand automatically "reuses" the transaction that is currently active on the command's OracleConnection. The Transaction property is there simply because it was declared in the base class (DbCommand) and you cannot "undeclare" a member in the inherited class. If you read it you'll get the connection's transaction (if any), setting it does nothing.

2) If I've started a transaction on a connection, are ALL subsequent commands performed on that connection (until a commit or rollback) part of that transaction, even if I don't set the Transaction property on those commands?

Exactly.