Application that works in both SQL Server And Oracle Databases

Question

What is the best approach to build a small (but scalable) application that works with Sql Server or Oracle?

I'm interested in build apps that supports multiple databases, in the process behind the feature.

Answer 1

Using an ORM that supports multiple databases is the first step here. You could look at either NHibernate or Entity framework for example - both have oracle and sql server support. That way you should just have to swap out the database mappings to get the application to work on either DBMS.

Edit - thanks to tvanfosson, added the 'new' link for nhibernate.

Answer 2

My suggestion would be to use an existing (free) framework, like nHibernate, which abstracts out the dependence on the database for you. Alternatively, you'll need to define your own abstraction layer that is able to interact with drivers for either of the two databases.

Answer 3

I would use an OR/M. Most of these have support for many different database vendors and have a database agnostic language to do quering and the like.

I can recommend NHibnernate for C#.

Answer 4

as a complement to the other answers, you should tak a look at DbProviderFactories architecture in ADO.Net... a bit low-profiled but maybe useful for you.

Answer 5

As many people have pointed out, using an ORM could solve your problem. I've used LLBLGen with great success. Alternatively you can use the interfaces IConnection, ICommand and so on to roll your own ConnectionFactory.

Answer 6

In addition to the ORM comments; sometimes life is not that simple.

You must keep separate scripts for generating your tables, views, and stored procedures on both systems as they will differ.

You may have the need to do something tricky for performance reasons that is specific to one database platform. For example, making a new partition in Oracle.

You should try to do it at this level by encapsulating it in a view or stored procedure.

Your client code can call the stored procedure with the same signature on any database. You can write a stored procedure that does nothing or lots depending on what that databse requires.