Transactions without setting OleDbCommand.Transaction property

[Osvaldo Bisignano]

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

 

[Frans Bouma [C# MVP]]

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)

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

 

[Kevin Yu [MSFT]]

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."

 

[Angel Saenz-Badillos[MS]]

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://weblogs.asp.net/angelsb/

 

[Osvaldo Bisignano]

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

 

[Osvaldo Bisignano]

Perdón 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

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://weblogs.asp.net/angelsb/

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

 

[Angel Saenz-Badillos[MS]]

Osvaldo,
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/

Perdón 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

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://weblogs.asp.net/angelsb/

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

 

[Osvaldo Bisignano]

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

Osvaldo,
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/

Perdón 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

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://weblogs.asp.net/angelsb/




I have to modify existing code which doesn't use transactions at

all.

It