Hi,

I'm trying to join two tables from two different catalogs but I'm not able to get to work.

I know I have to do something with CatalogNameOVerwriteHashtable but apparently I'm doing something.

The documentation link (http://www.llblgen.com/documentation/2.6/using%20the%20generated%20code/Adapter/gencode_dataaccessadapter_adapter.htm) doesn't give enough information to resolve my challenge.

I have the following situation:

• I've got two Catalogs: CatalogA and CatalogB
• There's Article-table in CatalogA and a StockCount-table in CatalogB

I've created a manual relation. So far so good.

My guess is that I have the following actions: - Create a new CatalogNameOverwriteHashtable instance: var foo = new CatalogNameOverwriteHashtable(); - foo.Add("StockCount", "CatalogA"); - foo.Add("Article", "CatalogB"); - Assign it to the adapter: adapter.CatalogNameOverwrites = foo;

Which results in the following query:

SELECT [dbo].[StockCount].[ArticleId], [dbo].[Article].[Description], [dbo].[StockCount].[ShopId], [dbo].[StockCount].[LastMutationDateTime] FROM ( [dbo].[StockCount] INNER JOIN [dbo].[Article] ON [dbo].[StockCount].[ArticleId]=[dbo].[Article].[ArticleId])

Clearly I'm doing something wrong because the catalog name is missing in the query. The question is, what?

Are both catalog names different than in the Development Environment? That's the only reason to use Catalog Names Overwrites.

Which version of LLBLGen are you using?

Walaa wrote:

Are both catalog names different than in the Development Environment? That's the only reason to use Catalog Names Overwrites.

Which version of LLBLGen are you using?

Are both catalog names different than in the Development Environment? Huh? Sorry, I don't get that. The two catalogs are two different database. On holds the article table and the other holds the StockCount table.

I'm using a very old version of LLBLGen (2.0). We are in the process of switching to 4.0. Is it easier in 4.0 .

The catalog overwrites works this way: suppose you have two original catalogs 'HumanResources' and 'Sales'. This works perfectly, but maybe at production environment they have different names like 'NewHumanResources' and 'NewSales'. Then you can make that change in your config:

<configSections>
    <section name="sqlServerCatalogNameOverwrites" type="System.Configuration.NameValueSectionHandler" />
</configSections>

<sqlServerCatalogNameOverwrites>
    <add key="HumanResources" value="NewHumanResources" />
    <add key="Sales" value="NewSales" />
</sqlServerCatalogNameOverwrites>

... or at runtime:

var overwrites = new CatalogNameOverwriteHashtable();
overwrites.Add("HumanResources", "NewHumanResources");
overwrites.Add("Sales", "NewSales");

using (var adapter = new DataAccessAdapter(..., overwrites, ...))
{
    ...
}

For this purpose, using the config file is enough, unless you don't know your new catalog name in advance, but at runtime, like when you change the database depending on the logged user.

You cannot change the database per table basis, like: now this table is in this catalog and this other is in this one. The tables you initially mapped stay in the original catalogs, all you can do is change the catalog name.

Hope this is clear.

daelmo wrote:

Hope this is clear.

Completely clear. Thanks for that.

In that case my question remains. Is it possible to execute a query involving two catalogs?

SELECT [CatalogA].[dbo].[StockCount].[ArticleId], [CatalogB].[dbo].[Article].[Description], [CatalogA].[dbo].[StockCount].[ShopId], [CatalogA].[dbo].[StockCount].[LastMutationDateTime] FROM ( [CatalogA].[dbo].[StockCount] INNER JOIN [CatalogB].[dbo].[Article] ON [CatalogA].[dbo].[StockCount].[ArticleId]=[CatalogB].[dbo].[Article].[ArticleId])

How does LLBLGen it's catalog name? Is purely done with the connection string?

CatalogNames are stored in the mapped entities (selfService) or in other classes (FieldInfoProviders) in the DBSpecificproject (Adapter).

So in short LLBLGen knows which catalog to use with which entity, and this has nothing to do with the catalog specified in the connection string.

Have you tried this and it failed somehow? If so could you please provide more info on the failure and the LLBLGen runtime library version (build no.) used.

Walaa wrote:

CatalogNames are stored in the mapped entities (selfService) or in other classes (FieldInfoProviders) in the DBSpecificproject (Adapter).

So in short LLBLGen knows which catalog to use with which entity, and this has nothing to do with the catalog specified in the connection string.

¨

Thank you for guiding me to the solution. The 'CatalogNameOverwrites'-class solves my problem. I've never had a deep look at it on how this works. Now I know. ¨

One final question. You say the CatalogNames are stored in other classes (FieldInfoProviders) in the DBSpecificproject (Adapter). I can't find them . (Using version 2.0, Adapter model)

CowHills wrote:

Thank you for guiding me to the solution. The 'CatalogNameOverwrites'-class solves my problem. I've never had a deep look at it on how this works. Now I know.

Good to know you solved it

CowHills wrote:

One final question. You say the CatalogNames are stored in other classes (FieldInfoProviders) in the DBSpecificproject (Adapter). I can't find them . (Using version 2.0, Adapter model)

At your generated code, go to your DBSpecific project and find the PersistenceInfoProvider.cs file.

daelmo wrote:

At your generated code, go to your DBSpecific project and find the PersistenceInfoProvider.cs file.

Found it

var catalogName = ORMapper.Utils.DatabasePersistenceInfo.GetFieldPersistenceInfo(ArticleFields.PKey).SourceCatalogName;

Thanks. This thread can be closed.

my essence: llblgen can't work with more than one db / catalog in parallel. maybe that this kind of use case is better done by the sql server...

dm.Frank wrote:

my essence: llblgen can't work with more than one db / catalog in parallel. maybe that this kind of use case is better done by the sql server...

Hi Frank. About this, there is a whole section in the documentation explaining the multi-catalog, multi-schema, multi-tenancy support. Also there are a lot of threads around the forum with actual examples of this.

-- Please don't reopen old threads, it's preferable to open a new one with a fresh question: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722