what is the benefits for each approach

alyeng2000 · December 20th, 2003, 05:46 AM

i am asking what is the benefits for each approach, and what is the best for each of:
first:
sending transaction as a text from Program Side and execute it on the sqlServer, for example

BEGIN TRANSACTION

'statment 1
if @@error<>0 RollBack TRANSACTION

'statment 2
if @@error<>0 RollBack TRANSACTION

COMMIT TRANSACTION

or second:
doing that with code using Connection object(I am using ADO.NET)
and checking exceptions to decide if rollback

cn.BeginTransaction
try
' statment one
' statment two
'......
cn.commit

catch
cn.RollBack
end try

Ahmed Ali
Software Developer

planoie · December 22nd, 2003, 12:10 AM

If you know about potential errors within the SQL it would tend to reason that handling the transaction and possible rollback directly in SQL would be more efficient. I have heard however that the performance of ADO.net SqlClient transaction functionality is close to direct SQL.

Peter
------------------------------------------------------
Work smarter, not harder.

Imar · December 22nd, 2003, 06:29 AM

I think the rule of thumb in scenario's like this is that you should let the database handle the transactions when all of them take place in a single database and database server (that is, you can easily put those statements in a Transaction inside a Stored Procedure).

You should use ADO / ADO.NET transactions when your statements are targeted at different databases and / or database servers or require multiple calls to the same datasource.

The first way will skip the creation of transaction objects on the "client" which will be a bit faster.

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

alyeng2000 · December 22nd, 2003, 11:08 PM

thx perter,Imar

but Imar

Quote:

quote:You should use ADO / ADO.NET transactions when your statements are targeted at different databases and / or database servers .

as you said you should use ado when dealing with multi database servers , i know that sqlServer can do remote transactions.

Quote:

quote:or require multiple calls to the same datasource.

And what is the difference if i do multible calls to the same datasource with the two approachs(why ado.net here).

Ahmed Ali
Software Developer

Imar · December 23rd, 2003, 04:00 AM

SQL Server may be able to do remote transactions, but I was referring to a broader scenario, where you're trying to run a transaction on both Oracle and SQL Server for example.

Executing multiple calls requires you to use ADO.NET transactions, or some other transaction manager. Consider this:

Code:

// ADO.NET
// Execute First Stored Procedure
// Execute Second Stored Procedure


-- Stored Procedures
-- Procedure 1
-- Create Transaction
DELETE FROM SomeTable

-- Procedure 2
-- Create Transaction
UPDATE SomeOtherTable bla bla

In this example, .NET code calls two stored procedures. Each Stored Procedure uses a transaction to protect the integrity of the data the procedure is responsible for. But how do you synch the two calls? How do you roll back the first transaction when the second fails?

Because there is no connection between the two calls to the procedures, you'll need an ADO.NET transaction to run both procedures correctly and safely.

Does this help?

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

alyeng2000 · December 24th, 2003, 01:46 AM

thx Imar but,
scenario you explain could be done using nested transactions.

Ahmed Ali
Software Developer

Imar · December 31st, 2003, 05:48 AM

Well, you asked what the benefits were. Not what the only possible solution is.

There are multiple ways to do the same thing. You can create Transactions in SQL Server, in .NET, or you can even use the Distributed Transactions Coordinator. It's up to you to decide what fits in where.

From what I know about Nested Transactions, I think they are executed by the client (well, at least in the scenario we're describing here, inside a Sproc is different of course). In that respect, I don't think there is much of a difference between Nested Transactions and using a client side transaction, but I could be totally wrong.

Sorry for not responding earlier. Overlooked this message.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.