.NET Transactionscope options

Question

I am a newbie in C#. So i was just wondering if anybody can help me figure out how C# works with transactionscope? Because i am a little confused by the definition of it. However, let me explain about my problem a little bit. So that you will get to know what i am trying to achieve.

I have three table adapter declared for three different dataset like this:

logTableAdapter logAdap = new logTableAdapter();
measTableAdapter measAdap = new measTableAdapter();
valueTableAdapter valueAdap = new valueTableAdapter();

The process to import data is:

1. First I insert a log entry via logAdap.insert() method.
2. Loop through an excel file to grab the measurements and starts inserting via measAdap.insert() method.
3. Foreach measurement i am inserting values via valueAdap.insert() method.

So my question is - since measurement & value has a nested relationship. How can I create a nested transactionscope & when an error occur anywhere (measurement insertion / value insertion) i just want to rollback everything i did. That is i just want to go back to the point before the i inserted the log entry.

Answer 1

Quoting this aptly named article: The definitive TableAdapters + Transactions blog post.

if you are working with plural operations inside one TransactionScope, i.e. “GetData” and “Update” both inside a single TransactionScope, or two Update’s within a TransactionScope, you will effectively open two SqlConnections to the single database, and thus unnecessarily promote the transaction from LTM to MSDTC. As a best practice, ALWAYS wrap only a singular operation inside a TransactionScope. Should you choose to wrap multiple operations inside a single TransactionScope, you must in that case manage connection lifetime yourself by extending the partial class definition. In other words, the following code will cause the transaction to promote –

using (TransactionScope tsc = new TransactionScope())
{
    tableAdap.GetData() ;
    //Do your transactional work.
    tableAdap.Update() ;
    tsc.Complete() ;
}

But the following code is just fine –

using (TransactionScope tsc = new TransactionScope())
{

    tableAdap.OpenConnection() ;
    tableAdap.GetData() ;

    //Do your transactional work.
    tableAdap.Update() ;
    tableAdap.CloseConnection() ;
    tsc.Complete() ;
} 

So you only need one TransactionScope, but with some caveats. Here is the gist, but I encourage you to read through the blog post.

TableAdapters aren't the most suitable data access methodology for high-integrity transactional systems. If you need more reliablity you should probably write your operation as a stored procedure, and execute it from you C# code.