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:
- do I need to set the Transaction property of my OracleCommand, and if so, what exactly does this do?
- 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?