create Sql TransAction Method on Data.xsd

Question

I've added to my project a dataset item called : myDataSet.xsd After this, I drop tables on myDataSet.xsd file. That's all okey. What I want to do is, to build Sql Transaction's as function on that dataset, how can I do this?

Answer 1

A DataSet is an in-memory structure - there is no direct relationship to SQL or SqlTransaction.

I think what you are probably referring to is performing operations on the generated TableAdapter instances using a SQL transaction.

It's tricky.

Sahil Malik has a reasonably complete primer on the subject (yes, it's really a "subject"). Essentially what it all boils down to is extending the TableAdapter via a partial class with a BeginTransaction method which opens the inner SqlConnection and holds it open and hands you back a SqlTransaction which you then use normally. It's cumbersome, and you have to do this for every TableAdapter. Alternatively, you can use System.Transactions.TransactionScope, but you have to jump through various hoops in order to avoid a promotion to DTC (distributed transaction).

I'd encourage you to take a look at the linked article, rather than me copying and pasting the code in here.

If you have a lot of TableAdapters, or in general need to combine more than one of them in a single transaction, Sahil's approach isn't going to scale too well. In the past (when I still used TableAdapters) I used a method similar to Ryan Whitaker's, which is basically monkey-patching; it uses Reflection for most of the heavy lifting.

Those two are kind of the definitive resources on making TableAdapter code transactionally-safe. Personally, I just wouldn't use a typed DataSet at all any more; it's all but obsolete now that Linq to SQL and EF are around, they are much more powerful, have deferred execution, don't rely exclusively on in-memory structures, and use a Table Data Gateway + Unit of Work pattern that is very easy to get working with transactions. In my experience, if those do not suffice, usually neither will a DataSet and I have to drop down to bare SqlCommand instances. But if for some reason you absolutely must use a DataSet for data access, one of the two aforementioned methods should be what you need.