Recommendations for supporting both Oracle and SQL Server in the same ASP.NET app with NHibernate

Question

Our client wants to support both SQL Server and Oracle in the next project. Our experience comes from .NET/SQL Server platform. We will hire an Oracle developer, but our concern is with the DataAccess code. Will NHibernate make the DB Engine transparent for us? I don't think so, but i would like to hear from developers who have faced similar situations.

I know this question is a little vague, because i don't have Oracle experience, so i don't know what issues we will find.

Answer 1

You can easily use NHibernate to make your application database-agnostic by following some basic practices:

• Design your object model first.
• Do not use any database-specific code. You need somebody with good C# experience, not an Oracle developer. Do not rely on stuff like triggers, stored procedures, etc.
• Let NHibernate generate the DB schemas at least initially (you can tweak things like indexes later) It will choose the best available datatypes for each DB.
• Use a DB-agnostic POID generator (hilo or guid) instead of sequences or identity.
• Try to avoid using SQL. HQL and Linq work fine in 99% of the cases.
• Avoid NH features that are not supported by all of your target DB (for example, Future, MultiCriteria, etc)

NHibernate has a great community. You can always ask your questions in http://groups.google.com/group/nhusers besides posting here.

Answer 2

There are three things to consider - the ISession object, the SQL queries that are generated and your plain-old-clr-objects that are mapped to tables.

NHiberante will generate the required SQL queries based upon the chosen database dialect. If you configure NHibernate to use the SQL Server dialect it will generate SQL server correct SQL statements. This can easily be configured dynamically at runtime based on configuration.

You also need to configure your session to connect to the right type of database. Again, various configuration methods can support dynamic ISession creation at runtime.

Your actual data objects which are mapped to tables should not need to change based on database choice. One of NHibernates strengths is flexibility it provides in supporting multiple databases via a (fairly) simply configuration change and some up-front architectural thought.

See http://codebetter.com/blogs/karlseguin/archive/2009/03/30/using-nhibernate-with-multiple-databases.aspx for some examples of how you might abstract the underlying database away from the creation and usage of NHibernate.