4

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

  1. 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 ?

Sharath Chandra
  • 654
  • 8
  • 26

2 Answers2

2

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.

Edit2:

As you have working system with SQL Server , and minimize developing time, study Alternative 4 :) solution "Using EntityFramework with Oracle Database." Have a look :

Can you use Microsoft Entity Framework with Oracle?

http://download.oracle.com/oll/obe/EntityFrameworkOBE/EntityFrameworkOBE.htm

you can make POC (proof of concept) for that solution.

https://www.nuget.org/packages/Oracle.ManagedDataAccess.EntityFramework/

Install-Package Oracle.ManagedDataAccess.EntityFramework

Migration steps to oracle database:

Use the Oracle Migration Workbench to migrate MS SQL Server database to oracle database

For More details:

http://www.oracle.com/technetwork/topics/o57sql-100253.html

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:

https://www.mssqltips.com/sqlservertip/2944/comparing-sql-server-and-oracle-datatypes/

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.

Community
  • 1
  • 1
M.Hassan
  • 10,282
  • 5
  • 65
  • 84
  • @m-hassan, If understand you correctly, what you are suggesting is have EF POCO classes but use dapper for both SQL & oracle separately? I agree there is no getting around from having integration test suite for testing the data access across both sql & oracle – Sharath Chandra Jul 25 '16 at 04:10
  • I edit, and provide alternative 4, using EF with Oracle – M.Hassan Jul 25 '16 at 06:20
  • @m-hassan, Knowing EF well, I don't think it would work well when I have some complex queries. So looks like I need to work on EF & Dapper work on minimizing the duplication for queries. – Sharath Chandra Jul 25 '16 at 06:24
  • @m-hassan, do you have any suggestions for the deployment automation? – Sharath Chandra Jul 25 '16 at 06:25
  • Ok, that is good. Now you think in (common Unit test suit) for both oracle and sql server – M.Hassan Jul 25 '16 at 06:27
  • The guidance is definitely helpful, I am tempted to mark this as answer :), but I have just one more query regarding any guidance on deployment automation? I am using SQLPackage and dacpac for automating the database deployments. Do you have any suggestions on how to approach the oracle side of deployment on similar lines? – Sharath Chandra Jul 26 '16 at 02:17
  • Can you post other question for this new request? thanks – M.Hassan Jul 26 '16 at 11:40
  • SQLPackage is used for automating the database deployment to only Ms Sql Server, and can't be used with oracle. You start developing by migrating sql server database to oracle using oracle migration tools provided by oracle. In Oracle environment many tools are there for deployment automation including tools integrated with visual studio. – M.Hassan Jul 26 '16 at 18:19
0

If you have tests for your data access layer, I would go for approach 1.

erikkallen
  • 33,800
  • 13
  • 85
  • 120
  • I don't have tests for the data access, but I think now is the only time I can afford to refactor and also add test cases. Without them I will be looking @ endless compatibility issues – Sharath Chandra Jul 26 '16 at 02:12
  • Glad to hear the importance of Unit test. BTW Test before coding (how it comes :) ) – M.Hassan Jul 26 '16 at 11:35