What is more efficient -- having a IDbTransaction in the .net code or handling it in the database? Why?
What are the possible scenarios in which either should be used?
+2
A:
When it comes to connection-based transactions (IDbTransaction), the overall performance should be pretty similar - but by handling it in the .NET code you make it possible to conveniently span multiple database operations on the same connection. If you are doing transaction management inside TSQL you should really limit it to the single TSQL query. There may well be an extra round-trip for the begin/end, but that isn't likely to hurt you.
It is pretty rare (these days) that I'd manually write TSQL-based transactions - maybe if I was writing something called directly by the server via an agent (rather than from my own application code).
The bigger difference is between IDbTransaction and TransactionScope see Transactions in .net for more, but the short version is that TransactionScope is slightly slower (depending on the scenario), but can span multiple connections / databases (or other resources).
Marc Gravell
2009-05-13 06:20:07
Thanks Marc ... still I would like to know why would one use IDbTransaction when he can use a transaction in a SQL stored proc
Rashmi Pandit
2009-05-16 06:58:35
Because you might want the IDbTransaction to span multiple atomic operations (that perhaps already exist). You can't do that if the transactions are inside the sproc, but you can if it is just connection based. It is also better from a separation of concerns angle: you move the responsibiliy for transactions away from the sproc, who's job is to update some data (etc).
Marc Gravell
2009-05-16 08:50:44