ORM isn’t an excuse for not using Stored Procedures

When I have looked at ORM tools, specifically NHibernate, the one thing that has always stopped me from using them is the fact that they dynamically build the SQL to access the database. For years it has been drummed in that you should always use Stored Procedures to access your database. The key reasons are:

• Performance: Stored Procedures are fasted than un-compiled statements
• Security: If you use Stored Procedures you don’t need to give permissions to the underlying tables
• Vulnerability:Stored Procedures aren’t suseptable to SQL injection attacks

I was very pleased to hear via Dot Net Rocks that there is a ORM tool, iBATIS that allows you to use Stored Procedures. The configuration would look like this:

<procedure id="SwapEmailAddresses" parameterMap="swap-params">
ps_swap_email_address
</procedure>
...
<parameterMap id="swap-params">
<parameter property="email1" column="First_Email" />
<parameter property="email2" column="Second_Email" />
</parameterMap>

ORM has been discussed on pretty much every episode of Dot Net Rocks since they interviewed Oren Eini from the NHibernate project. It is interesting to get Richard Campbell‘s perspective given his DBA slant on things. The impression I get from Richard’s comments is that he wants the SQL generated by these tools to be as good as any SQL that would be hand buit by a developer (Stored Procedures included).

Sounds like all of this discussion is going to culminate in a passionate ORM panel discussion, or a Carl refers to it ORM Smack-down. I will definitely have to blog about that when it happens.

Tags: .Net