Support for a single model connecting to multiple DB types

crazypit · Post by **crazypit** » Thu 28 Apr 2011 13:43

Hello,

I tried the latest beta of Entity Developer and i cannot seem to find a proper way to maintain a single model for multiple DB types (specifically an Oracle 11 and a MS SQL Server 2008). From what i've seen so far, it seems that you are very close to accomplish this, but for some reason, fail to do so. I will offer my thoughts on this and hope that you fix it during the beta.

What you support now is a model which can be synchronized to a DB through a SINGLE connection. Specific DB info (namely the Server Data Type) is maintained in the model and synchronized through this single connection. If someone wants to change the DB, she first needs to change the connection type, then re-synchronize so that the model gets the new server data types (not always the case. some bugs are present there...) and generate the code. In any case, it is impossible to keep the SAME model! The best one can do is to save the model with an other name and thus maintain 2 models for the different connections.

It is apparent, that a simple change in the architecture, can solve the problem. A single model is should be maintained with NO server info. User should be able to define one or more DB connections for this model. When the user needs to synchronize with a database (Update model from database, update database from model), he selects the connection he prefers and performs the synch. Apparently, the same DB-->model translation should be performed even if the DB type is different. The server data types are NOT required to be maintained in the model. When the user needs to generate Entities, again he selects the connection type and then the correct server data types are generated either in the entity attributes or in the xml mapping file. This is the only place where the server data types are required.

Challenger · Post by **Challenger** » Fri 06 May 2011 10:06

Please specify which ORM you use and give a more detailed description of your development scenario. As far as I understood, you are developing one database, then update your model from this database, and then update the second database from the model.

Perhaps the more correct way for us is storing several values of the ServerDataType property. One for each connection.

crazypit · Post by **crazypit** » Mon 09 May 2011 07:26

I use LinqConnect since its beginning and i want to play with Oracle 11g & MS SQL Server 2008.

I have an application which should be working with 2 different DB types, namely an Oracle 11g and an MS SQL Server 2008. I need to use the XML file mapping feature. Therefore, using a proper switch which denotes the DB type, my POCO LINQ entities should be mapped either to the one or the other DB type. This can be accomplished by loading the proper XML mapping file in the DataContext class.

Now, to my configuration, i only need to maintain ONE Entity model with all the .NET properties. Class names, Property names, .NET types etc. are (and should be) common for both databases. Now, for each of my two DB connections, a separate model (XML file?) should be maintained, along with the DB specific info as Column name and DB Type. What i effectively propose is that a 2-level configuration should be maintained. One for the Object part of the ORM mapping and one for the Relational part of the ORM mapping FOR EACH DB TYPE. When i need to synchronize with a database, i should select the connection upon which it should take place. The same goes when i need to generate code: The difference between the 2 DB types will only be the XML mapping file! The DataContext class differentiates only at the static mappingSource variable which can be easily custom configured to use the proper mapping file, and the parameterless constructor which is pretty much useless.

It seems to me that you have all the necessary components to properly accomplish a complete DB-agnostic entity model generation, you just need to re-structure your architecture a little bit.

Challenger · Post by **Challenger** » Wed 11 May 2011 14:01

Thank you for the reply. We will study this problem just after release.

For now, indeed, you should maintain two models and use the POCO template with XML mapping generation and create DataContext as you described in your post.

Can we expect your help in testing such functionality before the Beta version?

esben · Post by **esben** » Mon 11 Jul 2011 10:57

We are currently evaluating different solution for ORM and Conceptual modelling, among these the Entity Developer. A "multiple database" model such as suggested by crazypit would be very welcome. Almost none of the solutions tested so far provides this sort of thing (LLBLGen being a notable exception).

If DevArt developed such a feature we would be very willing to help test it and supply feedback.

StanislavK · Post by **StanislavK** » Tue 12 Jul 2011 12:12

We are working on this functionality, but cannot provide a timeframe for it at the moment. We will post here when any new information is available.

esben · Post by **esben** » Tue 12 Jul 2011 13:00

Awesome. As noted we will be willing to help test such a feature.