.NET Transactionscope options

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.

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.

you don't need more than one TransactionScope, you can do everything in the same one I think.

As I think that what you were looking for is how to use the TransactonScope, here is what your code would look like, modifying a little bit the example in your comment:

using( TransactionScope ts = new TransactionScope() ) { 
    try 
    { 
        logAdap.InsertLog(.....);

        foreach (.....)
        {
            measAdap.InsertMeas(.....); 
            foreach (.....)
            {
                valAdap.InsertVal(.....);
            }
        }

        // Complete the transaction
        ts.Complete();
    }
    catch (Exception ex) 
    { 
        // Your error handling here.
        // No need to rollback each table adapter. That along with all the 
        // transaction is done for you when exiting the using block without 
        // calling Complete() on the TransactionScope.
    }}

This way of using the scope is called implicit transactions and you can get a good overview of that in this MSDN article: Implementing an Implicit Transaction using Transaction Scope.

Having said that, what fencliff mentions is true, as you probably don't want to open multiple connections for your scenario, saving valuable database resources.