UseTransactions

There is really no excuse.

Although some prefer transactions deeper in the data layers, for example within stored procedures, I think that it really confines and restricts the archiecture. When designing scalable and more agile systems, one must be able to decide what is best per object, per component, and not have it dictated by how you construct a stored procedure or database tables etc.

So instead of Enterprise Services, or T-SQL transactions why not juts use the MSDTC component on its own. If all you are after is some transaction support for your objects, it makes for a good solution.

MSDTC in little steps


1. Add a reference to System.Transactions.dll in your project
2. Add a block of code surrounding your methods that make database calls.
3. Make sure your firewall, ports are all configured and working

More Detail


1. Add reference
It is in your Assembly List on the Add References Dialog Box

2. Write some code with TransactionScope
Read about TransactionScope here

C#
using (TransactionScope scope = new TransactionScope())
{
    SaveSomething();
    UpdateSomething();
   
    scope.Complete();
}

3. Add %windir%\msdtc.exe to your Allowed Applications in your firewall (firewall.cpl)

4. In Control Panel Component Services
Enable your MSDTC.
Troubleshooting (It will almost always be firewall or security)
Try switching your client machine's firewall off and try again. (just to check, remember to switch it back on again)

Download DTCPing
Run the app on the server and on the client if you have a problem connecting to the DTC. The connection problem can be either way.

A Typical Exception from Connection / Security / Firewall problem is the

"System.Transactions.TransactionManagerCommunicationException" -
Communication with the underlying transaction manager has failed.

A pretty generic exception. You can get it for almost anything related to communication (as the name suggests)

Some general Statistics

Running SQL 2005 Database on Windows Server 2003, and C# .NET 2.0 client on Windows XP
AMD64 3200+ with 1Gb RAM and an Intel 2.8 Test Box

10 Updates and 100 Inserts wrapped in a Transaction
Average guaged from 10 Runs.
Comitted: 10.2 seconds (average time)
Comitted: 9.8 seconds (fastest time)
Rolled Back: 10.6 seconds (average time)
Rolled Back: 10.2 seconds (fastest time)