I want to write an ASP.NET 4.0 application with IBM DB2 Express-C as its back-end. One thing that worries me, is hosting this application on a remote server. I don't know any hosting provider who provides ASP.NET DB2 hosting in the same price range as of SQL Server.

I also want to allow the customer to select the database of their choice from this list:

  1. SQL Sever
  2. Oracle
  3. DB2
  4. PostgreSQL
  5. Firebird

There are the same hosting worries with Oracle and Firebird.

Hosting issues aside, I want to know how to design an application so it is agnostic to the kind of RDBMS used and will work with any of the above mentioned ones.

Are there any such applications around?

  • This question is exceptionally broad. Could you narrow down your question to something more specific than "How do I design an application that works with a database?" – Jonathan Rich Feb 13 '12 at 18:11
  • Leave hosting issues, tell me how to design this application to work with first three databases. Given a choice to the customer. – RPK Feb 13 '12 at 18:18
  • The first three are supported by libraries availble in .NET. en.wikipedia.org/wiki/… – Rig Feb 13 '12 at 18:20
  • @Rig Sorry, I should have cleared. I am not asking about libraries. I want to know how to manage Stored Procedures, Triggers and Queries to work well with either database. I also want to know how to design a common connection class. – RPK Feb 13 '12 at 18:25
  • 4
    @RPK there is a common connection class. It is called System.Data.DbConnection. – Wyatt Barnett Feb 13 '12 at 18:37

The answer to use an ORM that allows you to abstract that away from the databases themselves. NHibernate is the first that comes to mind, though EF is an acceptable choice is there's an adaptor for the DBs you need. I'm not sure how good the support is for DB2, but SQL Server and Oracle work fine.

As to stored procedures and trigger: don't use them. As soon as you need to move any logic into the database, you're stuck managing them in all the databases you support. If you can let any logic living in the application layer behind an abstraction (NHibernate) then you can get away with the framework doing the translation for you. HQL will allow you write SQL like queries for NHibernate that are an abstraction as well if needed.

  • I remember Tom Kyte (Oracle) has written in his book to use most of the RDBMS capabilities as possible. This reduces code and application is manageable. I don't know whether I am moving in the right direction or not, but not using Stored Procedures and Triggers adds an overhead of writing code to handle things that may have been automatically handled by the database. – RPK Feb 14 '12 at 15:36
  • 1
    I'm sure Mr. Kyte's advice was predicated on the assumption that you were building an application on top of one specific database. If you want to do this with multiple back-ends, then you're going to wind up with basically separate applications that share little beyond their specifications, or you'll wind up writing to a common subset of capabilities that won't let you take advantage of any DB-specific functionality. – TMN Feb 14 '12 at 18:38
  • Another point that has come up as we (as software engineers) mature is that it is much harder to test any logic in the database than it is in the application. It is just much easier to isolate concerns, write SOLID code, and just generally be in a better place in terms of maintainability. Logic in the DB generally just ends up biting people in the butt - esp. unexpected triggers :( – Travis Feb 14 '12 at 20:58
  • 1
    @RPK Tom Kyte has a particular interest in getting you as tightly bound to the product he's selling as possible. Your express goal is the exact opposite... I would take his advice with a grain of salt. – Eric King Feb 14 '12 at 21:34

If you stick with the capabilities provided by Entity Framework, you should be OK for SQL Server and Oracle support, and probably DB2 as well.

I would caution, however, that you not look at this as simply a matter of different SQL dialects. Have you considered the different security models these databases have? Have you thought about how you're going to maintain your database scripts? Are you going to have test environments for all these databases? How are you going to generate sizing metrics, so customers know how much disk usage to expect? I work for a pretty large software shop, and we don't support any databases we don't have to, just because of all the overhead it involves.

  • I know it is going to be complex. To start with, I may offer only two choices. – RPK Feb 14 '12 at 15:37

You can and should abstract out your data layer. However, before you arbitrarily decide that your aim is to support multiple databases you need to realise that:

  1. All databases have their quirks and behave in different ways, so even if you use an ORM (the entity framework is OK, but I'm not convinced about how good it is for large data volumes) you need to be aware that it might not work identically for all databases (distributed transactions may or may not be supported for example). Given this you increase your development cost, possibly considerably, if you want to provide a consistent experience across all databases.
  2. Even if you ORM is Awesome and does everything identically in every database, you still need to run all of your acceptance tests in each database environment to confirm this which definitely increases your QA costs.

Now you are writing a .NET website, by the sound of it for the consumer market, so it will be hosted on a box somewhere like Godaddy with access to a SQL Server database as part of the package.

Given that what is the expected return on investment from supporting any database other than SQL Server?

I would say that only if you think this figure is positive and in proportion with your other development costs should you consider supporting more than one database, and then only support other databases once your product is established and stable with a SQL Server back end and there is a definite demand from your customers.

  • I would suggest trying to do a SaaS product over self hosted anyways. Cloud hosting on AWS is low priced enough to warent the simplicity of owning the install of the product and able to ensure your customers have a great experience because everyone is on the same OS and DB. – Travis Feb 14 '12 at 22:26
  • And the great thing about "the cloud" is that you no longer own any of your data! I work with a SaaS software product that lives "in the cloud" and I'd much rather it didn't! – user23157 Feb 15 '12 at 9:25

You must use ORM if you want to stay DRY and as @Travis pointed you should use NHibernate because it has support for all of them already prepared. Without ORM you will either have data access completely in stored procedures and you will write all of them in every database again or you will have special data access assembly per database with queries and commands defined in DB specific SQL dialect. ORM already wrap this functionality and uses universal query language translated to dialect required by the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.