I've had the pain joy opportunity to work with Subsonic, Linq, NHibernate and good ole ADO.  The only one I haven't got to mess with yet is that new MVC thing.  My girlfriend, god love her, asked an amazing question - "What's the difference?  Don't they do the same thing?" Er ...yea I guess they do.  They all write to a database of some kind, somewhere.  So what is the difference?  Well, based on my humble experience, this is my thoughts on each.  Sadly, I don't think there's a clear, big time winner, but there are some that are somewhat better than others.  This'll be a general overview with not a ton of tech detail.

Subsonic is the latest thing I've had to use.  It's very Linq-ish, but not exactly.  Subsonic generates classes for you based on a command line executable and from there, you get your basic goodies, update, save, delete, -destory- and other things.

A nice touch to this is three things, 1, all classes generated are partial classes so extending them is uber easy.  Just keep your namespace the same and you'll have NO problems expanding the class to do other things.  2, overloads of some items, such as "Validate", which by default confines to the table/column definition like "must be varchar, cannot be null". 3, if you have a "IsDeleted" column in your database, or "LastModified" ...when you call <entity>.delete(), IsDeleted goes to true and LastModified gets updated too.  4, the "Query" object is cool, I like it, you write various statements that append to your query for different situations (say, if you get an ID back that's greater than 0, that means you should go try to find it) and just do query.AndWhere("

and this magically paging feature.  My page index is 3 (I'm on page 3) and my page size is 20.  Done and done, no drama (makes repeaters easy to work with in that regard)

Of course, there's a few catches.  Joins are ...a royal pain -- creating views seems to be the best work-around (which are picked up by the code generation) and -any- change to the database -requires- a regeneration of the classes.  So if someone changes a column to a bit field, annddd you run your code without the new bits, kaboom.

Loading data is very easy, <Entity> entity = new <Entity>(Id) along with collections, for example <EntityCollection> collection = new <EntityCollection>.FetchAll() and it does bring back related data (database relationships).  Overall I like it, the learning curve is short and does its job with very few surprises.

Linq I've been using in my chapters in tandom and writing up my own little app using Linq.  Linq is part of the .Net 3 framework meaning "its built in!", no extra downloads but you do have to download the extension for VS2005.  Within Vstudio, "Linq to Sql" and you're well on your way.  Drag/drop the tables you want, save it, you've got your entites.  These are also partial classes, so extending them is easy.

Linq also introduces a new ...thing called "var".  Consider var your container for ANYTHING you'd want from your database.  Syntax goes something ...like this.

Product now becomes like an instant database collection with a bunch of really slick stuff on-tap as shown below

One of the advantages with Linq is its built into .net 3 so additional references are not needed, everything's in the GAC.  Second is that anything returned can be used like a database on tap -- don't like your results?  Need them filtered?  Do it right there on the spot if you want.  I think that's huge and uber flexible.  Another thing is more or less SQL like queries are used to get your data so the structure is familiar (just slightly out of order).  And Jon's a big fan -- that counts for something right?

Disadvantage, like SubSonic, every time you change the database, its time to go regenerate the classes (only this time you get to use a GUI!).  Also misuse of var could get nasty.  I've read a few warnings on this, but we're all smart devs, we'd NEVER do that ...right?  I tried to find the post I was thinking of ...can't find it.  Oh well. 

Part two I'll cover NHibernate and ADO because this got a lot longer than I suspected (2-3 days on and off)