So, I was looking at a chunk of code and find I have this scenario repeating over and over. All these components work on a single database, same connection string.
using (var trans = new TransactionScope())
{
var ta1 = component1.DS1TableAdapters.table1TableAdapter () ;
var ta2 = component2.DS2TableAdapters.table2TableAdapter () ;
var ta3 = component3.DS3TableAdapters.table3TableAdapter () ;
var ta4 = component4.DS4TableAdapters.table4TableAdapter () ;
ta1.Insert (...) ;
ta2.Update (...) ;
ta3.Delete (...) ;
ta4.Insert (...)
trans.Complete () ;
}
Looking at perfmon and the SQL-profiler I see
BEGIN TRANSACTION
exec sp_executesql ....
exec sp_reset_connection
exec sp_executesql ....
exec sp_reset_connection
exec sp_executesql ....
exec sp_reset_connection
exec sp_executesql ....
exec sp_reset_connection
COMMIT TRANSACTION
Now, I was looking here
http://www.sqldev.net/misc/sp_reset_connection.htm (top hit on google for sp_reset_connection) and it says that
It will abort open transactions
It will defect from a distributed transaction when enlisted
Which can't be the case surely as there is now way the above could work or am I missing something? In perfmon you can see that the transaction remains local and is not promoted to the DTC.
So, anyone know what sp_reset_connection really does? And is relying on the transactionScope the best way to manage the db connection between components like this?
Cheers,
Stephen.