What is a good approach for a Data Access Layer?

Question

Our software is a customized Human Resource Management System (HRMS) using ASP.NET with Oracle as the database and now we are actually moving to make it a product that supports multiple tenants with their own databases.

Our options:

1. Use NHibernate to support Multiple databases and use of OO. But we concern related to NHibernate learning curve and any problem we faced.

2. Make a generalized DAL which will continue working with Oracle using stored procedures and use tools to convert it to other databases such as SQL Server or MySql. There is a risk associated with having to support multiple database-dependent versions of a single script.

3. Provide the software as a Service (SaaS) and maintain the way we conduct business. However there can may be clients who do not want or trust the Cloud or other SaaS business models.

With this in mind, what's the best Data access layer technique?

Answer 1

We had a similar scenario "HRM + ASP.NET + multi DBs support" and we chose MyGeneration's dOOdads Architecture and the product already released and working pretty well!

just google for MyGeneration and you'll be ready to go!

Regarding SaaS: yes many clients won't accept to have there data on the cloud no matter how secure its. you could convince some of these clients after having a reputation in the market. so in the first phase focus on a design that support "in-house deployment" as high priority and Saas as second priority. if the "in-house deployment" not an option you better consult SaaS marketing consultant.

Answer 2

I would advise you spend the time and learn NHibernate it has a number of options for Querying and updating databases that make it database agnostic, this means you would only have to write 1 set of scripts in for example HQL.

I would recommend Nhibernate by Example it is an excellent book to get you going.

Answer 3

Using NHibernate is almost certainly going to be cheaper (from both a development cost and learning curve perspective) than a custom built ORM for a project of any significant size (more than 20 tables?) that needs to support multiple database vendors. I don't know how to respond to item 3 on your list because I don't know how to compare whatever you are doing today to using NHibernate. It's possible that whatever you are doing today is in fact better than NHibernate, but you have not provided enough information in that regard. It's a risky business decision to lock yourself into a specific business model based on a technology decision which could be expensive to undo later.

Answer 4

I would go with option #3 since that gets you to market earliest and hopefully starts a robust revenue stream. Clients will be much more willing to fund converting an existing successful product to a stand-alone system than a proposed product. You will also get invaluable feedback from real users that will improve the product. And you may find that there isn't demand for a stand-alone system.

If you were starting from scratch I would suggest learning NHibernate.

Answer 5

I think it all depends on priorities!

In my experience is that I would always consider the fact that any part of a system can be changed and therefore would always at the least have in mind how the system would work with other databases and move forward with that.

Clearly go with what you know best and adapt/refactor later when you have the time and money to do so. You could introduce NHibernate/IOCs in later components as you develop them and then go back and refactor.

Answer 6

I would say that NHibernate is impressive at first view and seems very complex to learn. Therefore, after reading the about 260 pages introduction document rapidly, and having insisted on the tasks I needed to perform within the tests applications, NHibernate is really the way to go. And if you're not enchanted with the XML mapping files, just use FluentNHibernate which allows you to use OOP for mapping your business domain objects.

Furthermore, if you're not completely at ease with NHibernate and prefer going another way, Enterprise Library 4.1 (October 2008) might either be a useful tool. Depending on the situation, in some organizations, I have opted for an hybrid of NHibernate - Enterprise Library approach. The The Data Access Application Block (DAAB) within Enterprise Library is quite easy to learn and doesn't require you to learn anything but what you already know. You just need to know what object to use to create your DbConnection from the DatabaseProviderFactory class to read from your configuration files and you may specify a default database.

As for my concerns, I often use both NHibernate along with Enterprise Library. The DAAB allows me for example to specify a database connection per configuration file since I prefer to parameter only one connection per file. This allows me not to deploy unnecessarily config files for configs that didn't change at all, and only deploy THE new configuration file for another connection. So, if you merge a new module that must connect somewhere else to another datastore, you build your module without caring about the rest, update your software with your module's DLL along with this new DAAB config file.

As for NHibernate, an important thing not to do is to get rid of the ISessionFactory when you no longer need it. It is costful to instantiate, so you want to keep it in memory. What you can do though is the serialize your configuration object class (as it is Serializable), so your application can build its configuration only if something changed into your NHibernate config file. Then again, I suggest you use the default hibernate.cfg.xml configuration file for NHibernate, this way you won't need to deploy your app.config file over and over again when updates come.

I hope this helps! Let me know if you need further information.