Support multiple databases

[Eddie Conner]

Does OA have the ability to support multiple databases from within the same generated classes? Basically can one switch between MS SQL, MySQL, etc based on what database the client wants to use? 

[Ady]

Hello Eddie Conner,

 Yes, this is very much possible if you are generating the classes via the Reversemapping wizard.
All you need to do during runtime is specify the right connection string. The app.config could look as follows:

<connections>
      <connection id="DatabaseConnectionMssql">
        <connectionString>SQL Server specific connection string here</connectionString>
        <backendconfigurationname>mssqlConfiguration</backendconfigurationname>
      </connection>
      <connection id="DatabaseConnectionMysql">
        <connectionString>MySQL specific connection string here</connectionString>
        <backendconfigurationname>mysqlConfiguration</backendconfigurationname>
      </connection>
    </connections>
    <backendconfigurations>
      <backendconfiguration id="mysqlConfiguration" backend="mysql">
        <mappingname>commonMapping</mappingname>
      </backendconfiguration>
      <backendconfiguration id="mssqlConfiguration" backend="mssql">
        <mappingname>commonMapping</mappingname>
      </backendconfiguration>
    </backendconfigurations>
<mappings current="commonMapping">
      <mapping id="commonMapping">
          <mapping created by Reversemapping wizard here>
</mapping>

Notice that both databases have their independent connection settings but a common mapping (tables,columns etc)

Do get back to us in case you have further questions on how to achieve this.

All the best,
Ady
the Telerik team

[Eddie Conner]

Are there any examples of switching connections during runtime?

[Ady]

Hi Eddie Conner,

 I have created a sample for you where I have reverse mapped the tables from the Northwind database, in Mssql. I have then used the same generated classes to create the database and tables in MySql.
In case you already have the database created you will need to adjust the mapping specific for MySql.

When the Reverse mapping wizard generates the mapping in the app.config file it adds an entry for each class' table - <extension key="db-do-not-create-table" value="true" />
This indicates that when you build the project, the database is not altered. We do not need to since it is reversemapped. When you try to create the same tables on MySql this entry should be removed.
This can be done via the 'Persistent types' node in the Forward mapping wizard.

Do get back in case you have further questions regarding this.

All the best,
Ady
the Telerik team

[Roland]

Is that possible with Forward Mapping in Q3 2010 ?

[Ady]

Hello Roland,

 It is not possible to reverse map an existing database on say, Sql Server, and then forward map it to another backend, using the new Domain Model wizard.
To achieve that you would need to use the classic Reverse Mapping wizard as show in the earlier example.

Do let me know if you need further assistance.

Greetings,
Ady
the Telerik team

[Roland]

We cannot afford using technology that will be deprecated. Is that possible with Visual Designer Model first approach or FluentMapping source ?

[Zoran]

Hello Roland,

1.  The functionality of switching between database backends is available in the Visual Designer, but I must specify that it is limited at them moment. let me explain you the limitations that we currently have there:
• You can only use forward mapping approach and let OpenAccess create database schema for your model using the default mapping settings. That means that you can define your class  model using the designer, but you are not able to specify any settings that are specific for a  backend: for example you can not specify that the column that will be created for a string  property should be of type NVARCHAR (instead of VARCHAR which is the default) as the  NVARCHAR type is specific only for SQL Server but there is no such type in Oracle.  
2. The same rule applies to the fluent mapping. If you use default mapping there everything will work out-of-the box as soon as you decide to use different backend(a BackendConfiguration object with the correct settings should be provided to the context).  However, implementing the functionality  that is missing in the Visual Designer is a fairly easy task with the Fluent Mapping. Indeed, it requires custom logic by the user, but it should not bother you too much. By that I mean you have to provide your own mechanism for specifying the correct backend, maybe your own XML configuration where you also keep mapping between backend and SQL Type. For example the XML could look something like this:

<Type>
   <Backend Name="MSSQL">NVARCHAR</Backend>
   <Backend Name="Oracle">VARCHAR2</Backend>
   ...
</Type>

Then based on this configuration you can do the mapping to the columns in your Fluent Mapping Code, thus always map to the correct type based on your current Backend.

I really hope that my answer helps you to get started with your project. I can assure you that this topic is with highest priority on our TODO list and we will provide much easier ways to switch between bacjkends and configure domain models for more than one database.

Sincerely yours,
Zoran
the Telerik team

[Stephane Zanoni]

Shouldn't  the fluent mapping source know about all sql data types for every database? Switching them in runtime should be only by providing the backend type :S

Do you mean I have to map them myself one by one in a config file ?

Will / when that functionality be available ?

Edit: By Roland, posted from another account.

[Zoran]

Hi Stephane Zanoni,

 Maybe we had a bit of misunderstanding here. The Fluent Mapping will map you properties to the required column types in each database as long as you leave for it to do the mapping for you using default settings. For example, if you have a string property, it will be mapped to Varchar in SQL Server and the corresponding types for the other backends without you specifying anything explicitly. However if you would like to manipulate this behavior and want to map a string property to something different than Varchar and use some types that are specific only for specific backends, that is when the custom logic comes in place. That is not a requirement for working with the Fluent API though, I must underline again.

Greetings,
Zoran
the Telerik team

[Roland]

What we need is exactly what you are saying.

The purpose is to create some classes to be persisted in the database. We need to be able to change the provider as a one-liner in some config file. We would use default settings, provided string type does not map to varchar(50) every time and in case of string we can control the length.

So you say the fluent mapping source is the way to go ?

Roland (and Stephane)

[Zoran]

Hello Roland,

 Actually if your are fine with the default settings, the Visual Designer can do the job for you as well. I believe it is a choice you can do at the moment to go either way. Both have their pros and cons, it depends on your preferences, writing more code or doing drag & drop in the designer. The Fluent approach can be considered more customizable as you could more easily plug custom logic while defining your mapping but again, it depends on what you are aiming for and the needs of your project.

Kind regards,
Zoran
the Telerik team

[Roland]

I have tried the fluent approach with the docs sample. Enhancer throws exceptions while building foreign keys.

We will try the visual designer, but we feel that ORM  it needs at least year to polish some stuff.

Edit: Tried the visual designer. Unable to select Use Default Mapping for new entity. The settings were Empty Model.

[Zoran]

Hello Roland,

 The enhancer with the Fluent mapping issue has already been fixed as discussed in another thread #372332. Can you tell us more regarding the problems that you got in the designer? We are looking forward to provide you with any assistance required for getting your project under way. 

Regards,
Zoran
the Telerik team

[Roland]

The designer is too slow. Takes around 5 minutes to save diagram with 4 classes. I have no idea what is it doing.

I need to be able to build a meta class with String property of unlimited length. I cannot achieve that. This needs to support mssql, sqlite and oracle, just by changing the backend type.

[Zoran]

Hello Roland,

 I must admit that this issue with the designer saving a rlinq file for 5 minutes really surprises me and is something not expected. Could you send us such diagram so that we could test the behavior on our side.  In case there is a flaw we will make sure it is fixed for the next release. Anyway if you would like to use the designer against multiple databases than indeed you are not able to edit the length of the columns but you could only go with the default mapping. Otherwise you can work with only one backend at a time and probably Fluent Mapping is the better choice at the moment.

Regards,
Zoran
the Telerik team

[Roland]

The diagram has been attached as mydatacontext.zip to ticket # 369330.

The fluent api does not support that either. I need unlimited string (mapped to text or varchar(max) in SQL and proper type in sqlite or oracle).
Someone has already advised how to do that using attributes to decorate poco objects for fluent api.

[Zoran]

Hello Roland,

 I have tried loading your .rilnq file in Visual Studio and the Designer was able to save the file in few seconds the first time(as it was creating the files for the first time). The next times I saved the file it was saved in less than a second as expected. However it is true that you can not achieve what you desired with the designer at the moment and I propose you to go with the Fluent Mapping approach where you could have you own logic setting the types and length of the columns. 

All the best,
Zoran
the Telerik team

[Mhundz]

Can anyone send me sample program using Winforms and using database SofiaCarRental that can connect to MSSQL and MySQL. I'm planning to create a program that has an option to use MySQL or MSSQL DB but with generated classes.


Thanks in advance.

[Doroteya]

Hello Mhundz,

Thank you for contacting us.

The scenario you describe can be implemented with Data Access, but at the time present we do not offer a single resource that matches exactly your requirements. The two parts of the scenario (WinForms + SofiaCarRental + SQL Server and a second backend) are covered by two separate resources. Following are the details:

The requirements about WinForms + SofiaCarRental + SQL Server are met by the Basic Data Binding with Windows Forms sample distributed with Samples Kit. And the steps for meeting the requirement for a second backend option are available in the Online and Offline Data Access with the same Model blog post. The blog demonstrates SQLite but the process is exactly the same for MySQL. 

I hope this helps. Do let us know, if you experience issues or need further details.

Regards,
Doroteya
Telerik