Hi all,

I'm having a little problem with transactions:

I need to save two entities where the second entity has a foreign key on the primary key of the first entity.

the code i wrote appears like this (visual basic):

MyAdapter.StartTransaction(IsolationLevel.ReadCommitted,"mytransaction") Try Dim e1 as new Entity1Entity ...e1 attributes MyAdapter.SaveEntity(e1) dim e2 as new Entity2Entity e2.foreignkey=e1.primarykey ...e2 attributes MyAdapter.SaveEntity(e2) MyAdapter.Commit() Catch MyAdapter.Rollback("mytransaction") Dim myerror as string="my error message" Throw new exception(myerror) Finally MyAdapter.CloseConnection() MyAdapter.Dispose() End Try

everything works well, except if I set:

e2.foreignkey=999 (an entity1 with this id does not exist)

just to cause an error on the second save...

the server responds with "This SqlTransaction has completed; it is no longer usable."

instead of my exception.

the problem is in the "finally" clause... why my exception get lost?

thanks a lot.

Pier

On which line does the exception occur: teh close connection or the dispose? The closeconnection isn't necessary, that's done with commit or rollback.

thanks Otis,

I've removed the adapter.CloseConnection(),

but the problem is still there...

can I remove adapter.Dispose() too and rely on .net garbage collection?

this means i will remove the "finally" clause at all.

pier wrote:

thanks Otis,

I've removed the adapter.CloseConnection(),

but the problem is still there...

can I remove adapter.Dispose() too and rely on .net garbage collection?

this means i will remove the "finally" clause at all.

You can, but it will probably take longer to dispose the contents of the transaction object. Could you paste a stacktrace of the exception?

Here is the stacktrace

Server stack trace: at System.Data.SqlClient.SqlTransaction.Rollback() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.Rollback() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.Dispose(Boolean isDisposing) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.Dispose() 1) at MyProject.PersoneSocieta.Persona.Aggiungi(PersonaEntity Persona) in C:\Lavoro\new_project\sorgente\server\business layer\my_project\PersoneSocieta\Persona.vb:line 99 at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(MethodBase mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)

Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at MyProject.PersoneSocieta.IPersona.Aggiungi(PersonaEntity Persona) 2) at importa_e_inserimento_persona.test_inserimento_persona.Button1_Click(Object sender, EventArgs e) in C:\Lavoro\new_project\sorgente\client\form per test funzionalità\importa e inserimento persona\importa e inserimento persona\test inserimento persona.vb:line 223

notes: 1) this is the adapter.Dispose() line 2) this is the client application (remoting iis/soap)

it seems like it is trying to do another rollback on the dispose command... but I already did a rollback.

Thanks.

I can't reproduce it. This test succeeds:

[Test]
[ExpectedException(typeof(ApplicationException))]
public void ExternalTransactionFailTest()
{
    DataAccessAdapter adapter = new DataAccessAdapter();

    try
    {
        adapter.StartTransaction(IsolationLevel.ReadCommitted, "TestTran");

        // first save a customer.
        CustomerEntity newCustomer1 = EntityCreator.CreateNewCustomer(1);
        newCustomer1.TestRunId = _testRunID;
        AddressEntity newAddress = EntityCreator.CreateNewAddress(1);
        newAddress.TestRunId = _testRunID;
        newCustomer1.VisitingAddress = newAddress;
        newCustomer1.BillingAddress = newAddress;
        bool result = adapter.SaveEntity(newCustomer1);

        // second save of a customer, this should fail.
        CustomerEntity newCustomer2 = EntityCreator.CreateNewCustomer(2);
        newCustomer2.TestRunId = _testRunID;
        newCustomer2.VisitingAddressId = 999;
        newCustomer2.BillingAddressId = 999;    // not there, will fail.
        result = adapter.SaveEntity(newCustomer2);
        adapter.Commit();
    }
    catch(ORMQueryExecutionException ex)
    {
        adapter.Rollback();
        throw new ApplicationException("Transaction failed", ex);
    }
    catch(Exception ex)
    {
        throw ex;
    }
    finally
    {
        adapter.Dispose();
    }
}

which more or less mimics what you're doing.

Dispose performs a rollback only if the transaction isn't rolled back / committed yet. Rollback and Commit both call Reset, which resets the transaction and all its parameters. Dispose then sees no transaction in progress and doesn't perform the rollback.

So I'm a bit confused what's going on. Silly question: is your service build with the wrong version (i.e.: .NET 1.0 ) of the ormsupport classes instead of the .NET 1.1 version?

(edit). I see you do: MyAdapter.SaveEntity(e1) dim e2 as new Entity2Entity e2.foreignkey=e1.primarykey

This gives an ORMEntityOutOfSyncException, as you save e1 without fetching it back. So the second save never happens. When I change: newCustomer2.VisitingAddressId = 999; into newCustomer2.VisitingAddressId = newCustomer1.VisitingAddressId; I do the same as you do, but I get an ORMEntityOutOfSyncException, and not the error you're running into...

my fault!

on my first post I wrote:

Catch MyAdapter.Rollback("mytransaction")

but

the argument for rollback is savepoint name not transaction name, here I try to rollback to a savepoint called "mytransaction" wich does not exist. and this (maybe) raises a new exception itselfs...

I found this after: - creating a new database with two tables, one table with a foreign key on the other's table primary key

• generating a new, clean, llblproject from this database

• creating a windows form application which uses the llblproject, causing the described error...

result? everything worked as you described.

so I compared every line of code of the new windows form application with my non-working server code and found the difference.

Is it possible to have an exception like "Transaction savepoint {name} does not exist."?

problem solved, Thanks a lot.

pier wrote:

my fault!

on my first post I wrote:

Catch MyAdapter.Rollback("mytransaction")

but

the argument for rollback is savepoint name not transaction name, here I try to rollback to a savepoint called "mytransaction" wich does not exist. and this (maybe) raises a new exception itselfs...

I missed that completely, my fault as well. Glad it's solved

I found this after: - creating a new database with two tables, one table with a foreign key on the other's table primary key

• generating a new, clean, llblproject from this database
• creating a windows form application which uses the llblproject, causing the described error...

result? everything worked as you described.

so I compared every line of code of the new windows form application with my non-working server code and found the difference.

Is it possible to have an exception like "Transaction savepoint {name} does not exist."? problem solved, Thanks a lot.

It doesn't store the transaction save point names at the moment. I'll see if I can add it to the upgrade.

Checking for savepoint names is added to the code for the upgrade currently in development.

nice!