.NET - Strategy for multi database support

Question

Our current web application is using SQL Server, we have a requirement for support Oracle now.

There are a few options:

1. Data Facade pattern: use Data Facade interface in Business Layer, so SQL Server and Oracle can have their own implementation of Data Access Layer. The problem is it's hard to synchronize DAL code for different type of database.

2. nHibernate: it seems promising, I don't have experience using it. As our current Data Access Layer is mixed with SQL and Stored Procedure, not sure whether there will be a lot of work involved if change to nHibernate (we are facing time pressure).

I'd like to hear your opinions. Thanks.

Answer 1

NHibernate has excellent support for multi-db applications. Here is a post on things to consider when doing that.

However, if you have "time pressure" and you are updating an application with a completely different approach (using Stored Procedures), it will definitely be non-trivial to do the change.

Answer 2

Nobody has mentioned the Microsoft Enterprise Library yet. I haven't used it so I can't provide a recommendation but I would have thought it warranted further attention. I was listening to a podcast the other day where they were stating that database inter-operability was a design goal.

Answer 3

ODP.NET provides standard ADO.NET data access, while exposing Oracle database-specific features, such as XML DB, data access performance optimizations, and Real Application Clusters connection pooling.

Answer 4

I don't see why it would be so hard to have a DAL that supports both Oracle and SQL Server. Specifically, LLBLGen does it. If it were me, I would use it. But that's because I've used it before and I'm a big fan.