> SQL should only live within those functions that provide a logical interface to the DB. Its called TAPI or XAPI (transaction API). Think of it as your own domain specific ORM.

Which is generally repetitive and full of duplication, exactly the thing that drives people to use ORM's in the first place. It doesn't matter that it's isolated into a separate layer, it's still more manual work than an ORM.

It is more work than ORM, but if efficient access to data is an important part of your application, it seems to be extra work that brings benefits. At least that has been my experience.

I'm not clear on why this layer has to be repetitive and full of duplication.

> I'm not clear on why this layer has to be repetitive and full of duplication.

Let's say you have a database with 100 tables in it. How many insert statements might there be, just a rough estimate, in your layer of functions? My ORM has 1. At the most basic level, a simple ActiveRecord ORM has 1 insert, 1 update, 1 detele, and 1 select statement. How many are in your typical app? If it's more than 1 of each, then it's duplication.

I haven't written an insert/update/delete statement in years because I don't need to. When I need a fancy select that's an aggregate of several tables, I write a view and then map it into a list of objects with the ORM. This is virtually no work, I spend all of my time working on the actual problem at hand and virtually none marshaling data in or out of the database.

I'm totally not following here...

How can multiple insert statements be considered duplication if they are inserting different things to different tables? My apps have multiple insert statements. They also have multiple calls to "print". Do you count that as a duplication as well?

We can continue the discussion by email if you prefer. It is interesting to me, but we may be getting off topic.

> How can multiple insert statements be considered duplication if they are inserting different things to different tables?

Because they're all the same pattern of code. Whenever you see common patterns, you're missing an abstraction that can greatly simplify things.

Why would I write 100 different insert functions when I can write one that generates itself dynamically using reflection over whatever object it is given? You only need one insert statement, it just needs to be smarter rather than static hand written code.

You need only tag your objects with a little bit of metadata using attributes to give a generic insert/update/select/delete everything it needs to dynamically generate parameterized statements on the fly for every table in the db that you use. Put simply, the only thing I need to do to use a User table in an app is write something like the the following...

    class User : ActiveRecord<User> {
        [PersistentString(MaxLength=50, Required=true)]
        public string UserName;

        [PersistentString(MaxLength=50, Required=true)]
        public string Password;

        [PersistentString(MaxLength=5000)]
        public string About;

        public User(){}
    }

And I'm done, I can now Find, FindAll, Save, Delete, Count, Sum, Min, Max, Avg, Count, Exists, Not Exists, etc... on those records without writing a line of SQL. All of these methods are inherited from the superclass and work on the table User. The same thing applies to view and stored procedures.