Hi,

I'm wondering what the official Microsoft recommendation is for handling
TransactionScope transactions when using TableAdapters within a DataSet.

I have a dataset that various areas of my business logic uses, and at a
higher level I'd like to wrap several business functions in a
TransactionScope transaction.

The problem is, each TableAdapter has an InitConnection method generated
that creates a _new_ Connection to the database. If a second connection gets
used within the TransactionScope, the transaction will needlessly be promoted
to a distributed transaction.

My current workaround is to alter the Dataset.Designer.cs generated file
manually to change how Connections are created, but that's somewhat painful
sin ce the file gets regenerated automatically.

Thanks for any advice,

Kirk

Kirk -

Prefer not to use TransactionScope with TableAdapters or DataAdapters.
Next month I have an article appearing in code-magazine which explains the
details of "why not". The explanation is kinda long drawn (the article is a
good 20 pages long), for now - just don't do it. (like EKIN)

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



Show quoteHide quote

Thanks Sahil,

That's the conclusion that I'm forming too, but I'd like a Microsoft
viewpoint. I'd rather not rearchitect my app to remove either Datasets or
TransactionScope, and I'm trying to avoid the performance hit of a
distributed transaction for no gain.

Kirk

Kirk,

If you are willing to put in the effort to read through undigested
information, please view a conversation between myself and Jim Johnson (who
is from the Sys.Tx team) here -
http://pluralsight.com/blogs/jimjohn/archive/2005/09/15/14838.aspx
Also, I have discussed this particular issue with a MS employees from both
Sys.Tx and DataWorks team regarding a few problem scenarios with
TableAdapter/DataAdapter with TxScope. So I'd be very surprised if this
isn't MS backed, though I can't speak for them :).

Now of course there are borderline cases where it may be acceptable to use
TableAdapters/DataAdapters with Sys.Tx. Here is an example - Calling in
Update on SQL2k5, where you manage the connection's opening & closing - is a
perfectly acceptable use of TableAdapter/DataAdapter. But as a best
practice, When using TxScope with DataAdapter,

- don't wrap Update and Fill within the same TxScope (this is especially
true with the second point)
- don't let the DataAdapter manage the connex for you.
- it makes very little sense to use it with Sql2k - because the transaction
will promote even with a single connection.

There's more to it than this, watch out for my upcoming article in
code-magazine. Also, Chap 11 in my book talks about all this in depth.

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


Show quoteHide quote

What causes SqlCommandBuilder to not build commands?
date/time fields
VB.NET - How do I return an AutoNumber from an Insert Statement using the DataGridView
Updating Database where Field Column Name has a space in it
How to retrieve all sql server errors on ADO.NET?
How do you pass NULLs to SQL via an Insert Statment
Combobox Lookup
ADO.NET 2.0 TableAdapter Configuration Wizard
JOIN on 2 datasets?
Default DateTime Value in a column