Manual DAL & BLL vs. ORM

Question

Which approach is better: 1) to use a third-party ORM system or 2) manually write DAL and BLL code to work with the database?

1) In one of our projects, we decided using the DevExpress XPO ORM system, and we ran across lots of slight problems that wasted a lot of our time. Amd still from time to time we encounter problems and exceptions that come from this ORM, and we do not have full understanding and control of this "black box".

2) In another project, we decided to write the DAL and BLL from scratch. Although this implied writing boring code many, many times, but this approach proved to be more versatile and flexible: we had full control over the way data was held in the database, how it was obtained from it, etc. And all the bugs could be fixed in a direct and easy way.

Which approach is generally better? Maybe the problem is just with the ORM that we used (DevExpress XPO), and maybe other ORMs are better (such as NHibernate)?

Is it worth using ADO Entiry Framework?

I found that the DotNetNuke CMS uses its own DAL and BLL code. What about other projects?

I would like to get info on your personal experience: which approach do you use in your projects, which is preferable?

Thank you.

Quinlan

Answer 1

Perhaps a little of both is the right fit. You could use a product like SubSonic. That way, you can design your database, generate your DAL code (removing all that boring stuff), use partial classes to extend it with your own code, use Stored Procedures if you want to, and generally get more stuff done.

That's what I do. I find it's the right balance between automation and control.

I'd also point out that I think you're on the right path by trying out different approaches and seeing what works best for you. I think that's ultimately the source for your answer.

Sisson

Answer 2

My personal experience has been that ORM is usually a complete waste of time.

First, consider the history behind this. Back in the 60s and early 70s, we had these DBMSes using the hierarchical and network models. These were a bit of a pain to use, since when querying them you had to deal with all of the mechanics of retrieval: follow links between records all over the place and deal with the situation when the links weren't the links you wanted (e.g., were pointing in the wrong direction for your particular query). So Codd thought up the idea of a relational DBMS: specify the relationships between things, say in your query only what you want, and let the DBMS deal with figuring out the mechanics of retrieving it. Once we had a couple of good implementations of this, the database guys were overjoyed, everybody switched to it, and the world was happy.

Until the OO guys came along into the business world.

The OO guys found this impedance mismatch: the DBMSes used in business programming were relational, but internally the OO guys stored things with links (references), and found things by figuring out the details of which links they had to follow and following them. Yup: this is essentially the hierarchical or network DBMS model. So they put a lot of (often ingenious) effort into layering that hierarchical/network model back on to relational databases, incidently throwing out many of the advantages given to us by RDBMSes.

My advice is to learn the relational model, design your system around it if it's suitable (it very frequently is), and use the power of your RDBMS. You'll avoid the impedance mismatch, you'll generally find the queries easy to write, and you'll avoid performance problems (such as your ORM layer taking hundreds of queries to do what it ought to be doing in one).

There is a certain amount of "mapping" to be done when it comes to processing the results of a query, but this goes pretty easily if you think about it in the right way: the heading of the result relation maps to a class, and each tuple in the relation is an object. Depending on what further logic you need, it may or may not be worth defining an actual class for this; it may be easy enough just to work through a list of hashes generated from the result. Just go through and process the list, doing what you need to do, and you're done.

Armilda

I would certainly agree with Curt about knowing relational theory. I think one of the dangers of tools like EF is that programmers will let the DAL do their work for them without understanding databases, and that is a mistake. – Zemstvo

Curt, Do you have an example(s) of what you're saying in the last paragraph, both types you mentioned? I really don't like the ORMs that I've looked at. – Perry

How you do the mapping of a result is highly lanugage- and situation-dependent. Say we have a query SELECT id, name FROM some_table whose result heading is id INTEGER, name VARCHAR(255). One extreme is to define a class of objects for this result: class IdName { public final int id; public final String name; } (Java) and then have a little routine that parses the query result producing a list of these objects. Going the other way, you might just create in Ruby an Array of Hash objects, resulting in something like [ { :id => 17, :name => 'Fred' }, { :id => 23, :name => 'Joe' }]. – Armilda

Answer 3

Recently I made the decision to use Linq to SQL on a new project, and I really like it. It is lightweight, high-performance, intuitive, and has many gurus at microsoft (and others) that blog about it.

Linq to SQL works by creating a data layer of c# objects from your database. DevExpress XPO works in the opposite direction, creating tables for your C# business objects. The Entity Framework is supposed to work either way. I am a database guy, so the idea of a framework designing the database for me doesn't make much sense, although I can see the attractiveness of that.

My Linq to SQL project is a medium-sized project (hundreds, maybe thousands of users). For smaller projects sometimes I just use SQLCommand and SQLConnection objects, and talk directly to the database, with good results. I have also used SQLDataSource objects as containers for my CRUD, but these seem clunky.

DALs make more sense the larger your project is. If it is a web application, I always use some sort of DAL because they have built-in protections against things like SQL injection attacks, better handling of null values, etc.

I debated whether to use the Entity Framework for my project, since Microsoft says this will be their go-to solution for data access in the future. But EF feels immature to me, and if you search StackOverflow for Entity Framework, you will find several people who are struggling with small, obtuse problems. I suspect version 2 will be much better.

I don't know anything about nHibernate, but there are people out there who love it and would not use anything else.

Zemstvo

Unfortunately, I am not quite familiar with the LINQ technology, I just quickly read a chapter about it in a book. Does LINQ to SQL allow you to map a row from a query such as "SELECT * FROM Clients" to the Client class that you created? Or does it only allow you to work with automatically generated strongly typed row classes? – Quinlan

Yes, you can do this with Linq queries. They look just like SQL, except that they map to your strongly-typed classes, so you get intellisense and compiler checks. – Zemstvo

Speaking about LINQ to SQL, I think that it helps to create a DAL with less code and more easily, thanks to features like Intellisense, etc. But it does NOT free us from creating BLL classes and providers. We still will have to create classes with methods like "ClientsProvider.GetClientsWhoAreRich()", that will communicate with the DAL, and in the DAL, we can use either traditional SqlCommands or LINQ. But LINQ does not free us from creating the BLL "ClientsProvider" class. Am I not right? – Quinlan

Correct. But you don't necessarily need to write a method for every possible retrieval scenario into the Clients Provider. If you return IQueryable results in these methods, you can always run another LINQ query on the result sets upstream. Note that Linq to SQL already provides Create, Read, Update, Delete functionality. You don't have to write those queries. – Zemstvo

I disagree. LINQ is actually the path back to the relational view of the data. I may hate C# even more than I hate Java, but LINQ is a great step ahead. LINQ pushes you towards putting your data back (well, mostly for the first time, in to) the relational model, and avoiding a bunch of the OO cruft you'd have to deal with otherwise. – Armilda

Answer 4

You might try using NHibernate. Since it's open source, it's not exactly a black box. It is very versatile, and it has many extensibility points for you to plug in your own additional or replacement functionality.

Comment 1:

NHibernate is a true ORM, in that it permits you to create a mapping between your arbitrary domain model (classes) and your arbitrary data model (tables, views, functions, and procedures). You tell it how you want your classes to be mapped to tables, for example, whether this class maps to two joined tables or two classes map to the same table, whether this class property maps to a many-to-many relation, etc. NHibernate expects your data model to be mostly normalized, but it does not require that your data model correspond precisely to your domain model, nor does it generate your data model.

Comment 2:

NHibernate's approach is to permit you to write any classes you like, and then after that to tell NHibernate how to map those classes to tables. There's no special base class to inherit from, no special list class that all your one-to-many properties have to be, etc. NHibernate can do its magic without them. In fact, your business object classes are not supposed to have any dependencies on NHibernate at all. Your business object classes, by themselves, have absolutely no persistence or database code in them.

You will most likely find that you can exercise very fine-grained control over the data-access strategies that NHibernate uses, so much so that NHibernate is likely to be an excellent choice for your complex cases as well. However, in any given context, you are free to use NHibernate or not to use it (in favor of more customized DAL code), as you like, because NHibernate tries not to get in your way when you don't need it. So you can use a custom DAL or DevExpress XPO in one BLL class (or method), and you can use NHibernate in another.

Cantina

Does NHibernate allow to design the database structure by ourselves, or does it create the tables, etc. itself? We want to have control over the design of the DB. – Quinlan

NHibernate is a true ORM, in that it permits you to create a mapping between your arbitrary domain model (classes) and your arbitrary data model (tables, views, functions, and procedures). You tell it how you want your classes to be mapped to tables, for example, whether this class maps to two joined tables or two classes map to the same table, whether this class property maps to a many-to-many relation, etc. NHibernate expects your data model to be mostly normalized, but it does not require that your data model correspond precisely to your domain model, nor does it generate your data model. – Cantina

What you are saying about NHibernate is quite a cool thing. And does NHibernate allow you to use a "manual DAL / BLL" approach, at the same time, within the same application? This may be needed in complex cases, where more control is needed to work with the database. In DexExpress XPO, we found it to be a problem. – Quinlan

@micha12 I updated my answer to respond to your latest question. – Cantina

Justice, thank you for your great comments on NHibernate. By the way, are you a true user of NHibernate, or maybe you are a member of the development team? :) I hope that not the latter :) – Quinlan

I've used NHibernate in a commercial app, as well as in a few side projects. – Cantina

Answer 5

I recently took part in sufficiently large interesting project. I didn't join it from the beginning and we had to support already implemented architecture. Data access to all objects was implemented through stored procedures and automatically generated wrapper-methods on .NET that returned DataTable objects. The development process in such system was really slow and inefficient. We had to write huge stored procedure on PL/SQL for every query, that could be expressed in simple LINQ query. If we had used ORM, we would have implement project several times faster. And I don't see any advantage of such architecture.

I admit, that it is just particular not very successful project, but I made following conclusion: Before refusing to use ORM think twice, do you really need such flexibility and control over database? I think in most cases it isn't worth wasted time and money.

Podvin

Did you use a third-party ORM in other projects? If yes, was that successful? If yes, what were those ORMs that you were using? – Quinlan

Yes, it was not very large business application on DataObjects.Net 3.9 (it was two years ago). It is very useful ORM, although it has many limitations (mainly in database structure) and redundant features. Surely there were some performance-related issues and difficulties with offline classes concept, but generally project was successful. – Podvin

Btw, we used DevExpress win-forms controls in that project and thought about using XPO. It looks like easy to use and useful ORM for rapid application development. – Podvin

We also thought that XPO was something that would help us make the database access efficiently, but it turned out to be false: we had so many slight but really frustrating problems and exceptions with XPO that now I understand that we should have written a DAL and BLL from scratch - this would take 30% of the time we spent on implementing XPO. – Quinlan

Answer 6

As others explain, there is a fundamental difficulty with ORM's that make it such that no existing solution does a very good job of doing the right thing, most of the time. This Blog Post: The Vietnam Of Computer Science echoes some of my feelings about it.

The executive summary is something along the lines of the assumptions and optimizations that are incompatible between object and relational models. although early returns are good, as the project progresses, the abstractions of the ORM fall short, and the extra overhead of working around it tends to cancel out the successes.

Disney

Answer 7

I have used Bold for Delphi four years now. It is great but it is not available anymore for sale and it lacks some features like databinding. ECO the successor has all that. No I'm not selling ECO-licenses or something but I just think it is a pity that so few people realize what MDD (Model Driven Development) can do. Ability to solve more complex problems in less time and fewer bugs. This is very hard to measure but I have heard something like 5-10 more efficient development. And as I work with it every day I know this is true.

Maybe some traditional developer that is centered around data and SQL say:

1. "But what about performance?"
2. "I may loose control of what SQL is run!"

Well...

1. If you want to load 10000 instances of a table as fast as possible it may be better to use stored procedures, but most application don't do this. Both Bold and ECO use simple SQL queries to load data. Performance is highly dependent of the number of queries to the database to load a certain amount of data. Developer can help by saying this data belong to each other. Load them as effiecent as possible.

2. The actual queries that is executed can of course be logged to catch any performance problems. And if you really want to use your hyper optimized SQL query this is no problem as long as it don't update the database.

There is many ORM system to choose from, specially if you use dot.net. But honestly it is very very hard to do a good ORM framework. It should be concentrated around the model. If the model change, it should be an easy task to change database and the code dependent of the model. This make it easy to maintain. The cost for making small but many changes is very low. Many developers do the mistake to center around the database and adapt everthing around that. In my opinion this is not the best way to work.

More should try ECO. It is free to use an unlimited time as long the model is no more than 12 classes. You can do a lot with 12 classes!

Hobbyhorse

Answer 8

I suggest you to use Code Smith Tool for creating Nettiers, that is a good option for developer.

Creuse