Support multiple databases

20 posts, 0 answers
  1. Eddie Conner
    Eddie Conner avatar
    32 posts
    Member since:
    Dec 2009

    Posted 23 Jun 2010 Link to this post

    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? 
  2. Ady
    Admin
    Ady avatar
    589 posts

    Posted 24 Jun 2010 Link to this post

    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
    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. Eddie Conner
    Eddie Conner avatar
    32 posts
    Member since:
    Dec 2009

    Posted 19 Jul 2010 Link to this post

    Are there any examples of switching connections during runtime?
  5. Ady
    Admin
    Ady avatar
    589 posts

    Posted 22 Jul 2010 Link to this post

    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
    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
  6. Roland
    Roland avatar
    156 posts
    Member since:
    Feb 2009

    Posted 19 Nov 2010 Link to this post

    Is that possible with Forward Mapping in Q3 2010 ?
  7. Ady
    Admin
    Ady avatar
    589 posts

    Posted 26 Nov 2010 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  8. Roland
    Roland avatar
    156 posts
    Member since:
    Feb 2009

    Posted 26 Nov 2010 Link to this post

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

  9. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 29 Nov 2010 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  10. Stephane Zanoni
    Stephane Zanoni avatar
    23 posts
    Member since:
    Jul 2005

    Posted 29 Nov 2010 Link to this post

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

    Posted 30 Nov 2010 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  12. Roland
    Roland avatar
    156 posts
    Member since:
    Feb 2009

    Posted 30 Nov 2010 Link to this post

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

    Posted 01 Dec 2010 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  14. Roland
    Roland avatar
    156 posts
    Member since:
    Feb 2009

    Posted 01 Dec 2010 Link to this post

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

    Posted 03 Dec 2010 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  16. Roland
    Roland avatar
    156 posts
    Member since:
    Feb 2009

    Posted 03 Dec 2010 Link to this post

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

    Posted 09 Dec 2010 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  18. Roland
    Roland avatar
    156 posts
    Member since:
    Feb 2009

    Posted 09 Dec 2010 Link to this post

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

    Posted 14 Dec 2010 Link to this post

    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
    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.
  20. Mhundz
    Mhundz avatar
    1 posts
    Member since:
    Jan 2015

    Posted 17 Feb 2015 in reply to Ady Link to this post

    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.
  21. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 18 Feb 2015 Link to this post

    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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner