Transactions in ADO.NET typed datasets -TransactionScope or DbTransaction

[micklang]

Hi there,

I'm currently trying to come up with a solution for incorporating
transactions into ADO.NET typed datasets.

I was all set to simply use implicit transactions using
TransactionScope as discussed on this page...

http://msdn2.microsoft.com/en-us/library/ms172152.aspx#Mtps_DropDownFilterText

....then I read this post...

http://groups.google.com.au/group/m...2.0+transaction&rnum=1&hl=en#f8908aef63814046

....and now I'm worried my solution won't be performant, as I am indeed
calling stored procs on a SQL Server 2005 database. From reading
this post it seems to suggest that TransactionScope is inappropriate
for my needs as my application requires only transactions across
stored proc calls (not across databases or wcf calls).

I would use DbTransactions as suggested in this post however that
seems to be a ridiculously difficult when working with ADO.NET 2.0
typed DataSets as the code generated does not expose the DataAdapter
or Commands publically, making it impossible to set a transaction on
the commands without implementing some extremely kludgey code. Namely
implementing a partial class for every table in which you copy and
paste code to set a transaction on the commands, which in my case
means an extra 30 classes.

What is the best strategy here (besides turfing ado.net 2.0 typed
datasets and coming up with a sensible solution) ?

Is it really true that using System.Transactions and TransactionScope
would be an inappropriate solution for my situation?

Or is there a way of optimising System.Transactions for my my app to
ensure it stays performant?

Thanks in advance

Michael

 

[Mary Chipman [MSFT]]

If performance is the main criteria, then keeping all transactions on
the server is going to be the best solution. Once you extend the
transactional boundaries outside of the server then you become
dependent on the network, etc. Locks are held longer, negatively
impacting concurrency. Implement transactions in your stored
procedures using BEGIN TRANSACTION, etc. You can nest stored
procedures if so desired. System.Transactions is designed for
distributed transactions, so if your transactions are all in one
database or even on one server, then you don't need it.

--Mary