Thanks for the great feedback on my previous article on O/R Mappers. I just wanted to add some more of my 2 cents to the issue of scalability with O/R Mappers (or DataAdapters) compared to using a tasty dose of raw, handcrafted SQL in all its beauty.

Let's assume the following: You have an application which allows you to order stuff. Amongst dozens of other tables, you end up with a table "articles" and a table "inventory". Inventory consists only of two integer columns: articleID and stock. (The reason for splitting the data into two tables is that now, "article" contains only near-static data whereas "inventory" is changed all the time. This allows for better caching, but more importantly, for better transaction locking which in-turn yields higher throughput). One requirement for the application is that it must not allow ordering a greater number of pieces of any article as there are in stock. Let's further simplify the matter and say that every order can only contain a single article.

When writing an application using these tables, you essentially have two different possibilities on how to deal with database access and locking. Let's assume - for the sake of this example - that you are placing an order of 3 pieces of article 42 which originally has 125 pieces in stock:

A) Optimistic concurrency. Your O/R Mapper or DataAdapter [which are, as Frans correctly said, essentially very similar anyway] will load the corresponding entry from "Inventory", check if enough stock is available, update it in-memory and try to write it back using an optimistic concurrency-compatible statement similar to "UPDATE INVENTORY SET STOCK = 123 WHERE ARTICLEID = 42 AND STOCK = 125". This means at least two round trips (one for SELECT, one for UPDATE).

B) Bare Bones SQL the way our fathers did. Your application doesn't even care about loading the inventory table, instead it just sends "UPDATE INVENTORY SET STOCK = STOCK - 3 WHERE STOCK >= 3 AND ARTICLEID = 42" to the database. This automatically ensures that enough stock is available for the given article in just one roundtrip. (Extend this sample as necessary. Either use a stored procedure or create a SQL batch with another SELECT statement returning the available inventory in the same round trip.)

No matter which option you've chosen, you now have to check the return value for your update statement as it will contain the number of affected rows. If this number is zero when using option A (optimistic concurrency), it means that someone else has changed the inventory in the meantime. In the world of O/R Mappers or DataAdapter this means that you have to re-fetch the data, check the inventory level, change the inventory level, and try to update again while hoping that nobody in the meantime changed your data.

With option B however, a simple change in inventory stock level will not result into returning 0 as the count of affected rows. The only time when the affected row counter does not equal 1 is when the "business rule" regarding inventory level has not been satisfied. In addition (when using the stored procedure or the batched SELECT) you will also receive - in the same round trip - the available inventory level to suggest possible corrections to the user. All this is done with fewer round-trips, better performance, less chance to prolong the locking time if other data is changed during the same transaction. Or, more easily, with higher scalability.

So, yes, DataAdapters (and probably O/R Mappers -- even though they still limit the ability to use the best SELECT for any given application) have a place in applications. Whenever you change near-static data, there's few things which make your life easier than using these automated tools. Transaction processing however doesn't really fit that nicely into the strict optimistic concurrency model used with DataSets or the OO model used with O/R Mappers. At least I will keep doing my bare bones SQL the way our fathers did for transactions. No, actually I'll use stored procedures --- but that's a different story.