SQL Server Transactions

Question

When to use ado.net and SQL Server transactions?

Are there any reasons why one would choose to use ado.net transactions over SQL Server transactions when there is only one SP or query fired against the database within that transactions.

Answer 1

What if you have multiple resources contributing in the transaction then use ADO.NET (and let DTC/Enterprise Services manage it for you), else can use mssql transaction from the SP.

Answer 2

I wouldn't use ADO.NET transactions if using one call to one stored proc which does one query. The purpose of the ADO.NET transaction would be to wrap the execution of multiple calls in to a single transaction. Your case does not fall in to that criteria.