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:
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.
In my experience (YMMV) 80% of db activity is CRUD. I prefer to handle that through a DAL that generates SQL on the fly, that way my code is clean and uncluttered. After I get my problem solved, I measure through profiling (dotTrace is a great tool). As a last measure I consider using SP's if speed improvement warrants it. That way I was able to generate code that runs pretty much unchanged with SQL Server, Oracle, Informix, DB2.
ReplyDeleteBut if the only thing you know and love is SQL Server 2005, be my guest, go crazy with stored procedures calling store procedures and have three triggers per table to boot. And perpetuate your job translating them to Oracle or DB2 when necessary.
As long as you don't work for me or with me, that is perfectly fine.
I am interested in knowing about your DAL. Recently inherited an Informix db. Any help? Thanks.
DeleteYep... This is actually a pretty good example of somebody who "doesn't get" the thrust of my point, e.g. "As long as you don't work for me" etc. type comments.
ReplyDeleteI already stated in my post that I am familiar with and have used the database agnostic approach. Maybe 80% of your db activity is CRUD, because you've got it in your mind that's all you can do with a database.
Some more blanket statements for you:
ReplyDeleteSP's are evil
Triggers are evil
MS Enterprise Arch is evil
Now, run!
Peter, it's all a matter of preference as so many things are. I personally have been using a hybrid entity/ADO.NET mixture that works either way and is database agnostic.
ReplyDeleteI've used both pure ORM and raw ADO.NET (or SQL specific libraries) and it all works for most scenarios. The thing to remember is that even if you run into something a library can't do, one way or another you can usually always STILL go down to raw ADO.NET and do what you need to regardless.
As to all the crap that now comes with SQL Server like Service Broker - have you actually looked and played with these horrible, horrible APIs? Nobody but a SQL Admin could possibly think about using an interface that is so cryptic and convoluted... it's almost easier to build your own than use this stuff!
We use SQL Databases because of SQL and not because of all the stuff around it. In the work I do - as the other commenter said - I see upwards of 80% for CRUD so there's no way that I want to hand code that same code over and over and over again. THat may be different for other apps though - obviously if you do business analysis type of apps you'll have totally different needs and you're a lot less likely to use any sort of framework for that.
OTOH, I'm also not shy about using SPs when I need to do complex data manipulation. I think it's more important that a FRAMEWORK supports multiple data back ends, but it's maybe somewhat less important that an APPLICATION supports it. Few applications need to actually port across DB backends and if you're sitting on a specific DB platform surely you'll want to take advantage of some of the more advanced features for performance and more likley for easy of use. And the better frameworks will do this for you as part of the framework actually wrappering such features so they can if necessary be simulated on other DB platforms.
From my point of view a database is a store and nothing else. Additionally it's really hard to distribute data and it's much easier to distribute logic thus I always try to keep my database as simple as possible. Needless to say that refactoring and maintenance of C# code is much easier then T-SQL.
ReplyDeleteAs I've said, I've worked with both modalities and feel comfortable either way. But I have found that specialized kinds of actions like triggers, table variables, and SQLCache dependencies (especially with SQL Server 2005) are things that can really enhance the scalability and business logic of an application, and are extremely difficult or impossible to do with ORM's. As you said, Rick, sometimes the flexibility of a combination of techniques is necessary. But, when you start doing this, you often have to make a portability decision. I've done stuff with SQL Server and CLR-Hosted assemblies / sprocs that really would be impossible with any type of "BLL/DAL/ORM" framework. But then, these were apps where it was so remote that tha app would ever be used with a different brand of database that I had little difficulty justifying the approach that was taken. What I object to is these self-serving "purists" who tell you that you couldn't work for them unless you were willing to do it their way, which is often quite stunted because of the box they've put themselves into.
ReplyDeleteRick - right on the money. DAL is (or at least should be) a framework that isolates your application from the database. It should implement all the best practices regarding database access and take care of the laborious process of converting DB data types to language data types, as well as doing sanity check before sending data to the db (typical case - checking for string lengths so you don't end up with a truncation error).
ReplyDeleteMy own framework supports stored procedures, and for the most part it makes the calling conventions transparent (some need "@" parms, some need : parms, etc). I use them - parcimoniously. Complex data manipulation that my app doesn't need to know about - yes, goes to a stored procedure. That's 20% or less.
The more you use of the "advanced features" the more you are married to that vendor. If that's ok in your company, then so be it. Oracle for example has amazing hierarchical SQL statements and I used it - but still 80% or more goes through my DAL.
I just want a database to be taken for what it is - a persistence mechanism. That's all.
When reading this type of discussion I always wonder why people think the only part of system that gets changed often is the database.
ReplyDeleteIn 13 years of software development I have changed once - Access to SQL Server - and it was easy as almost no code changed as I use errors (Views unh parameters in Access). On the other hard, I have changed from VBA to Perl to VBScript to VB to VB.Net to C#.
Also, changing from Classic Asp to DotNet 1.1 then 2.0 and now adding 3.0 functionality has required serious rewriting. Interestingly, the db part hasn't changed much though. Which is exactly what Codd intended.
BTW, is it hard too change ORMs? What if I use nHibernate and want to swap to LLBLGEN or vice versa? Or maybe I want to move to the Ms Entity thingy next year? These issues are much more relevant than being db agnostic IMHO.
Why code C# in stored procs when you can just keep the logic in your layers. Doesn't make any sense to me to extend procs with the CLR, just makes another "place" for .NET related code and so now you're managing both the proc OOP and the OOP layer coded in your application. To me it just seems even more messier.
ReplyDeleteI think that's a matter of personal choice by the developer. It seems to make the most sense when you want to build a library of utility functions (for example REGEX processing) inside SQL Server.
ReplyDelete