Or ORM's Are Not the Whole Answer
OR Some ORM's Are A Better Answer

One point I feel I need to make.  So many people seem to think that at some point an ORM will appear that will just magically work.  It will map all your entities to objects and "just work."

So an endless parade of ORM's continue to lurch forth from the hinterlands. 

I'm amazed that people like Bruce Eckel think that an ORM powerful enough will just solve the problem.

The thinking is usually something like "we can shield the user from that ugly, mean SQL with some nice procedural and markup tricks." 

I've come to believe that anytime you try to hide/wrap/abstract a powerful technology/language (SQL, JavaScript, HTML, etc) you end up with at best a leaky abstraction and at worst a complete mess.

SQL is concise and powerful.  It doesn't need to be hidden.  It's not that hard to understand.  It's at least easier to understand than most of the ORM frameworks.  It's quite ridiculous what kind of code acrobatics you must perform to add parameters to a query. 

One of the biggest problems with an ORM is predicting what kind of code they will generate/execute.  The more considerate frameworks include a logging function of some sort.  This is nice but when I go to look at some code and I find a bunch of ORM garbage code, I find myself wishing for the SQL.

I used to hate embedded SQL, then I realized there really is nothing wrong with it, and it fact it's preferable as I found out what a web page is doing all in one file, without analyzing a (now unsupported) ORM or trying to find a stored procedure in the database.

The stored procedure approach is not that bad, I just find myself shying away from them as code re-use is a myth.  Yes, now I've become a true OOP Heretic.  Code re-use, especially between projects, is a rarity.  And this is the major problem with ORM's, they tend to emphasize the one object for every situation paradigm.

If I have an employee table, naturally I will have an employee object, if I'm an OOP ORM Myrmidon.  So I try to use this Employee object for my employee edit screen, my employee absence report, my employee salary report, my company org chart, etc, etc.  However, I soon find my nice clean ERD and UML diagrams become corrupted by the business needs.  We have to join in extra tables.  Pretty soon we are writing all manner of custom logic in our programming language of choice.  We have to use a little somthing like join_to(tables.employee_benefits).get_by(table.columns.user_name='a%').limit(10).sort_by(table.columns.user_name). 

We are wasting resources by bringing back unneeded columns.  Because the One True Employee contains fields for every situation, we populate the whole object for every request.  We can resort to lazy loading and other performance optimizations for expensive entities. 

The Better Solution

The answer is that there is no answer.  However, I think the frameworks like CoolStorage.NET and the db library in Web.Py have a nice ability to load data from queries into objects.  This takes away about 80 percent of the pain of Database/Object interaction and let's us use queries specific to the Use Case at hand.

I think the best we can do is to use the best tool for the job, in the case of talking to databases that tool is SQL.  We should use SQL and take advantage of automatic mapping. 

Learning SQL one time is much better than learning a new ORM every year.  I feel the same for HTML, JavaScript, CSS and the rest of fundamental technologies of our trade. 

http://www.aspfaqs.com/webtech/042606-1.shtml

One of the best (not to mention easiest) paging methods I've seen.  In fact it requires no temp tables or table variables. 

Paging is something that is so common yet seems to hard to get right and keep performant that it pays to have a nice easy template to start from.  I've posted this mainly for my own benefit so I have a permanant bookmark to this code.

The example uses a stored procedure, but its important to understand that a stored procedure is not required to make this work.

Here's the code:

CREATE PROCEDURE [dbo].[usp_PageResults_NAI]

(
@startRowIndex int,
@maximumRows int
)
AS

DECLARE @first_id int, @startRow int
    
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO