I need to pull data from two databases and expose from a BLL or middle tier of some sort. I prefer to expose only LLBLGenPro entities. Logically, the tables in the two databases are related to each other, but of course since they are separate databases there are not actually foreign keys defined.

If both databases are SQL databases maybe this is problematic enough. But while both are in SQL Server 2000, one of them is Microsoft CRM and so I am supposed to use only the CRM API to access this data.

Is it possible for me to create LLBLGenPro entities that wrap tables that are in separate databases precisely as if they were in the same database and the FKs defined between them? For example I might have a Persons table with an AccountID that is a foreign key. But Persons is in one database and AccountBase is a CRM table. If they were in same database then I could reference Person.Account, etc.

This is different, I believe, from the usual "multiple database support" question posted on here in which someone has identical schema in different databases. Here I have different schema but they are related and really should be in same database (but one is CRM so putting other tables in it is considered no-no by Microsoft).

JimFoye wrote:

I need to pull data from two databases and expose from a BLL or middle tier of some sort. I prefer to expose only LLBLGenPro entities. Logically, the tables in the two databases are related to each other, but of course since they are separate databases there are not actually foreign keys defined.

If both databases are SQL databases maybe this is problematic enough. But while both are in SQL Server 2000, one of them is Microsoft CRM and so I am supposed to use only the CRM API to access this data.

Is it possible for me to create LLBLGenPro entities that wrap tables that are in separate databases precisely as if they were in the same database and the FKs defined between them? For example I might have a Persons table with an AccountID that is a foreign key. But Persons is in one database and AccountBase is a CRM table. If they were in same database then I could reference Person.Account, etc.

Yes you can. You can have multiple catalogs in 1.0.2004.2 which offers you a single entity set and possibly multiple catalogs. You can then define custom relations between these entities as if they're in a single database and use them as if they're in a single database

Otis wrote:

Yes you can. You can have multiple catalogs in 1.0.2004.2 which offers you a single entity set and possibly multiple catalogs. You can then define custom relations between these entities as if they're in a single database and use them as if they're in a single database

OK, very good. That is really cool! However...what do I do about CRM since it is a "non-SQL database". What I mean by that is, I am supposed to use the CRM API to fetch the data rather than SQL (of course, this raises the question, what is the point of SQL? Guess that is a question for Bill Gates).

Hmm. That's indeed a good one.. I'm not familiar with MS CRM software so I don't know if they offer an api which converts data back and forth, probably not.

Fetching/saving those entities is then indeed not possible using LLBLGen Pro, you then have to call into that CRM API to get that done, though you can use the entities as objects in your own code, however I'm not sure how much that will give you, considering the work that it might cause.

Yeah. Of course, what I can do is go ahead and query directly against CRM database. I am only reading data, not writing. Then the "risk" is that next version of CRM will change the schema (I don't want to go on and on here about this ugly thing CRM, but...I have a feeling they won't change the schema much anyway - it would be a nightmare to get it properly normalized). Customer would probably go through evaluation period with 2.0 anyway to make sure all their existing customization is not broken.

Thanks for all the help, as always.

If it's just reading, I'd go ahead and setup a project with the two catalogs and see if the data you get out of it is indeed useful. The trouble can be that the API of the software performs post-fetch calculations on the data or the data is stored in such a weird way that you need the API to get data you need...

I've already got a project that uses LLBLGenPro directly on the CRM database. The database schema is funky, but I've been able to figure out what I need. Then someone pointed out we shouldn't really be doing that. So now I will split my tables out to a separate database.

Question: if the data is in two separate databases, and an update is made to an entity that has fields from tables in both databases, what does LLBLGenPro do about an update that succeeds in one but fails in the other? Is it doing some kind of multi-database transaction?

JimFoye wrote:

I've already got a project that uses LLBLGenPro directly on the CRM database. The database schema is funky, but I've been able to figure out what I need. Then someone pointed out we shouldn't really be doing that. So now I will split my tables out to a separate database.

Not doing that as the API will always be doing some post-processing? Makes sense.

Question: if the data is in two separate databases, and an update is made to an entity that has fields from tables in both databases, what does LLBLGenPro do about an update that succeeds in one but fails in the other? Is it doing some kind of multi-database transaction?

You mean, an entity has a related entity which is located in a different catalog? The ado.net transaction run is spanning multiple catalogs. Rolling back the transaction will roll back both actions. It's mandatory the catalogs are located on the same SqlServer server of course.

When you say "catalog" you mean database right? And the two catalogs must be on same SQL Server?

Let me give a specific example (sorry, I just want to make sure I understand this).

CRM has a table ContactBase with PK ContactId. So that's in the CRM database.

My database has a table Persons with a field that maps to ContactBase.ContactId. There would be a FK defined if these two tables existed in the same database, but they don't.

So if both databases are on the same SQL Server, then this works? And if they are in different SQL Servers then it doesn't?

JimFoye wrote:

When you say "catalog" you mean database right? And the two catalogs must be on same SQL Server?

yes.

Let me give a specific example (sorry, I just want to make sure I understand this).

CRM has a table ContactBase with PK ContactId. So that's in the CRM database.

My database has a table Persons with a field that maps to ContactBase.ContactId. There would be a FK defined if these two tables existed in the same database, but they don't.

So if both databases are on the same SQL Server, then this works? And if they are in different SQL Servers then it doesn't?

correct.

If will work with different servers though you then have to use two projects and use the regular multi-database setup with adapter and use transactions with COM+