In the current version can i use system.Transactions? specifically the transactionscope object?

This i very important that i can use these features and i cant wait util v2.0, how can i enable this to work in the current version?

I can see nothing that should prevent you from using System.Transactions if your code is generated for .net 2 (VS 2005)

That was my thought, the sql connection object should autodetect the transaction running on the current thread. But i wanted to verify and see if anyone else has used this feature

I thought maybe llblgen autocreating transactions etc...could possibly effect it..

The automatic transaction creation indeed could be affected, but I haven't read up on System.Transactions yet to fully answer your question. I think a little test with a recursive save could be helpful in this. (which creates a new transaction behind the scenes)

It appears it does not work

I haven't looked at system.transactions yet, though could it be that you could replace the context for COM+ in adapter for a home-written class which makes it possible? OR! just start a normal ado.net transaction (adapter.StartTransaction() ) and because a system.transaction' transaction is going on, the transaction is automatically enlisted in that one? (IIRC that should work).

from my understanding its the connection object that auto enlists it, not the transaction object. Hmm...

Answer wrote:

from my understanding its the connection object that auto enlists it, not the transaction object. Hmm...

You could try to create a derived class of DataAccessAdapter and override createphysicalconnection and do enlistment there, but as I said, I haven't studied system.transactions that much yet

Shouldn't it work like this?

/// <summary>
/// Tests the insertion of a Customer entity and an VisitingAddress entity. This is done using a System.Transaction transaction scope. 
/// </summary>
[Test]
public void InsertCustomerTestSimpleWithSystemTransactionTest()
{
    CustomerEntity newCustomer = EntityCreator.CreateNewCustomer( 1 );
    AddressEntity newAddress = EntityCreator.CreateNewAddress( 1 );

    using( TransactionScope ts = new TransactionScope() )
    {
        using(DataAccessAdapter adapter = new DataAccessAdapter())
        {
            // save 2 entities non-recursive. This should be done in one transaction, namely the transaction scope we've started. 
            newCustomer.TestRunId = _testRunID;
            newAddress.TestRunId = _testRunID;
            newCustomer.VisitingAddress = newAddress;
            newCustomer.BillingAddress = newAddress;
            Assert.IsTrue( adapter.SaveEntity( newAddress, true, false ) );
            Assert.IsTrue( adapter.SaveEntity( newCustomer, true, false ) );

            Assert.AreEqual( EntityState.Fetched, newAddress.Fields.State );
            Assert.AreEqual( EntityState.Fetched, newCustomer.Fields.State );
            Assert.IsTrue( (((int)newAddress.Fields[(int)AddressFieldIndex.AddressId].CurrentValue) > 0) );
            Assert.IsTrue( (((int)newCustomer.Fields[(int)CustomerFieldIndex.CustomerId].CurrentValue) > 0) );
            int addressID = newAddress.AddressId;
            int customerID = newCustomer.CustomerId;
            Assert.IsTrue( (newCustomer.VisitingAddressId == addressID) );
            Assert.AreEqual( newAddress, newCustomer.BillingAddress );
            Assert.IsTrue( (newAddress == newCustomer.BillingAddress) );
            Assert.AreEqual( newAddress.ObjectID, newCustomer.VisitingAddress.ObjectID );
            Assert.AreEqual( newAddress.ObjectID, newCustomer.BillingAddress.ObjectID );

            // don't call Complete, everything will be rolled back.
        }
    }

    // test if the data is still there. Shouldn't be as the transaction has been rolled back. 
    using( DataAccessAdapter adapter = new DataAccessAdapter() )
    {
        CustomerEntity fetchedCustomer = new CustomerEntity( newCustomer.CustomerId );
        Assert.IsFalse( adapter.FetchEntity( fetchedCustomer ) );
        AddressEntity fetchedAddress = new AddressEntity( newAddress.AddressId );
        Assert.IsFalse( adapter.FetchEntity( fetchedAddress ) );
    }
}

This is the way it should work, and this should work today with 1.0.2005.1. Please correct me if I'm wrong.

(edit) I just got networked DTC working and the test above works ok. NO changes to the runtime libs required for this. Please let me know if it doesn't work for you.

Hmm. If I start a transaction implicitly inside the adapter (by saving recursively) the transaction is committed, not rolled back...

Edit: Ok I didn't look closely, it works as expected: both with implicit started transactions and with a transaction started manually inside the using (dataaccessadapter) block.

What's not done, and I have no idea how to solve this, is the rollback of entity fields to their previous values IF the transaction in the scope rolls back.

The ADO.NET transaction first succeeded, though the overall transaction rolled back. This means that the ADO.NET transaction inside the db is rolled back as well. BUT that's done on the server level, not on the ADO.NET level.

I don't have an idea how to tap into this at the moment, possibly an exception somewhere to intercept but I've no idea where as I currently don't see it appearing....

Ok, I found a proper solution, however I don't think it's addable easily to the 1.2005.1 code, as the transaction logic inside adapter has to know that it is inside a system.transactions transaction.

If you can live with the rollback of the entities in the db but not in memory, you can use the code today as discussed above.

In v2.0 I'll add a resource manager to the system.transactions transaction and the resource manager contains the adapter or Transaction instance. When the actual transaction is committed, the dataaccessadapter or Transaction is committed or rolled back if applicable, and with that the entities participating. It can be (likely) the Transaction and dataaccessadapter is already out of scope and Disposed, so the code internally has to be altered a bit.

Sounds good frans, i plan on upgrading this project to V2.0 once its released, but for now the current version is working great, and this should get me started.

whats odd is that i did something very simliar to your test code and it threw an error on me...hmmm, ill hve to try again once i a chance.

Otis wrote:

In v2.0 I'll add a resource manager to the system.transactions transaction and the resource manager contains the adapter or Transaction instance. When the actual transaction is committed, the dataaccessadapter or Transaction is committed or rolled back if applicable, and with that the entities participating. It can be (likely) the Transaction and dataaccessadapter is already out of scope and Disposed, so the code internally has to be altered a bit.

Hi. I intend to buy LLBLGen 2.0 and use it with VS2005. I'd like to know if it is working with System.Transactions, TransactionScope, etc. and if it has any important issue or concern I should care.

Thanks,

David

It is implemented now, see the new documentation

Otis wrote:

It is implemented now, see the new documentation

Reading the FAQ, it is written that it only supports System.Transactions with SQL2005. Why?

Because SqlServer 2005 is the only rdbms that supports promoting native transactions to distributed transactions at the moment. (it's also in the docs of system.transactions). Oracle for example needs new features in 10g to support it, so even with their upcoming ODP.NET v10.2 they don't support it.

Otis wrote:

Because SqlServer 2005 is the only rdbms that supports promoting native transactions to distributed transactions at the moment. (it's also in the docs of system.transactions). Oracle for example needs new features in 10g to support it, so even with their upcoming ODP.NET v10.2 they don't support it.

Well, you can use a TransactionScope with SQL Server 2000, however, it will be a DTC transaction automatically. SQL Server 2000 doesn't support light weight transactions, as they call it.

BOb

Oh ok, that's of course true, sorry if I was unclear about that. However with sqlserver 2000, there's not a real difference between using enterprise services and system.transactions.

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

Tx, KB

Moved to new thread: MySQL .NET 1.1 ADO transaction doesn't roll back first of two edits. My apologies for inadvertently hijacking this one.

system.transactions needs COM+/MTS transaction support and furthermore RDBMS functionality to be able to start lightweight transactions. Only sqlserver2005 and oracle 10gR2 do that. mysql doesn't support com+ nor lightweight transactions so you can't use it with mysql.

To be honest, mysql doesn't support acid transactions anyway (it's still possible to get a db in an inconsistent state after an error during the transaction), so I think you're out of luck.

Unfortunately, the work i'm doing is for a client who has chosen MySQL, so I have little chance of getting them to migrate to another RDBMS.

Please let me confirm that i understand you.

Are you saying that LLBLGen does NOT support .NET 1.1 transactions with MySQL because MySQL can't handle them?

Or are you saying that transactions ARE supported in MySQL, but that sometimes bad data results from dropped connections, etc. in any case? Thanks.

ADO.NET transactions are supported on all databases we support, except on MySql 4.x ISAM, as MySql ISAM doesn't support transactions at all.

COM+/System.Transaction transactions (!) are only supported on databases which support COM+ transactions, and MySql isn't one of them. Lightweight transactions are only supported on sqlserver 2005/oracle 10gR2 simply because these databases are the only ones which support lightweight distributed transactions (COM+ transactions via MS DTC which are not as heavy as normal COM+ transactions)

So if you're using MySql innoDB, you should have transactions, otherwise you don't have transactions simply because MYSql doesn't support them.

The transaction support of MySql in general (innodb / mysql 5) isn't always great, there are situations where mysql can make mistakes. That's a problem of MySql, not of our code, we just use ADO.NET and leave it to the RDBMS to actually fulfil the transaction.

Moved to new thread: MySQL .NET 1.1 ADO transaction doesn't roll back first of two edits.

Moved to new thread: MySQL .NET 1.1 ADO transaction doesn't roll back first of two edits.