COM+ Trancation vs Stored Procedure Transactions

[Irfan]

There are several ways of handling Transactions in DotNet. Some of them are

1. Using COM+ Serviced Component.
2. Using ADO .Net
3. using stored procedure

What is the best way of handling Transaction in DotNet Enterprise
Application interms of performance, maintainance and scalability?

How feasible it would be if we go for stored procedure option, considering
our Transaction can span multiple tables?
can we still use other COM+ Services like object pooling etc if we go for
stored procedure option?

Regards,
Irfan

 

[Nicholas Paldino [.NET/C# MVP]]

Irfan,

If you go for the stored procedure option, you can still have the
transaction span multiple tables. Its a matter of where you call the BEGIN
TRANS and END TRANS statements.

If you do not use COM+ for transactioning, then you can still use it for
everything else it provides. You just set the transaction mode to "Not
Supported"

I think that for what you are doing, you might want to go the stored
procedure route, or even the ADO.NET route. I don't think that COM+ is
terribly unperformant when providing transaction services, but it does
provide a flexibility that you don't seem to need (what if you have multiple
resources contributing in the transaction, for example?)

Hope this helps.

 

[Muthu]

The COM+ method is better if used for distributed transaction. If your
application demands for multiple database server/database/tables then you
should use the COM+ serviced environemt. Otherwise it is better and simplet
to use Database Transaction Handling using SP or ADO.Net.

I would suggest the following sequence using ADO.Net

- Cretae Connection
- Begin Transaction
- Call SP
- Verify the return Code
- If return code is success then Commit
- else Rollback


muthu!

 

[Daniel Pratt]

Hi Irfan,

There are several ways of handling Transactions in DotNet. Some of them are

1. Using COM+ Serviced Component.
2. Using ADO .Net
3. using stored procedure

What is the best way of handling Transaction in DotNet Enterprise
Application interms of performance, maintainance and scalability?

How feasible it would be if we go for stored procedure option, considering
our Transaction can span multiple tables?
can we still use other COM+ Services like object pooling etc if we go for
stored procedure option?

I just want to point out that once you've decided to use COM+ component
services anyway, the added performance cost of using COM+ transactions is
negligible:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dncomser/html/entsvcperf.asp

In my opinion, COM+ transactions are a lot easier than coding transactions
yourself. This is particularly true if you ever need a transaction to span
multiple COM+ components or multiple machines.

Regards,
Daniel