What is a good approach for data access layer?

Question

Our software is an individual human resource management system (HRMS) using ASP.NET with Oracle as a database, and now we are actually moving on to creating a product that supports several tenants with their own databases.

Our options:

• Use NHibernate to support multiple databases and use OO. But we relate to the NHibernate learning curve and to any problem we have encountered.

• Create a generic DAL that will continue to work 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 supporting multiple database-dependent versions of a single script.

• Provide software as a service (SaaS) and support the way you do business. However, there may be customers who do not want or do not trust the cloud or other SaaS business models.

With that in mind, what is the best technology for data access layer?

Answer 1

I would advise you to spend time and study NHibernate, it has a number of options for querying and updating databases that make it database agnostic, which means that you only need to write one set of scripts, for example, in HQL.

I would recommend Nhibernate for example, this is a great book for you.

Answer 2

I would say that NHibernate is impressive at first glance and seems very difficult to learn. Therefore, by quickly reading an introductory document of 260 pages, and insisting on the tasks that I need to complete in test applications, NHibernate really fits. And if you're not fascinated by the XML mapping files, just use FluentNHibernate , which allows you to use OOP to map business domain objects.

In addition, if you are not completely free at NHibernate and prefer to go the other way, Enterprise Library 4.1 (October 2008) can either be a useful tool. Depending on the situation, in some organizations I chose the hybrid approach NHibernate - Enterprise Library. The data access application block (DAAB) in the corporate library is fairly easy to learn and does not require you to learn anything, but what you already know. You just need to know which object to use to create your DbConnection from the DatabaseProviderFactory class to read from your configuration files, and you can specify the default database.

As for my problems, I often use both NHibernate with the Enterprise Library. DAAB allows me, for example, to specify a database connection for each configuration file, since I prefer to parameterize only one connection per file. This allows me not to deploy unnecessary configuration files for configurations that have not changed at all, and only deploy a new configuration file for another connection. So, if you combine a new module that needs to connect somewhere else to another data warehouse, you create your module without worrying about everything else, update your software using the module DLL along with this new DAAB configuration file.

As for NHibernate, it’s important not to get rid of ISessionFactory when you no longer need it. This is an expensive instance, so you want to keep it in memory. However, you can serialize your class of configuration objects (like Serializable), so your application can build its configuration only if something has changed in your NHibernate configuration file. Again, I suggest you use the default hibernate.cfg.xml configuration file for NHibernate, so you won’t have to deploy the app.config file again and again when updates appear.

Hope this helps! Let me know if you need more information.

Answer 3

We had a similar scenario "Support HRM + ASP.NET + multi DBs", and we chose MyGeneration dOOdads Architecture, and the product has already been released and works very well!

just google for mygenation and you're ready to go!

Regarding SaaS: yes, many customers will not agree to have data there in the cloud, no matter how secure it is. You could convince some of these customers after their reputation in the market. therefore, the first phase focuses on design that supports “internal deployment” as a high priority, and Saas as a second priority. if "internal deployment" is not an option, you better contact a SaaS marketing consultant.

Answer 4

Using NHibernate will almost certainly be cheaper (both in terms of development costs and in terms of training) than a custom ORM for a project of any significant size (more than 20 tables?), Which must support several database providers. I don’t know how to answer item 3 on your list because I don’t know how to compare what you are doing today using NHibernate. It is possible that what you are doing today is actually better than NHibernate, but you have not provided enough information in this regard. This is a risky business decision to lock yourself in a specific business model based on a technology solution that can be costly to cancel later.

Answer 5

I would go with option number 3, as this will allow you to enter the market earlier and, I hope, will begin a reliable income stream. Customers will be much more willing to finance the conversion of an existing successful product into an autonomous system than the proposed product. You will also receive invaluable feedback from real users who will improve the product. And you may find that there is no demand for an autonomous system.

If you started from scratch, I would suggest exploring NHibernate.

Answer 6

I think it all depends on the priorities!

In my experience, I will always take into account the fact that any part of the system can be changed and therefore will always keep in mind how the system will work with other databases and move forward with this.

It is clear that you know better and adapt / refactor later when you have time and money for this. You can inject NHibernate / IOCs into later components as they are developed, and then come back and refactor.