(Thread is continuation of this thread: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=54842&ThreadID=5220 -- Otis )

I've generated entities with LLBLGen version 2.0.0.0 Final (Dec. 6th, 2006 build).

The following code uses .NET 1.1 transactions with MySQL 5.1.11, but it doesn't roll back my first edit (see bold below) when i intentionally throw an exception during the edit of the second value.

This example uses an entity called Tutor through an facade class called MockTutor, but you could test it on any old Entity2.

public class MockDataAdapter
{
        private static DataAccessAdapter da = new DataAccessAdapter(kConnectionString, true);
        public static DataAccessAdapter GetAdapter()
        {
            return da;
        }
  }

class TestEntityDotnet1_Transaction
{
    MockTutor theTutor = null;

    public TestEntityDotnet1_Transaction(MockTutor aTutor)
    {
        this.theTutor = aTutor;
    }

    public void TestTutorEditTransactional()
    {
        **DataAccessAdapter adapter = null;**
        try
        {
            **adapter = MockDataAdapter.GetAdapter();**

            // start the transaction.
            **adapter.StartTransaction**(System.Data.IsolationLevel.ReadCommitted, "TwoUpates");

            this.theTutor = new MockTutor("Trans", "Dude", "Action", "123-12-1234", "M", 0);

            this.theTutor.TutorEntity.Tid = 7;
            if (this.theTutor.Exists())
            {
                this.EditSsn("123-12-2222"); // this edit should be rolled back, but it's not
                this.EditDateAdded(DateTime.Today.AddDays(1));
            }
            // done
            adapter.Commit();
        }
        catch (Exception ormException)
        {
            Trace.Write(ormException.ToString());

            // abort, roll back the transaction
            adapter.Rollback();
            // test thrown exception is caught & Rollback is called, but edited value stays in db
        }
        finally
        {
            // clean up. Necessary action.
            adapter.Dispose();
        }
    }

    public void EditSsn(string testValue)
    {
            using (DataAccessAdapter adapter = MockDataAdapter.GetAdapter())
            {
                PredicateExpression filter = new PredicateExpression();
                this.theTutor.TutorEntity.Ssn = testValue;
                **adapter.SaveEntity**(theTutor.TutorEntity, true, filter);
                **// this save goes straight to the database and doesn't get rolled back**
            }
    }

    public void EditFirstName(string testValue)
    {
        using (DataAccessAdapter adapter = MockDataAdapter.GetAdapter())
        {
            PredicateExpression filter = new PredicateExpression();
            this.theTutor.TutorEntity.Fname = testValue;
            **//intentionally try to fail transaction by throwing exception**
            **throw new Exception("Test Transaction fail on second edit");**
            adapter.SaveEntity(theTutor.TutorEntity, true, filter);
        }
    }
}

Looking at your testcode.... I don't see what it SHOULD do that it doesn't... I see code which edits an entity and then commits the transaction. That doesn't happen?

The edits work as individual edits, not as a transaction. They go into the database one by one immediately on adapter.Save() and the first is not rolled back if i throw an exception during the second.

You edit an entity's field. That ONLY breaks the transaction if that throws an exception. does it throw that exception or not? If it doesn't throw an exception the code of course ends up at the Commit call.

    // the second edit intentionally throws an exception to break the transaction
    // Rollback() is called but the first edit doesn't get rolled back
    public void EditFirstName(string testValue)
    {
        using (DataAccessAdapter adapter = MockDataAdapter.GetAdapter())
        {
            PredicateExpression filter = new PredicateExpression();
            this.theTutor.TutorEntity.Fname = testValue;
            //intentionally try to fail transaction by throwing exception
            **throw new Exception("Test Transaction fail on second edit");**
            adapter.SaveEntity(theTutor.TutorEntity, true, filter);
         }
     }

I also don't see any save actions in your code. You start a transaction on an Adapter object, but it's not used in any call after the transaction start till the Commit happens. Normal ado.net transactions aren't global per thread, you actually have to use the adapter object which has the transaction to perform db access so that access takes place inside the transaction.action.

I'm using the same adapter, no? I call adapter.StartTransaction() and adapter.SaveEntity() on the same adapter. Perhaps I confused matters by posting three different test classes? This first one is the original Entity-Adapter test. I'll attach the two other test classes which I used to isolate the problem by going down to the lowest level and working back up:

1) CoreLab MySQL driver-level test: low-level transactions: test passed

2) ADO test: .NET 1.1 ADO transactions: test passed

3) Entity test: LLBLGen DataAccessAdapter: test failed

First, please, PLEASE, keep everything in a SINGLE thread. You've now started a new thread and it continues from an older one..

Also:

...             adapter = MockDataAdapter.GetAdapter();

                // start the transaction.
                adapter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, "TwoUpates");

                this.theTutor = new MockTutor("Trans", "Dude", "Action", "123-12-1234", "M", 0);

                this.theTutor.TutorEntity.Tid = 7;
                if (this.theTutor.Exists())
                {
                    this.EditSsn("123-12-2222"); // this edit should be rolled back, but it's not
                    this.EditDateAdded(DateTime.Today.AddDays(1));
                }
                // done
                adapter.Commit();
...

In there, all I see is you start a transaction on SOME adapter and then call some method which apparently also gets an adapter, but is that the same instance? I don't know.

All our code does is start a transaction on the open connection and roll it back if you call Rollback. What is MockDataAdapter.GetAdapter() doing? Creating a new dataaccessadapter? Creating a mock?

If you simply do (NO MOCKS! please use REAL code. ) -> using(DataAccessAdapter adapter = new DataAccessAdapter()) { // create some entity here and fill it with data // ...

// then save it in a transaction:
adapter.StartTransaction(IsolationLevel.ReadCommitted);
adapter.SaveEntity(theEntity);
adapter.Rollback();

}

is the entity then saved or not? IF not, and you change Rollback to Commit() is it then saved?

(edit) I'll look at your code tomorrow (wednesday) to see if can find oddness. Also I'll re-try to repro it with my own mysql code. Still I'd like you to do the little test above, WITHOUT mocks.

First, please, PLEASE, keep everything in a SINGLE thread. You've now started a new thread and it continues from an older one.

Sorry, but i'd understdood your earlier remark about hijacking the thread to mean that you would prefer my issue in a separate thread. I'm doing my best to remain calm and polite and helpful. I ask that you do the same and remember that you are providing service to a paying customer who is helping you debug an issue free of charge.

In there, all I see is you start a transaction on SOME adapter and then call some method which apparently also gets an adapter, but is that the same instance? I don't know.

I'm sorry that you find my code difficult to follow. I'm using a simple facade (Mock) design pattern and calling member functions using explicit "this." notation. I'll do as you suggest and simplify the test to a single method.

kbelange wrote:

First, please, PLEASE, keep everything in a SINGLE thread. You've now started a new thread and it continues from an older one.

Sorry, but i'd understdood your earlier remark about hijacking the thread to mean that you would prefer my issue in a separate thread. I'm doing my best to remain calm and polite and helpful. I ask that you do the same and remember that you are providing service to a paying customer who is helping you debug an issue free of charge.

I was mentioning that for the next time. Now overview is lost a bit. I've added the link to the tread in your post at the top. So sorry for the confusion, we misunderstood eachother.

About the issue: the code you posted (I haven't looked into the attached code yet) in the thread posts was confusing to me, so didn't understand what was going on. I hope the code you attached gives more clarity. I also would like you to perform the little test I asked you. That will likely give more insight in what's going on.

Oh, and I know what I am providing, please let's focus on the issue to get it sorted

(Edited out connection string info -- Otis)

Good news, finally. First off, the following simple example below (1) works as expected. Thanks for keeping me honest and reminding me not to trust my mock turtles.

Secondly, I've isolated the problem to be TransactionScope-style using blocks. The second example class below (2) works unless you uncomment the using (DataAccessAdapter) blocks (and adjust their contents to use the block-scope adapter).

This second example shows that a member-scope adapter & filter work just as well as local ones, but the using block-scope adapter is not recognized as belonging to the same transaction.

It was my understanding that C# would simply manage the block scope variable as a reference to the member-scope adapter, but this doesn't seem to be the case. Is it really creating a local memberwise copy? Yuck.

In case it's not painfully obvious, I fell into this hole by starting with TransactionScope .NET 2.0 code and then backing it out to work with .NET 1.1 ADO transactions. My bad. Mea culpa, etc.

Thanks again for your help. Glad we were able to re-focus on the issue and get it sorted.

example (1): local scope: test passed class TestSimpleEntityDotnet1_Transaction { private const string kConnectionString = "Server=IP;Database=db_name;User ID=user;Password=password";

    public void TestTutorEditTransactional()
    {
        DataAccessAdapter adapter = new DataAccessAdapter(kConnectionString, true);
        try
        {
            // start the transaction.
            adapter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, "TwoUpates");

            TutorApp.EntityClasses.TutorsEntity theTutor = new TutorsEntity();
            theTutor.Tid = 7;

            // see if entity exists
            PredicateExpression filter = new PredicateExpression();
            filter.Add(PredicateFactory.CompareValue(TutorsFieldIndex.Tid, ComparisonOperator.Equal, theTutor.Tid));
            bool tutorExists = adapter.FetchEntityUsingUniqueConstraint(theTutor, filter);

            if (tutorExists)
            {
                theTutor.Ssn = "123-12-2222";
                adapter.SaveEntity(theTutor, true, filter);

                theTutor.Fname = "Kevin";
                throw new Exception("Test Transaction fail on second edit");
                adapter.SaveEntity(theTutor, true, filter);
            }

            // done
            adapter.Commit();
        }
        catch (Exception ormException)
        {
            Trace.Write(ormException.ToString());

            // abort, roll back the transaction
            adapter.Rollback();
        }
        finally
        {
            // clean up. Necessary action.
            adapter.Dispose();
        }
    }
}

example (2): class member scope: test passed (but fails with using blocks)

class TestEntityDotnet1_Transaction
{
    private const string kConnectionString = "Server=IP;Database=DB;User ID=user;Password=********";

    private TutorsEntity theTutor = new TutorsEntity();
    private DataAccessAdapter da = new DataAccessAdapter(kConnectionString, true);
    private PredicateExpression filter = new PredicateExpression();

    public DataAccessAdapter Adapter
    {
        get
        {
            return da;
        }
    }

    public PredicateExpression Filter
    {
        get
        {                               
            return filter;
        }
    }

    public bool Exists()
    {
        bool tutorExists = false;
        if (theTutor.Tid != 0)
        {
            //using (DataAccessAdapter adapter = this.Adapter)
            //{
                tutorExists = this.Adapter.FetchEntityUsingUniqueConstraint(this.theTutor, this.Filter);
            //}
        }
        return tutorExists;
    }

    public void TestTutorEditTransactional()
    {
        try
        {
            // start the transaction.
            this.Adapter.StartTransaction(System.Data.IsolationLevel.ReadCommitted, "TwoUpates");
            this.theTutor.Tid = 7;
            filter.Add(PredicateFactory.CompareValue(TutorsFieldIndex.Tid, ComparisonOperator.Equal, this.theTutor.Tid));
            if (this.Exists())
            {
                this.EditSsn("123-12-2222");
                this.EditFirstName("Dean");
                //this.EditDateAdded(DateTime.Today.AddDays(1));
            }
            // done
            this.Adapter.Commit();
        }
        catch (Exception ormException)
        {
            Trace.Write(ormException.ToString());

            // abort, roll back the transaction
            this.Adapter.Rollback();
        }
        finally
        {
            // clean up. Necessary action.
            this.Adapter.Dispose();
        }
    }

    public void EditSsn(string testValue)
    {
        //using (DataAccessAdapter adapter = this.Adapter)
        //{             
            this.theTutor.Ssn = testValue;
            this.Adapter.SaveEntity(theTutor, true, this.Filter);
        //}
    }

    public void EditFirstName(string testValue)
    {
        //using (DataAccessAdapter adapter = this.Adapter)
        //{             
            this.theTutor.Fname = testValue;
            throw new Exception("Test Transaction fail on second edit");
            this.Adapter.SaveEntity(theTutor, true, this.Filter);
        //}
    }

    public void EditDateAdded(DateTime testValue)
    {
        //using (DataAccessAdapter adapter = this.Adapter)
        //{             
            this.theTutor.Dateadded = testValue;
            throw new Exception("Test Transaction fail on second edit");
            this.Adapter.SaveEntity(theTutor, true, this.Filter);
        //}
    }
}

Thanks for the feedback.

Please note that the using bloack would dispose the declared variable as soon as it reaches its closing }.

So the secong using block would actually use a disposed adapter if the disposing hadn't been issued on a local copy in the first using block.

kbelange wrote:

Good news, finally. First off, the following simple example below (1) works as expected. Thanks for keeping me honest and reminding me not to trust my mock turtles.

A mistake is easily made and can often be caused by something very tiny, that's why I wanted you to test the straight forward code without mocks to rule uit any glitches in these . Glad it's sorted out.

I'll comment on some details you brought up below to follow up on Walaa's post.

It was my understanding that C# would simply manage the block scope variable as a reference to the member-scope adapter, but this doesn't seem to be the case. Is it really creating a local memberwise copy? Yuck.

I don't think it's doing a memberwise copy, it's just what Walaa says: the using block will simply dispose the object at hand which actually is what's used in another routine as well as all routines share the same instance .

In case it's not painfully obvious, I fell into this hole by starting with TransactionScope .NET 2.0 code and then backing it out to work with .NET 1.1 ADO transactions. My bad. Mea culpa, etc.

Don't blame yourself, it's not very clear what's going on with System.Transactions and TransactionScope: does it always work? Just in sqlserver? etc.

LLBLGen Pro's runtime contains a resource manager which makes sure that an adapter enlists itself in the transaction scope it's in and will get notified when the transaction scope commits. The thing with transactionscopes is that no ado.net transaction is used: it's left to the transaction scope to start and commit the transaction as the transaction scope is the controlling transaction engine (actually MSDTC but for the developer, it looks as if the transactionscope manages everything), not the RDBMS. This thus means that if the ADO.NET provider doesnt have support for system.transactions (I have no idea if corelab's stuff has or hasn't, the documentation doesn't contain any word about transactionscope, so I guess it doesn't support it (only Oracle 10g R2 and sqlserver 2005 do)), it won't commit anything nor even start a transaction!

I think that's also why you didn't see anything roll back because there was no transaction going on: the adapter might have notified a transaction scope, but the scope itself wasn't able to physically commit anything.

I removed the attachment and the connection string data you had in your post, as that might contain data for logging in onto your server.