Both MS-SQL and Oracle are supported.

c# dapper entity-framework repository-pattern sql-server


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 ?

7/24/2016 7:22:10 PM

Accepted Answer

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 :

Can you use Microsoft Entity Framework with Oracle?

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

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:

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.

5/23/2017 12:32:42 PM

Popular Answer

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

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow