Transactions with TableAdapters in ADo.Net 2.0

[Brian Smith]

I have a VS2005 C# winforms app connecting to SQL2005. Having discovered
the limitations of the new TableAdapter implementation, and the fact
that if I use System.Transaction on updates to a DataSet with several
related tables then MSDTC is automatically invoked, I've followed
advice from this newsgroup and elsewhere to implement my own methods in
the TableAdapter classes to apply a SQLTransaction object to each
command (much as I used to do with DataAdapters in ADO 1.1).

This seems to work very well, but I'm puzzled by the fact that when I
trace the events in SQL Profiler I do not see any BEGIN TRAN or COMMIT
statements, even though if I artificially induce a rollback the
transaction is definitely completely rolled back as expected. Is there a
different communication channel for transactions with SQL2005?

brian smith

 

[Sahil Malik [MVP C#]]

There isn't a different channel - it's the same TDS you'd use if you were
using SSMS. But it isn't as direct as SqlConnection.BeginTransaction will
end up calling "BEGIN TRAN" .. in fact, the exact behaviors are a bit
different between the two - ADO.NET and TSQL.

Any specific reason why you're worrying about the exact implementation
detail? I've never really had to worry about it so just curious

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx

 

[Brian Smith]

Thanks Sahil. My only concern is that my transactions really are working
- I'm used to seeing them in the Profiler trace - it just gives extra
confidence that I didn't miss out something somewhere in my code (it
happens)

brian

 

[Sahil Malik [MVP C#]]

Oh yes they are working . ADO.NET in fact has checks built in which won't
let you run a SqlCommand without a transaction midway of a transaction
batch - try it.


SM

 

[Miha Markic [MVP C#]]

Yup, the best way to check it out is to actually try it...