Database agnostic - SQL Server, Oracle, MySQL

[Tim]

I am using a 64 bit dev machine, Win 7 with VS 2010 and creating a Silverlight 4 test application using ORM.  I pointed to an existing legacy SQL Server 2005 database and successfully brought in 3 existing tables and created a few associations as a test.  I then created a domain service and a datagrid and successfully have the data from my database table appearing in the grid.

Now I would like to be able to have the application be able to login to Oracle and MySQL.

Do I need to just change the connection string in the Web.config and change the ProviderName?  If so, to what? 

Do you have any documentation, other forum links or sample that shows how to set everything up so that the application can connect to SQL Server, Oracle and MySQL?

-Tim

[Petko_I]

Hello Tim,

There is a topic in our updated online help which explains the format of the configuration file depending on the backend. Unfortunately, the domain model mapping information contains some backend specific pieces. For example, there are some column-type mismatches among the different database vendors. So, all in all, the automation level you require is not available right now and you can hardly find any ORM tools that are completely independent of the backend.

Currently, you can establish connections to the other database vendors and create empty models with our tool. Then you can copy the mapping information from the XML content of the working .rlinq domain model to the XML of the new .rlinq files. You need to remove anything linked to the relational model. For instance, the table and column tags nested in the class and field tags respectively should be deleted.

<orm:class name="Student" uniqueId="916ca3f1-7ab1-4ada-8771-a3c617d02f38">
    <orm:table name="student" />
    <orm:identity>
        <orm:single-field field-name="studentID" />
    </orm:identity>
    <orm:field name="studentID" property="StudentID" null-value="none" uniqueId="76c94a54-7382-4ffa-96c6-0ff8c59553d1" type="System.Int32">
        <orm:column name="i_d" sql-type="int" nullable="false" scale="0" primary-key="true" ado-type="Int32" />
    </orm:field>
. . .

goes to

<orm:class name="Student" uniqueId="916ca3f1-7ab1-4ada-8771-a3c617d02f38">
    <orm:identity>
        <orm:single-field field-name="studentID" />
    </orm:identity>
    <orm:field name="studentID" property="StudentID" uniqueId="76c94a54-7382-4ffa-96c6-0ff8c59553d1" type="System.Int32" />
. . .

Furthermore, the tags under the orm:schema section should also be deleted. What you have at your disposal after this step is the data needed to forward map your model.

After changing the namespaces (if necessary) and rebuilding the new projects, you should be able to see the entity diagrams populated with the visual representation of your model. Now, you can update the database from the model, execute the generated scripts, update your models from the database by including the tables in the model and finally map the tables and constraints.

You can have a look at the following blog post which performs quite a lot of essential steps when working with OpenAccess. Although the described approach is a valid scenario for forward mapping associations, it is now possible to create the associations without the need to have the entities mapped to tables beforehand.   

Should you need any further assistance regarding the setup of your models or you feel I missed something in the explanations, do not hesitate to contact us.

Best wishes,
Petko_I
the Telerik team

[Bill Togkas]

Hi Petko,

is there any news about this matter. We also have an SQL server created model and we want to be able to change to mysql at runtime (without having another model)

[Zoran]

Hello Tim,

 As long as you have the same database schema under MS SQL Server and MySQL this feature has just been released in the last Service Pack but is only available for customers using the code-only approach via the Fluent API so far. The runtime is able to handle this situation and the UI goods are yet to be developed. We plan to have the feature ready in the designer for the official Q3 2012 release.

Regards,
Zoran
the Telerik team

[Bill Togkas]

Thanks Tim for the info

Looking forward for Q3 2012  (:D)

[Bill Togkas]

Hi guys,

I have installed the new 2012 Q3 ORM and got {"Cannot create converter for SQL type: 'uniqueidentifier' CLR type: 'Guid'.\r\nParameter name: sqltype"}.

tried mysql backend and sql server backend at the same time at the same model.
Is there an example that we can just switch off the connection string and still work on the same generated model?

[Damyan Bogoev]

Hi Bill,

Currently you are able to use your domain model against different backends only when the underlying tables
are not explicitly specified. Having classes mapped to tables, which contains backend specific sql types for the table’s columns, could lead to different errors in your application.

If the persistent types are not explicitly mapped to tables, OpenAccess ORM will do that during runtime – it will calculate the correct table setup for each of the classes and used them.

Hope that helps.

All the best,
Damyan Bogoev
the Telerik team

[Bill Togkas]

Hi Damyan,

Can you give me an example then of how can i create a domain model using the wizard and then convert it to be able to switch at runtime?
I have generated the database from sql server to FluentModel. I could really use Telerik ORM if i can have the same model to multiple different database types and an implementation of Membership provider.

[Damyan Bogoev]

Hello Bill,

You could find the attached sample application useful. It shows how to switch between different backends during runtime.

Note that the domain model is created using model-first approach. 

Hope that helps.

Greetings,
Damyan Bogoev
the Telerik team