Hi.

I have a following code (pseudo code)

adapter.StartTransaction(IsolationLevel.Serializable, "GetQueuedTask");

FetchEntityCollection(adapter, collection, filter, null, null, null, 1);

Just before Entity Collection Fetch I need to lock table (i think it's not possible using directly llblgen) by passing the following sql query before fetch:

LOCK TABLE queue.background_tasks_queue IN ROW EXCLUSIVE MODE;

This query should be executed in the transaction. So it'll be :

a) create a transaction b) lock table c) fetch entity collection d) etc.

How can I insert the mentioned query into a transaction ?

Best Regards, MiloszeS

I've used the following code. But unfortuanteley, when I'm using the orm profiler I get an exception at ExecuteActionQuery:

adapter.StartTransaction(IsolationLevel.Serializable, "GetQueuedTask");

                                                adapter.ExecuteActionQuery(new ActionQuery(new Npgsql.NpgsqlCommand("LOCK TABLE queue.background_tasks_queue IN ROW EXCLUSIVE MODE;")));

                                                FetchEntityCollection(adapter, collection, filter, maxCount,
                                                                      sortExpression, null, 0, 0);

{System.InvalidCastException: Unable to cast object of type 'SD.Tools.OrmProfiler.Interceptor.ProfilerDbConnection' to type 'Npgsql.NpgsqlConnection'. at Npgsql.NpgsqlCommand.set_DbConnection(DbConnection value) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 237 at SD.LLBLGen.Pro.ORMSupportClasses.Query.set_Connection(DbConnection value) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\Query.cs:line 383 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateActiveConnectionInQueryIfRequired(IQuery queryToExecute, Boolean forceConnectionSet) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 5533 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.PrepareQueryExecution(IQuery queryToExecute, Boolean forceConnectionSet) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 5557 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteActionQuery(IActionQuery queryToExecute) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.5\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 526 at AplusC.TvMan.New.CommonBusiness.Facades.CommonQueueDao.<>c__DisplayClass4.<GetQueuedTasks>b__3() in d:\projects\svn\TvManNext\Branches\Production\Current\Common\dotNet\TvMan.CommonBusiness\Facades\CommonQueueDao.cs:line 186 at ...

Whe runned without profiler it works.

This: new Npgsql.NpgsqlCommand is the problem. Grab the factory using DbProviderFactories.GetFactory and create a command from that factory, by calling its CreateCommand method.

if you have a problem with obtaining the factory, please let us know.

var providerFactory = DbProviderFactories.GetFactory("Npgsql");
DbCommand command = null;

if (providerFactory != null)
{
  command = providerFactory.CreateCommand();

  command.CommandText = "...";
}

Is it ok, or can I handle it more "pretty" ?

That's about it indeed. Unfortunately, provider factory oriented programming is a bit ugly, you have to set properties manually to get things done.

You could add code to the Adapter class though (as a partial class), obtain the DQE by calling the protected method CreateDynamicQueryEngine(), and obtain the factory from that dqe instance by reading the property 'FactoryToUse'.

Or you can read the DQE instance' Creator property and call that object's CreateCommand() method.