I have developed an enterprise product which is currently live in a few sites. This was developed on the MS stack using SQL server as back-end data storage. I have not used EF/NHibernate for data-access, instead used dapper and some custom extensions around the same. This has worked well for us since most of the queries we have are complex aggregates and filters across multiple tables. Having custom SQL largely reduces our effort in identifying bottlenecks and fixing it faster.
Now due to some business reasons we need to support oracle for one large customer (There was no way we could get them to use SQL since they are a complete oracle shop).
Assuming I need to change many data types to ensure consistency across the databases, It would be helpful if I can get some directions on overall approach
What is the best way to approach so that I can support queries on both data-sources
Approach 1 : Attempt to move majority of the queries to ANSI SQL and only have seperate queries for some specialized queries
Approach 2 : Use combination of EF and Dapper. EF will be used for simpler operations and dapper will used to heavy lifting read operations. Whereever dapper is used again might need to have seperate queries for oracle & sql
Approach 3 : Continue to use Dapper and have separate queries for all the operations
2) I have dacpac for database deployment automation. How do I achieve the same thing for oracle?
Apart from these are there any other items which I need to watch out when supporting both databases, like handling datetime, currency etc ?
As you start developing using EF , and has POCO class for all your tables, I prefer approach 3.
So, build Oracle environment with Dapper (using your POCO generated with Ms SQL Server).
You can build your DAL as a repository pattern , unit of work
Document as you go , data type mismatch between oracle /sql server, and take necessary corrections to unify DAL.
With unit test for your DAL that should pass in Oracle/SQL Server, you are safe.
As you have working system with SQL Server , and minimize developing time, study Alternative 4 :) solution "Using EntityFramework with Oracle Database." Have a look :
you can make POC (proof of concept) for that solution.
Migration steps to oracle database:
Use the Oracle Migration Workbench to migrate MS SQL Server database to oracle database
For More details:
The tool enable you to migrate all objects including the schema objects,Tables, triggers, and stored procedures and handling data type conversion. To be aware of the mapping between data type, read:
A quick Move of the application To work with Oracle Database
After migrating database, and you use Dapper, modify the connection string and start testing your application, find all bugs (don't debug one by one) to all the side effect of changes.
I expect very minor change in application (if not working as is).
Plan For change.
You can Move all DAL with dapper in shared project (if you are working using vs 2013/2015), which can be referenced by both your current application with Sql Server and the new application with Oracle Database.
Your DAL in that case include only class of connection with the connection String and the shared project.
Unit test for both oracle/Ms Sql Server
1) create shared Project which include all common tests for oracle and Ms sqL server.
2) Create unit test project For Ms Sql Server ,reference The shared project, define the connection string , add other tests (in case if exist some difference in implementation).
3) Create unit test project For oracle, as we do in step 2
4) Run both tests and be sure that all tests are passed For any change, run tests again.
If you have tests for your data access layer, I would go for approach 1.