Many years back when ASP 2.0 (not ASP.NET) was used as primary server language for web application on Microsoft platform, it was common that business logic was implemented using stored procedures on database side. Nowadays, this approach is used less and less especially because of.NET capabilities to act as full-featured application layer. Big step forward were also arise of various ORM tools (such as NHibernate) which did awful of creating CRUD operations completely for developer. I am against any generalization but I am not far from truth if I say that most of newly developed .NET software today uses some kind of object-relational mapping technique at least for very basic SQL operations such as insert, update and delete.

NHibernate is good option but there are still some concept disadvantages:

• Your domain object model must be very well designed and carefully mapped to relational data. ORM creates pressure to developer to sometimes “align” object model to relational model just because mapping could be done easier.
• It brings performance drawbacks especially during application startup
• You can easily misuse ORM without proper technical background of your ORM tool (such as nhibernate).
• You are limited what you can do with your SQL code. It applies to DDL and DML code as well.

If you are facing to complex SQL query you can use things like NHibernate criterias or  NHibernate Linq provider which may provide you way to build that kind of queries.

There are basically three scenarios where NHibernate is not helpful:

• Even more complex data retrieval query which you want to fine tune using e.g. SQL Server query hints.
• Any DML batch operation which affects more rows at one time
• Any complex application logic which require “batch approach” to your data and cannot be done “row-by-row” using your domain model manipulation and its mapping to relational data

For those scenarios, you must simply go lower in your application stack and deal with it directly in database. For first scenario you could use database views, for second and third you could use stored procedures mapped as named query in NHibernate.

Imagine even more complex application logic which must be implemented as stored procedures just because of performance – if you deploy your best developer skills such as encapsulation or reuse, you can deal with many stored procedures and functions (you can organize them with SQL Treeo Add-In in your SQL Server Management Studio).

I’ve identified very bad developer habit “do-it-at-application-layer-at-all-costs” which is that developer does even very complex SQL tasks using manipulating object model and ORM. Just not to dirty his code with some direct SQL. It inevitably leads to many row-by-tow operations and inefficient queries made by confused OR mapping components.

Head first – sometimes few stored procedures, functions or views at proper places could make better job than do-it-at-application-layer-at-all-costs.

Jakub Dvorák