Database agnostic - SQL Server, Oracle, MySQL

9 posts, 0 answers
  1. Tim
    Tim avatar
    20 posts
    Member since:
    Jan 2009

    Posted 02 Aug 2010 Link to this post

    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
  2. Petko_I
    Admin
    Petko_I avatar
    184 posts

    Posted 05 Aug 2010 Link to this post

    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
    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
  3. DevCraft banner
  4. Bill Togkas
    Bill Togkas avatar
    18 posts
    Member since:
    Mar 2010

    Posted 17 Aug 2012 Link to this post

    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)
  5. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 17 Aug 2012 Link to this post

    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
    Follow @OpenAccessORM Twitter channel to be the first one to get the latest updates on new releases, tips and tricks and sneak peeks at our product labs!
  6. Bill Togkas
    Bill Togkas avatar
    18 posts
    Member since:
    Mar 2010

    Posted 18 Aug 2012 Link to this post

    Thanks Tim for the info

    Looking forward for Q3 2012  (:D)
  7. Bill Togkas
    Bill Togkas avatar
    18 posts
    Member since:
    Mar 2010

    Posted 19 Oct 2012 Link to this post

    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?
  8. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 22 Oct 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
  9. Bill Togkas
    Bill Togkas avatar
    18 posts
    Member since:
    Mar 2010

    Posted 22 Oct 2012 Link to this post

    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.
  10. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 24 Oct 2012 Link to this post

    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
    Telerik OpenAccess ORM Meets ASP.NET Web API. Read more.
Back to Top
DevCraft banner