Developer Forum

I have to modify existing code which doesn't use transactions at all. It
only uses OleDbCommands to execute SQL Statements against the DB.

I know that each of the commands must have their Transaction property
established, but i don't want to modify all of the commands, which are many.

Is there any way to make the Commands ASUME by default the first transaction
started in Connection.BeginTransaction? I mean, like we used to do with ADO
and recordsets?

By the way, all of the sentences are ExecuteNonQuery, so I cannot use
DataAdapter.Update (what would be a kind of transaction)

Thanks in advance,
Osvaldo
Buenos Aires

You have to wire the command with the transaction object. However you can do
that in a method: instead of calling ExecuteNonQuery everywhere, you call a
method, passing the command and transaction object. In that method you wire
the transaction to the command and call ExecuteNonQuery and return the
result. Should be an easy refactor.

FB

--
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.yqcomputer.com/
My .NET Blog: http://www.yqcomputer.com/
Microsoft C# MVP

Thanks for Frans's quick response!

Hi Osvaldo,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to set a default trasaction
for all the executed command objects. If there is any misunderstanding,
please feel free to let me know.

Frans's has provided us with a good advice that we can write a method which
will set transaction property for the command object automatically. When
the transaction is not set, we just set the default transaction to the
property. However, it is not required to set the transaction property. We
can just leave it as null reference and it doesn't execute the command in
any transactions.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Osvaldo,
There is nothing stopping you from using transactions outside of the API.
For example when connecting to Sql Server I can executenonquery a command
with command text="Begin Transaction" and this will start a transaction on
the server outside of the API. All of your commands will automatically run
under this transaction without needing to enlist a transaction object to
them. When you are done you can just executenonquery "commit transaction" to
commit or "rollback transaction" to roll back the changes.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://www.yqcomputer.com/






many.
transaction
ADO

Thank you both, you've understood very well my problem. I understand that it
is logical to specify the transaction for a command object, as the
"nested-point-of-view" is not always usefull. I have added the line
cmd.Transacion = pvtMyTransaction just before calling ExecuteNonQuery.

I'd like to say that I'd preffer to work on a "disconnected model" basis,
using dataadapters, for example, but i didn't write this code i'm working
with.

Till next time,
Osvaldo



which

Perd Angel, but I think you missed that I want to run all (or at least
more than one) commands in the scope of the same transaction, in such a way
that if one of them fails, all of them rollback.
On the other hand, I think that when you run an ExecuteNonQuery, an
automatic transaction is begun automatically, and it is commited when it
reaches the end of the command text.
Correct me if i'm wrong.

Saludos,
Osvaldo





to

svaldo,
There are very few differences between calling the SqlClient
connection.BeginTransaction and executing a command with commandText="Begin
Transaction".

Code like this is perfectly valid:
sqlconnection1.Open();
SqlCommand sqlcommand1 = sqlconnection1.CreateCommand();
sqlcommand1.CommandText = "begin transaction";
sqlcommand1.ExecuteNonQuery();

//everything here will run under this transaction:
sqlcommand1.CommandText = "create table foo123 (myid int)";
sqlcommand1.ExecuteNonQuery(); // -1
SqlCommand sqlcommand2 = sqlconnection1.CreateCommand();
sqlcommand2.CommandText = "insert into foo123 values (1)";
sqlcommand2.ExecuteNonQuery(); // 1
SqlCommand sqlcommand3 = sqlconnection1.CreateCommand();
sqlcommand3.CommandText = "insert into foo123 values (2)";
sqlcommand3.ExecuteNonQuery(); // 1

//It is up to you to Commit or Rollback all the changes that
//your commands have done.
sqlcommand3.CommandText = "rollback transaction";
sqlcommand3.ExecuteNonQuery(); // -1

So what does conn.BeginTransaction buy you? Well you save one database
roundtrip since we will batch the first Begin Transaction command with the
first execute, in this case it will be sent with "create table...". We keep
track of the transaction, so if you leak it we will explicitly roll it back
on connection close. If you leak the transaction in the model above on
connection close we will return the connection to the pool with the
transaction active and it can lock your database for up to 8 minutes under
worst case scenario.

One word of warning, I would HIGHLY recommend you do not mix both API and
client side TSQL transactions. This can easily be a recipe for disaster. If
you stick to either model you will be much better off.

Saludos!

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.
I am now blogging about ADO.NET: http://weblogs.asp.net/angelsb/




"Osvaldo Bisignano" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
way
API.
command
on
run
transaction"
It
with

ery simple and interesting. I'll try that.
Thanks

"Angel Saenz-Badillos[MS]" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...
commandText="Begin
keep
back
If
message
transaction
to
all.
property
are
use