Transactions

[asadikhan]

Hi,

I have a program that massages the data in an access database. I have a
total of about ten methods, each one of which has one or two
OleDbCommand objects that are executed. So in the whole application I
have about 10-12 OleDbCommand objects. Note however that I only have an
OleDbConnection object.

What I want to do now is to have a transaction setup such that if any
one of the command executions fail, I roll back all the other commands
that were executed thus far.

What I want to know is if it is possible to achieve this using a single
transaction object for all command objects like:

try {
myConnection.Open();
myTrans = myConnection.BeginTransaction();
myCommand1.Transaction = myTrans;
myCommand1.ExecuteNonQuery();
myCommand2.Transaction = myTrans;
myCommand2.ExecuteNonQuery();
....
myCommand12.Transaction = myTrans;
myCommand12.ExecuteNonQuery();
myTrans.Commit();
}
catch (Exception ex) {
myTrans.Rollback();
}

or do I need a seperate transaction object for every command object?

Asad

 

[Frans Bouma [C# MVP]]

Hi,

I have a program that massages the data in an access database. I have
a total of about ten methods, each one of which has one or two
OleDbCommand objects that are executed. So in the whole application I
have about 10-12 OleDbCommand objects. Note however that I only have
an OleDbConnection object.

What I want to do now is to have a transaction setup such that if any
one of the command executions fail, I roll back all the other commands
that were executed thus far.

What I want to know is if it is possible to achieve this using a
single transaction object for all command objects like:

try {
myConnection.Open();
myTrans = myConnection.BeginTransaction();
myCommand1.Transaction = myTrans;
myCommand1.ExecuteNonQuery();
myCommand2.Transaction = myTrans;
myCommand2.ExecuteNonQuery();
...
myCommand12.Transaction = myTrans;
myCommand12.ExecuteNonQuery();
myTrans.Commit();
}
catch (Exception ex) {
myTrans.Rollback();
}

or do I need a seperate transaction object for every command object?

No, your code looks fine (as far as I can see), I would throw the
exception again though. Swallowing exceptions isn't that good in
general.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------