.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:

First I insert a log entry via logAdap.insert() method.
Loop through an excel file to grab the measurements and starts inserting via measAdap.insert() method.
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.

BTW there's no such thing as "C#.NET". It's just "C#". – John Saunders Feb 28 '11 at 20:12 — John Saunders, Feb 28 '11 at 20:12

score 3 · Answer 1 · answered Feb 28 '11 at 20:18

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.

score 0 · Answer 2 · answered Feb 28 '11 at 20:11

0

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

answered Feb 28 '11 at 20:11

Davide Piras

43,984
10
98
147

So you think the pseudo logic would be something like this: `code` using(TransactionScope ts = new TransactionScope()) { try { foreach (.....) { measAdap.InsertMeas(.....); foreach (.....) { valAdap.InsertVal(.....); } } measAdap.Commit() ; valAdap.Commit(); } catch (Exception ex) { measAdap.Rollback(); valAdap.Rollback(); } } `code` – Mystic Feb 28 '11 at 23:31
@Mystic using that logic the transaction gets promoted to using the distributed transaction coordinator. This SO answer tells you why you probably don't want that: http://bit.ly/gGampm - see my answer for workaround. – jevakallio Feb 28 '11 at 23:39

score 0 · Answer 3 · answered Mar 07 '11 at 01:51

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.

.NET Transactionscope options

3 Answers3