7/06/2007

Database-Agnostic or Database-Specific .NET Architecture?


This is one of those flaming debates which really derives from (or perhaps just implements the interface of) the "Stored Procedures Are Evil" debate. I title it thusly because that's the real issue in my mind - not whether one should use stored procs or parameterized textual queries - but whether code should be written using a provider model that allows any RDBMS system to be "plugged in", or whether to exploit all the features of a particular database system and say "be damned" to the provider model. In other words, "this is the app, we take advantage of x, y and z special features of xyz brand database that we wanted to use, and you can't switch databases with this app". So what the hell is wrong with that? For certain apps, that can be a good thing.

Here are a few juicy links on the original issue to get your blood boiling, if you haven't seen them already:

Bouma

Howard

Miller

DeBetta

Attwood

Personally, I can and have taken both approaches. I've used the Enterprise Library (3.0 and now, 3.1) to have an infrastructure that is RDBMS - agnostic, as long as there is a Provider written for the particular brand of Database you want to use with it. In fact, although we didn't use it for the client, I reworked and unit-tested an Oracle Provider (ODP) for it. I've used CodeSmith with NetTiers, I've used Db4.0 (which doesn't even use an RDBMS); I've used Subsonic (a hybrid) and others. Do they handle FullText Search? Nope. If you want that, you need to make a decision, like "this is the app", above, and stick to it.

I have no quibble with the "ORM D00ds" insisting on not using stored procs and doing all the database logic "Not in the database" - but within their mapping and DAL classes. An ORM framework is the type of app that you probably do want to be database-agnostic. The problem is, that's not the only architectural modality we ever face as developers. If you look, for example, at SQL Server 2005 -- you've got Service Broker for very transactional MSMQ-like notifications and updates, you have SQLCache invalidation, you've got TABLE VARIABLES where you can do some sophisticated and lightning fast data - manipulation that would be virtually impossible to perform from "outside the database". You have built - in WebServices, triggers, complex cascading delete logic, User-Defined-Functions - all of this stuff either cannot be reproduced outside of the database, or it is less efficient if you can reproduce it there.

You've got updateable Views, the XML Data type, and the list goes on. And finally, you have CLR-Hosted .NET code hooked into T-SQL stored procs, where literally - the sky is the limit on what you can accomplish. In fact, I just finished reading a post on the MS C# newsgroup where Nick Paldino claims that CLR-hosted .NET UDF's to parse delimited strings of items are faster than T-SQL code - and he is probably right.

Are you gonna tell me that I have to "dumb down" SQL Server 2005 (or Oracle, or whatever my chosen RDBMS platform) and only use the most basic of features in my application so that it can conform to your narrow- minded view of the world whose mantra states that "Stored Procedures Are Evil"? That's Honky-Code, man!

Let me try to boil it down to a basic theme: Databases like Oracle, SQL Server 2005, etc. aren't just "CRUD" any longer. They offer advanced features. If you -- as a developer -- are entrenched in this CRUD and portablity thing, you may think that you are pretty advanced as a programmer, but the fact of the matter is -- you've put yourself into a box where you may never be able to take advantage of everything an advanced product offers. Some applications do fine with the Provider - DB agnostic "no sproc" model; other applications can and should be designed to take full advantage of the strong features of a particular brand of database - with the idea that you ARE NOT ever going to switch it to a different brand. The main lesson I've gotten here is that when people start making "blanket statements" such as "Stored Procedures are Evil" - learn to run the other way. The best approach, in my view, is to look at each situation and avoid putting thinking into restrictive "boxes" via "XYZ are evil" type pronouncements.

That's my take on it. What do you think?

Practically Done Events

I recently heard from Jonathan Goodyear of ASPSoft, who tells me they are now featuring a new series of short seminars for .NET Developers who can't "take a whole week off" for a conference. Here's the "Practically Done" page from Jon's site. Their first deal features John Papa, who has an excellent reputation and has done some really good MSDN Magazine articles. Jon tells me that future sessions will feature AJAX and Silverlight. Something to keep an eye on!Here is a link to the event setup.