I am using LLBLGen 4.0 with LLBLGen Pro Runtime Framework, Adapter. I want to accomplish a simple task: change the isolation level on the connection.

I know that this can be done by starting and immediately committing a dummy transaction; but this ugly. Is there another way to accomplish this?

Isolation level for transactions, please check the following thread. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21632 This one might be useful too. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21404

Start a transaction specifying the Isolation level, use the transaction and then commit it.

Connections don't have isolation levels, transactions do. I'm not sure why you would want this (as in: what you are trying to accomplish?)

Sorry for the late answer.

@Walaa: thanks, that's what I am already doing, but it is quite ugly.

@Otis: It is my understanding that (at least in SQL Server) each query issued on a connection with no running transaction will be run inside an implicit transaction. The isolation level used for these transactions can be changed using "set transaction isolation level xxx." This value is saved for each connection, which is what I meant in my earlier post. (If you open the profiler and list the existing connections, its shows a bunch of session-related SET statements, of which the isolation level is one.)

So what I would like to do is issue a "set transaction isolation level xxx" statement at runtime, without using T-SQL in an action query.

I want to do this to change the isolation level temporarily to READ UNCOMMITTED to allow dirty reads on a bunch of queries. I want to revert this change when I am done, because that's what the caller of my code expects me to do.

To tell LLBLGen to use some IsolationLevel when it needs to use a transaction implicitly (i.e. updates) you can do this way:

using (var adater = new DataAccessAdapter())
{               
    adater.TransactionIsolationLevel = IsolationLevel.ReadUncommitted;
    
    // ...
}

I know; but I cannot open a new DataAccessAdapter. Setting the TransactionIsolationLevel property after the adapter is opened has no effect.

But creating an adapter doesn't open a connection yet. If you set it before the first action is done/connection is opened, it will take effect.

I recon the connection is already open?

Yes, the adapter is already opened when I get it

acl wrote:

Yes, the adapter is already opened when I get it

Adapter or its connection, these are two different things: creating a dataaccessadapter instance doesn't open a connection, hence my question: there's no adapter 'open'.

I meant the connection, sorry

Then it's indeed only possible to do what you want by starting a short transaction. You could create a default ActionQuery for this, but it's not supported by the framework.

ok, thanks!