How to support MSSQL and Oracle with a single Assembly

[ivoryguard]

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]

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]

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]

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]

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]

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