The following code does not seem to work as expected. Changes to the entity are always committed.

using (TransactionScope tx = new TransactionScope())
{
     using (DataAccessAdapter adapter = new DataAccessAdapter())
     {
          adapter.SaveEntity(entity, false, false);
     }
}

Sorry, I wanted to bookmark this thread but accidentally marked it as "done". I found that there is no way I could clear the "done" flag except by posting a new message.

Oracle has no support for lightweight transactions. (Oracle confirmed to me that they need to make changes to 10g to make it possible so it will be in a future version). So use a normal llblgenpro transaction instead, or COM+ /enterprise services transactions.

System.Transactions works with sqlserver2005.

is this also the case for MySQL 5.x?

i.e. aren't lightweight transactions supported by MySQL?

or is SQLServer the only database which supports .NET 2.0 TransactionScope

In the following test code (using an entity called a Tutor), i intentionally comment out the ts.Complete() calls to try and make the whole transaction fail. The first (main test) method creates a new transaction (RequiresNew), the other methods should use the existing transaction (Required) from the first method.

The results of my test are:

1) the first edit (Tutor.Ssn) remains committedd in the DB 2) the second (Tutor.DateAdded) throws an exception 3) The exception is not thrown where it should be (see bold comments in code below)

The first edit should get rolled back at the missing Complete() call where the using block goes out of scope, no?

Thanks for your help,

KB

    [Test]
    public void TestTutorEditTransactional()
    {
        try
        {
            using (TransactionScope ts = new TransactionScope(TransactionScopeOption.RequiresNew))
            {
                using (DataAccessAdapter adapter = MockDataAdapter.GetAdapter())
                {                       
                    this.theTutor = new MockTutor("Trans", "Dude", "Action", "123-12-1234", "M", 0); //new TutorsEntity();                      

                    this.theTutor.TutorEntity.Tid = 7;                      
                    if (this.theTutor.Exists())
                    {
                        this.EditSsn("123-12-4444");
                        this.EditDateAdded(DateTime.Today.AddDays(3));
                    }
                }
                ts.Complete(); // commit transaction
            }
        }
        catch (Exception ormException)
        {
            Trace.Write(ormException.ToString());
        }
    }

    public void EditSsn(string testValue)
    {
        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
        {
            using (DataAccessAdapter adapter = MockDataAdapter.GetAdapter())
            {
                PredicateExpression filter = new PredicateExpression();
                this.theTutor.TutorEntity.Ssn = testValue;                  
                adapter.SaveEntity(theTutor.TutorEntity, true, filter);
            }
            //ts.Complete(); // each sub-transaction must complete or root transaction will fail

        }

** // ^^ missing ts.Complete() call should immediately throw exception here ** }

    public void EditDateAdded(DateTime testValue)
    {
        using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))

** // ^^ exception thrown on next use of ts, instead of above at missing ts.Complete()** { using (DataAccessAdapter adapter = MockDataAdapter.GetAdapter()) { PredicateExpression filter = new PredicateExpression(); this.theTutor.TutorEntity.Dateadded = testValue; adapter.SaveEntity(theTutor.TutorEntity, true, filter); } //ts.Complete(); // each sub-transaction must complete or root transaction will fail } }

Sorry, just read the General.System.Transactions thread where you answer this question.

any word on if/when MySQL 5.x will support TransactionScope (lightweight)?

Thanks, KB

This question was posted in another thread, so I'll close this thread so we can follow up here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5220