Sorry for opening the prior thread. Here is a new thread for the message. Please close the thread I opened earlier.

After reading the solution to this issue, as suggested in the following thread, http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14111, I am still unable to get the NOLOCK optimizer hint applied at the dataaccessadapter level.

I am using LL2.6.

I have a need to set a NOLOCK on a specific connection as well, not globally, due to some legacy code issues. Can anyone post a sample of their implementation of solution that Frans suggested in this thread?

I can't seem to get it to work.

For the subclassed dynamic query engine, I have created a new class in the database specific adapter project:

class CustomNoLockDynamicQueryEngine : SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine
    {
        public CustomNoLockDynamicQueryEngine(): base() 
        {
            ((SqlServerSpecificCreator)Creator).UseNoLockHintsForObjectNames = true;
        }
    }

I have then also created a separate NoLock DataAccessAdapter class for testing:

    public class DataAccessAdapterNoLock : OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapter
    {
        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        public DataAccessAdapterNoLock(string connectionString)
            : base(connectionString)
        {
        }

        protected override SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase CreateDynamicQueryEngine()
        {
            //return base.CreateDynamicQueryEngine();
            return new OneShop.Mc.DAL.DatabaseConnector.CustomNoLockDynamicQueryEngine();
        }
    }

My consuming code is then creating an instance of the new data access adapter class:

OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapterNoLock  dataAccessAdapter = new DataAccessAdapterNoLock(connectionString);

ScheduleEntity scheduleEntity = new ScheduleEntity(18646);
dataAccessAdapter.FetchEntity(scheduleEntity);

When I do this, data is retrieved, but when I put tracing in the app to check the sql that is emitted, it does not contain NOLOCk?

Help, anyone?

Thanks.

Can1

In response to the Mtrinder question on the previous thread:

_# Posted on: 27-Aug-2009 22:23:53. Please open new threads rather than hijacking old ones.

In reference to your question - if you step through the code, does your custom NoLock DQE actually get returned ?

Matt_

My calling code creates my subclassed DataAccessAdapterNoLock code, then initiates a fetch statement. At the time the fetch statement executes, a break point stops on the following line:

            
return  new OneShop.Mc.DAL.DatabaseConnector.CustomNoLockDynamicQueryEngine();

I can then step into the constructor of the CustomNoLockDynamicQueryEngine class I created and I see the UseNoLockHintsForObjectNames field being set:

        
public CustomNoLockDynamicQueryEngine(): base() 
        {
            //DynamicQueryEngine.UseNoLockHintOnSelects = true;
            ((SqlServerSpecificCreator)Creator).UseNoLockHintsForObjectNames = true;

            
        }

Once I step out past the

            
return  new OneShop.Mc.DAL.DatabaseConnector.CustomNoLockDynamicQueryEngine();

statement, I step into the

        
protected override IDbConnection CreateNewPhysicalConnection(string connectionString)
        {
#if CF
            return new SqlCeConnection(connectionString);
#else
            DbConnection toReturn = SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.FactoryToUse.CreateConnection();
            toReturn.ConnectionString = connectionString;
            return toReturn;
#endif
        }

I know the return statement executed above, returning my CustomNoLockDynamicQueryEngine class.

Can1

Weird,

Let me test what you are doing. Will post my results today later...

the generated SQL shows no NOLOCK being emitted?

Yes, that is correct Frans.

Stepping through the code, I see my custom DQE being returned:

return  new OneShop.Mc.DAL.DatabaseConnector.CustomNoLockDynamicQueryEngine();

Then, when I look at the trace, I see:

Executed Sql Query: 
    Query: SELECT [mc].[dbo].[ScheduleTest].[ScheduleID] AS [ScheduleId]
 FROM [mc].[dbo].[ScheduleTest]  WHERE ( ( [mc].[dbo].[ScheduleTest].[ScheduleID] = @ScheduleId1))
    Parameter: @ScheduleId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 555.

I am not sure what I am doing incorrectly in my subclassed classes?

Can1

What if you use this:

UseNoLockHintOnSelects = true;

instead of

((SqlServerSpecificCreator)Creator).UseNoLockHintsForObjectNames = true;

Looking into DQE code, seems the UseNoLockHintOnSelects flag is used to set the Creator.UseNoLockHintsForObjectNames flag.

I had tried that but that seems to set it globally. If I use this testing code with the code modification you suggested:

using (OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapterNoLock dataAccessAdapter = new DataAccessAdapterNoLock(sqlConnectionString))
              {
                  ScheduleEntity scheduleEntity = new ScheduleEntity(18646);
                  dataAccessAdapter.FetchEntity(scheduleEntity);
              }

using (OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapter dataAccessAdapter2 = new DataAccessAdapter(sqlConnectionString))
              {
                  ScheduleEntity scheduleEntity2 = new ScheduleEntity(18646);
                  dataAccessAdapter2.FetchEntity(scheduleEntity2);
              }

Using this test code, when the first call using _DataAccessAdapterNoLock _ happens, it adds NOLOCK to the SQL.

When the second call using the standard _DataAccessAdapter _ happens, it also adds NOLOCK to the SQL.

When I reverse the order, the first result does NOT contain NOLOCK, but the second does. So it seams that setting that property is setting it globally, not per dataaccessadapter.

Can1

oh that's correct, it's a static property. This is because there's currently no 'per query' hint system, so it's all or nothing. One should always avoid NOLOCK, but if it's inevitable, it's apparently not that required to wait for locks, so it's set at a global level. You want it switch off for some queries? Why use it at other queries then if I may ask?

You want it at a query level? Or call level? Because if you want it at a call level and 2 queries are created (e..g. prefetch path), which one do you want to apply it to?

That's why a per-query level lock system is preferred, but it's not implemented, and as per call level is also multi-query, we left it as a global setting.

Frans,

I agree that one should always avoid NOLOCK. No argument there at all I completely agree with the theory of not using them, but I have a special circumstance where I need to apply NOLOCK to a handful of cases.

There are several different areas to the system that I am introducing LLBLGen into at this time. The one particular area that we are putting it into is not well designed and I am using LLBLGen for some UI re-working. I have to put some NOLOCKS in place in this area to resolve some locking issues with the legacy code under extremely high load. Re-engineering it is just not an option for now.

Going forward, most of the other areas are written well, and as a rule, I don't want to issue NOLOCK at all times. Basically, I have a handful of queries that are touching nasty high load tables and I need to put a NOLOCK on these handful of cases.

I need to differentiate these 2 cases more at the DataAccessAdapter level, than at the query level.

Maybe I am approaching my explanation here incorrectly. My concern is that having a business class loading in App Domain A, setting NOLOCK on, then having a different business class loading in App Domain A, and it getting NOLOCK set on when it is for a very different part of the application. Really, I only want to set NOLOCK on for this 1 area of the application, and do so explicitly, and for all the other areas of the application that are written better, I don't want to. My thought is that it is easiest to do so at the DataAccessAdapter level, and given the previous thread indicating how to do so by subclassing the DataAccessAdapter and DQE, that would work for me in this case.

The other thread seemed to indicate that he got it working, but I just can't see to get the same methodology working in my test code.

Can1

I think you set the flag too soo in your test code. Your code doesn't do all the steps I described, it merged some of them into one. The DQE at some point sets the flag in the creator with the value of the global setting. Your call-specific value should be set after that point, not before. This is likely happening now: you set the value which is overwritten with the global setting after that. Please implement all steps I described. If it then still doesn't work, please post the complete classes you wrote so we can test them out locally

Here are the classes that I originally wrote, and tested again, based on your suggestion in thread http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=14111. NOLOCK is not being emitted:

Custom DQE class with property and setter to set the UseNoLockHintsForObjectNames property:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.LLBLGen.Pro.DQE.SqlServer;

namespace OneShop.Mc.DAL.DatabaseConnector
{
    class CustomNoLockDynamicQueryEngine : SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine
    {
        private bool _applyNoLockToSelectQueries;

        public bool ApplyNoLockToSelectQueries
        {
            get { return _applyNoLockToSelectQueries; }
            set { _applyNoLockToSelectQueries = value;
            ((SqlServerSpecificCreator)Creator).UseNoLockHintsForObjectNames = _applyNoLockToSelectQueries;
            }
        }

        public CustomNoLockDynamicQueryEngine() : base() { }

}
}

Custom DataAccessAdapter class with overridden CreateDynamicQueryEngine() method:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OneShop.Mc.DAL.DatabaseConnector
{
    public class DataAccessAdapterNoLock : OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapter
    {
        private bool _applyNoLockToSelectQueries = true;

        /// <summary>
        /// CTor
        /// </summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        public DataAccessAdapterNoLock(string connectionString)
            : base(connectionString)
        {
    
        }

        protected override SD.LLBLGen.Pro.ORMSupportClasses.DynamicQueryEngineBase CreateDynamicQueryEngine()
        {
            OneShop.Mc.DAL.DatabaseConnector.CustomNoLockDynamicQueryEngine newDQE = new OneShop.Mc.DAL.DatabaseConnector.CustomNoLockDynamicQueryEngine();
            newDQE.ApplyNoLockToSelectQueries = _applyNoLockToSelectQueries;
            return newDQE;
            
        }
    }
}

Consuming test code, written in C# console application:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OneShop.Mc.DAL.DatabaseConnector;
using OneShop.Mc.DAL.EntityClasses;
using OneShop.Mc.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.LinqSupportClasses;
using OneShop.Mc.DAL.Linq;
using System.Configuration;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
          {
              string _connectionString = ConfigurationManager.AppSettings["ConnectionString"];

              using (OneShop.Mc.DAL.DatabaseConnector.DataAccessAdapterNoLock dataAccessAdapter = new DataAccessAdapterNoLock(_connectionString))
              {
                  ScheduleEntity scheduleEntity = new ScheduleEntity(18646);
                  dataAccessAdapter.FetchEntity(scheduleEntity);
              }

        }
    }
}

Log of output, missing NOLOCK (fields in log output removed except for ScheduleID for brevity):

Method Exit: DataAccessAdapterBase.OpenConnection
Executed Sql Query: 
    Query: SELECT [ar].[dbo].[Schedule].[ScheduleID] AS [ScheduleId]FROM [ar].[dbo].[Schedule]  WHERE ( ( [ar].[dbo].[Schedule].[ScheduleID] = @ScheduleId1))
    Parameter: @ScheduleId1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 18646.

Thanks.

Can1

Please hang on, we are checking this out.

We've found the problem, in CreateSelectDQ, the value of the static flag is set into the creator, overwriting your flag. I also now see that the advice given in the thread you linked to isn't really working, due to the line above, which is there since 2005.

I though that instead, a subclass of SqlServerSpecificCreator would work, created in an override of CreateDbSpecificCreator, (your subclass would be really simple, just return true for the nolock flag), but the flag property isn't virtual

but! there's another, more clean way.

As you've suffered enough, I'll write the code for you, so you can proceed with that code. You can also use this code to emit other hints if you want to. Stay tuned.

Here's the code:

public class NoLockAdapter : DataAccessAdapter
{
    public NoLockAdapter()
    {
    }

    public NoLockAdapter(IComPlusAdapterContext comPlusContextHost)
        : base(comPlusContextHost)
    {
    }

    public NoLockAdapter(IComPlusAdapterContext comPlusContextHost, string connectionString)
        : base(comPlusContextHost, connectionString)
    {
    }

    public NoLockAdapter(bool keepConnectionOpen)
        : base(keepConnectionOpen)
    {
    }

    public NoLockAdapter(string connectionString)
        : base(connectionString)
    {
    }

    public NoLockAdapter(string connectionString, bool keepConnectionOpen)
        : base(connectionString, keepConnectionOpen)
    {
    }

    public NoLockAdapter(string connectionString, bool keepConnectionOpen, CatalogNameUsage catalogNameUsageSetting, string catalogNameToUse)
        : base(connectionString, keepConnectionOpen, catalogNameUsageSetting, catalogNameToUse)
    {
    }

    public NoLockAdapter(string connectionString, bool keepConnectionOpen, CatalogNameOverwriteHashtable catalogNameOverwrites, 
                         SchemaNameOverwriteHashtable schemaNameOverwrites)
        : base(connectionString, keepConnectionOpen, catalogNameOverwrites, schemaNameOverwrites)
    {
    }

    protected override DynamicQueryEngineBase CreateDynamicQueryEngine()
    {
        return new NoLockSqlServerDQE();
    }
}


public class NoLockSqlServerDQE : DynamicQueryEngine
{
    public NoLockSqlServerDQE()
    {
    }

    protected override IDbSpecificCreator CreateDbSpecificCreator()
    {
        return new NoLockSqlServerCreator();
    }
}


public class NoLockSqlServerCreator : SqlServerSpecificCreator
{
    public NoLockSqlServerCreator()
    {
    }

    public override string CreateHintStatement(RdbmsHint hint, string targetName, params object[] values)
    {
        string toReturn = string.Empty;
        switch(hint)
        {
            case RdbmsHint.TableInFromClauseHint:
                // enhancement tip: you can check for 'targetname' and values to limit emition of nolock even further. 
                toReturn = "(nolock)";
                break;
        }
        return toReturn;
    }
}

Simply create an instance of the NoLockAdapter and you're set. No need for setting flags or anything. Doesn't work on CE Desktop, as CE Desktop uses a different creator class (derived from SqlServerSpecificCreator, so if you want to support nolocks on CE Desktop as well, you've to create two derived classes, and switch between the two depending on IsCe().

I've also updated the thread you linked to.

Nine minutes from promise to code. Frans, you the man.

That worked great Frans. Thanks very much. This is the 3rd or 4th year that I have been using LLBLGen and every time I come across a gotcha, you come up with a solution for me. Your support is beyond stellar, and combined with the feature set you have built into LLBLGen, is the real reason I recommend your product over any other option.

Thank-you.

Can1

can1 wrote:

That worked great Frans. Thanks very much. This is the 3rd or 4th year that I have been using LLBLGen and every time I come across a gotcha, you come up with a solution for me. Your support is beyond stellar, and combined with the feature set you have built into LLBLGen, is the real reason I recommend your product over any other option.

Thank-you.

Can1

No problem! Support is part of a product's quality

@Scott: I cheated a little (I already knew what to code when I posted the 'I'll write something' post

For future reference, I believe

    protected override DynamicQueryEngineBase CreateDynamicQueryEngine()
    {
        return new NoLockSqlServerDQE();
    }

should be

protected override DynamicQueryEngineBase CreateDynamicQueryEngine()
        {
            DynamicQueryEngine dqe = new NoLockSqlServerDQE();
            dqe.PerCallCatalogNameOverwrites = CatalogNameOverwrites;
            dqe.PerCallSchemaNameOverwrites = SchemaNameOverwrites;
            return dqe;
        }

Otherwise the catalogname overrides are not applied

Thanks for the feedback. Could you clarify which runtimeLibrary version does this apply to? i.e. Which one are you using?

Could you clarify which runtimeLibrary version does this apply to? i.e. Which one are you using?

I think it's version 2.6. Since I only support the already generated code (or mostly the extensions thereof), I'm not entirely sure offhand.

Hi,

Today we've upgraded from 4.1 to 5.1 and suddenly it seems that the NoLockDataAdapter custom class is not good anymore because:

1) warning CS0618: 'RdbmsHint' is obsolete: 'Starting with v5.1 this enumeration has been marked obsolete. Please use the FromClauseDirective system instead.'

2) warning CS0672: Member 'NoLockSqlServerCreator.CreateHintStatement(RdbmsHint, string, params object[])' overrides obsolete member 'DbSpecificCreatorBase.CreateHintStatement(RdbmsHint, string, params object[])'. Add the Obsolete attribute to 'NoLockSqlServerCreator.CreateHintStatement(RdbmsHint, string, params object[])'.

Can you please help me a bit?

Thank you, Evdin

They're replaced with a hint system:

linq: http://www.llblgen.com/Documentation/5.1/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Linq/gencode_linq_generalusage.htm#target--index-hints-sql-server--mysql

queryspec: http://www.llblgen.com/Documentation/5.1/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_generalusage.htm#specifying-a-target-hint--index-hint-sql-server--mysql

The class you refer to was always a hack, it's now implemented properly. If you want to apply NoLock on a global scale, you can always use the Nolock directive on the Sqlserver DQE.

http://www.llblgen.com/Documentation/5.1/ReferenceManuals/LLBLGenProRTF/html/9363447C.htm