Hi,

As we understand it, for some reason, the Catalog Name overwriting functionality can only be fully utilized in the Adapter scenario. This is unfortunate.

In our case, we have several databases that we need to target. Our primary model is named like so: [Database]_MDL, but the other systems are named for the locales which they target. Thus, we have something like this:

(no, "Database" is not the actual name)

Database_MDL Database_UK Database_CA

..and so on.

We have a heavy investment here in the Self-Servicing model. It is too late to change to adapter on these projects, and we desperately need the capability to overwrite catalog name on the fly. We have a "half-solution" in doing this:

  <sqlServerCatalogNameOverwrites>
    <add key="Database_MDL" value="" />
  </sqlServerCatalogNameOverwrites>

This (apparently) causes the system to use no catalog name at all, so rather than have Database_MDL.dbo.TableName, the DQEs generate "dbo.TableName". This, while functional, eliminates query re-use because of the lack of fully qualified object names. We also have no certainty that this method will work in all situations with Self-Servicing. Can you comment on that?

As a more complete solution, we decided to take matters into our own hands, feeling somewhat left out in the cold as users of the Self-Servicing model.

Thus, we created the following class and placed it into the HelperClasses namespace:

  ''' <summary>
  ''' Replacement DQE for custom SQL Server Usage
  ''' </summary>
    Public Class InternalDQE
      Inherits SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine

      Private _functionMappings As SD.LLBLGen.Pro.ORMSupportClasses.FunctionMappingStore

      Public Sub New()

          MyBase.New()
          Me.PerCallCatalogNameOverwrites = New CatalogNameOverwriteHashtable()
          Me.PerCallCatalogNameOverwrites.Add(DbUtils.OriginalCatalogName, DbUtils.NewCatalogName)

      End Sub

      Public Sub New(ByVal dqeToUse As DynamicQueryEngineBase)

          MyBase.New()

          'Leave what was passed
          With dqeToUse
              Me.Creator = .Creator
              _functionMappings = .FunctionMappings
              Me.PerCallSchemaNameOverwrites = .PerCallSchemaNameOverwrites
              Me.UniqueMarker = .UniqueMarker
          End With

          'Custom overwrites
          Me.PerCallCatalogNameOverwrites = New CatalogNameOverwriteHashtable()
          Me.PerCallCatalogNameOverwrites.Add(DbUtils.OriginalCatalogName, DbUtils.NewCatalogName)

      End Sub

      Public Overrides ReadOnly Property FunctionMappings() As SD.LLBLGen.Pro.ORMSupportClasses.FunctionMappingStore
          Get
              Return _functionMappings
          End Get
      End Property

  End Class

The intent of this is to replace the use of "New DynamicQueryEngine()" and "dqeToUse" in the two constructors of each DAO in our project. We are using SQL Server, by the way.

Thus, we take the constructors in the DAOs and do this:

        ''' <summary>CTor</summary>
        Public Sub New()
            'MyBase.New(InheritanceInfoProviderSingleton.GetInstance(), New DynamicQueryEngine(), InheritanceHierarchyType.None, "BucketEntity", New BucketEntityFactory())
            MyBase.New(InheritanceInfoProviderSingleton.GetInstance(), New InternalDQE(), InheritanceHierarchyType.None, "BucketEntity", New BucketEntityFactory())

        End Sub

        ''' <summary>CTor</summary>
        ''' <param name="inheritanceInfoProviderToUse">Inheritance info provider to use.</param>
        ''' <param name="dqeToUse">Dqe to use.</param>
        ''' <param name="typeOfInheritance">Type of inheritance.</param>
        ''' <param name="entityName">Name of the entity.</param>
        ''' <param name="entityFactory">Entity factory.</param>
        Friend Sub New(ByVal inheritanceInfoProviderToUse As IInheritanceInfoProvider, ByVal dqeToUse As DynamicQueryEngineBase, ByVal typeOfInheritance As InheritanceHierarchyType, ByVal entityName As String, ByVal entityFactory As IEntityFactory)
            'MyBase.New(inheritanceInfoProviderToUse, dqeToUse, typeOfInheritance, entityName, entityFactory)
            MyBase.New(inheritanceInfoProviderToUse, New InternalDQE(dqeToUse), typeOfInheritance, entityName, entityFactory)
        End Sub

We also added two new Shared Properties to DBUtils, namely OriginalCatalogName and NewCatalogName.

The usage pattern then becomes (to target the UK database, for example

DBUtils.ActualConnectionString = desiredConnectionString
DBUtils.OriginalCatalogName = "Database_MDL"
DBUtils.NewCatalogName = "Database_UK"

In our testing thus far, this works. Our concern lies with the second DAO constructor, the one that takes the parameter "dqeToUse". Will our tactic work in that situation? As you can see in our overloaded constructor in InternalDQE, we attempted to use as many of its properties as possible. Our testing shows that it works both for retrieval and persistence, and it now generates the fully qualified object names that we desire.

Does this solution look viable?

Thanks, cardplayer

We have a heavy investment here in the Self-Servicing model. It is too late to change to adapter on these projects, and we desperately need the capability to overwrite catalog name on the fly. We have a "half-solution" in doing this:

Code: <sqlServerCatalogNameOverwrites> <add key="Database_MDL" value="" /> </sqlServerCatalogNameOverwrites>

In that case, the DQE will not specify a catalog name in the generated SQL elements, which will make the SQL target the catalog specified in the connection string. Which can be changed at runtime using:

DbUtils.ActualConnectionString = "Datasource=myserver;....";

If you want to make the application use the connection string defined in the config file again, simply set the ActualConnectionString property to string.Empty.

A similar thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=10053

Walaa,

Thank you. We are aware of that, as I mentioned in my original post. This is most undesirable, however, because query plan caching will not occur. This is because query re-use for these types of parameterized queries requires fully-qualified object names (ask Frans).

We want to know if our approach is valid, because we are aware of the alternatives, and we do not like them

Is there a company-line explanation as to why the functionality is nearly unavailable in Self-Servicing? It seem that Self-Servicing users are starting to get the leper treatment.

Here is the snippet from the original post, which shows that we are very clearly aware of this option.

cardplayer wrote:

We have a heavy investment here in the Self-Servicing model. It is too late to change to adapter on these projects, and we desperately need the capability to overwrite catalog name on the fly. We have a "half-solution" in doing this:

  <sqlServerCatalogNameOverwrites>
    <add key="Database_MDL" value="" />
  </sqlServerCatalogNameOverwrites>

This (apparently) causes the system to use no catalog name at all, so rather than have Database_MDL.dbo.TableName, the DQEs generate "dbo.TableName". This, while functional, eliminates query re-use because of the lack of fully qualified object names. We also have no certainty that this method will work in all situations with Self-Servicing. Can you comment on that?

As a more complete solution, we decided to take matters into our own hands, feeling somewhat left out in the cold as users of the Self-Servicing model.

Thanks, cardplayer

Your solution seems fine to me.

Also here is another roposed solution: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=15107&StartAtMessage=0&#84238