Suggestions for pluggable database layer

Question

I am currently working on a software project which uses SQL Server database to store data. Since there are some plans to move away from SQL Server to Oracle, one of the requirements is to build pluggable database layer.

So my question is what is the best way to do it?

Answer 1

You have lots of choices. One option is to go with one of the various Object Relational Mapper (ORM) frameworks out there. NHibernate is a popular one, but Microsoft's Entity Framework (in v4) is a reasonable possibility as well, and it integrates better with Linq, if that's the sort of thing you're interested in.

A second option (not necessarily exclusive of the above) is to implement something like the Repository pattern, and run all database access through a repository layer. Some folks see the ORM frameworks as a replacement for the Repository pattern; other see a repository layer adding value on top of the ORM framework. The real value that a repository gives you is the ability to swap out ORM layers. Only you know whether that's a reasonable likelihood, and even if it is, it may be more work to implement the additional repository level than to just re-bind everything to the new ORM.

Answer 2

I suggest using Entity Framework, quite easier than NHibernate and being matured very fast. For oracle support in EF, you'll need the oracle provider.

Along with using Entity framework, I suggest using a pattern such as Repository Pattern. This way, you can use Dependency Injection to change the implementation of your choice. Your application becomes independent of database or the ORM itself. So you could also use NHibernate if you wish.