How to support MSSQL and Oracle with a single Assembly

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
ivoryguard
Posts: 5
Joined: Wed 13 Oct 2010 01:11

How to support MSSQL and Oracle with a single Assembly

Post by ivoryguard » Wed 13 Oct 2010 01:23

Hello.

I am developing software at an ISV in Korea.

I have developed a product using WCF RIA Services, ADO.NET Entity Framework and MSSQL Express.

It works very well, but, my customer demands to support Oracle DB.

My database is simple; it has 4 tables with foreign key constraints and uses vchars, int, bit, and int with id (auto-incremental).

Could I make my app support both MSSQL and Oracle by changing only connection string?

If existing entities are reusable, it will be perfect.


If it is possible, I will DotConnect right now.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 13 Oct 2010 12:07

Yes, it is possible to implement this scenario with the help of dotConnect for Oracle.
The simplest solution is to create an Oracle script using the Generate Database From model (don't forget to change the DDL Generation Template property to "Devart SSDLToOracle.tt") and then generate the SSDL from it (using ADO.NET Entity Data Model Wizard, for example).
It will be enough to correctly handle the metadata resources (split your SQL Server .edmx into CSDL, MSL, and SSDL files, and use the common CSDL and MSL, and separate SSDL layers for MSSQL and Oracle).
Basically, that's all.
Paul Reynolds, one of our users, has created series of posts based on his experience in a similar task:
Preparing for Multiple Databases
Database Caveats
SSDL Adjustments
Database Agnostic LINQ to Entities

ivoryguard
Posts: 5
Joined: Wed 13 Oct 2010 01:11

Another Questions

Post by ivoryguard » Thu 14 Oct 2010 10:09

Thank you for the reply.

Could you please explain Devart SSDLToOracle.tt more?

And.. I have no idea to handle bit and int with ID data type of MSSQL.

I know that they don't be supported at Oracle.
Could you please give a guideline for mapping them to Oracle?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 14 Oct 2010 15:13

In EF Designer go to the Properties of the generated model, and change the DDL Generation Template property to "Devart SSDLToOracle.tt".
As for the type mappings, take a look at this article, hope it helps.
Particularly the autoincremented ID should be handled as a column with On Insert trigger (this is taken into account in our SQL generation template).

Dominik
Posts: 29
Joined: Wed 19 May 2010 07:26

Post by Dominik » Mon 22 Nov 2010 10:21

Hi,

I don't know if I understand it at all. My Oracle database is already created, but I need to create EDMX models for both environments.

I copied my original SQL Server model, and then changed the DDL Generation Template property to "Devart SSDLToOracle.tt".

This would change the SSDL types to their corresponding in Oracle, or may I change them manually?

I would like to have some automatic mode to work with both SQL Server and Oracle databases, and avoid manual changes as much as possible.

Thanks,

Dominik.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Mon 22 Nov 2010 12:13

Could you please add your suggestion at our User Voice? This is the common way our users influence our road map.

Post Reply