There are numerous ways to connect and interact with the DB layer. In Java, for example, common usages are JDBC calls of raw SQL, object relational mappers, JDBCTemplate (Spring), stored procedures, etc.
In your language, which option is your preference and why? When would you consider the others?
I prefer to build a business object model layer (objects and collecitons of objects).
I build into each object/collection the ability to interact with the DB (SQL I use System.Data.SqlClient). I've used this pattern for SQL, MySql and Oracle.
Then I interact with the objects from my app code.
By abstracting my DB into objects, my app code is consistent regardless of the backend DB.
I really like the 3 + 1 tier way of doing things.
One tier for UI, one for business logic, and for persisting data. The last one you say? Domain objects and interfaces.This makes it possible to load up any one or two of the main tiers plus the domain "tier", and the code should work.
It relies heavily on dependency injection and Inversion of Control principles.
The data/persistence tier does only two things. It Creates,Reads,Updates and Deletes data, and maps it to the domain object format.
The UI tier does the exact opposite. It displays and receives data in a way that the user can relate to, and maps that output/input to and from the domain object format.
The business logic tier just need to know one thing. Business logic. It does not care about where the data is from, and it does not care about where the data tier is putting it. It knows that it should flag an account that was just overdrawn, how to physically do that is not part of its job really.
The domain objects themselves does not have any logic, they are just containers for passing data between the tiers. This means that you can load the domain objects and interfaces without having to think at all about dependencies.
In the end of the day I feel that I have a pretty clear code base with clearly separated tiers. And with some strict interfaces and good base classes most of the coding is just telling the software what to do then X happens. Just how it is supposed to be.
</rant>
Edit:
Oh, yeah. This is true for both the linq and SubSonic/Other ORMs.
ORM is indeed fantastic.
I use SQL Alchemy when working within python - it works with just about every DBMS I've run across.
For lightweight data-driven applications on MacOS X, I use Core Data, which has a great data modelling tool accessible via Xcode.
Both of these show that ORM done right is excellent. I've had less success and enjoyment with EJB.
I haven't gotten into the LINQ world yet, but I've really come to love the DataTable/TableAdapter classes that Visual Studio has done by means of an XSD dataset. By means of a few drags and clicks after creating my database schema, I now have a DataSet/DataTable object that is strongly typed and I have adapter methods that are using parametrized queries to my stored procedures for all my CRUD statements. It'll even create query table adapters for some of those procedures that aren't directly tied to a table.
Oh, and if you haven't created the stored procedures yet and just have the tables, the wizard will create the procedures or the adhoc SQL statements for you.
This has been out since Visual Studio 2005 and has drastically cut down on my "structure" time with my new web apps and I can focus more on the business and presentation logic.
in c# I love Linq-to-sql for anything new, but I really like using nettiers + codesmith to get a quick a dirty datalayer to the database happening if I'm using c# on .net 2.0
Rails ActiveRecord wipes the floor with everything else I've seen so far. Linq looks like it might be better in some cases, but ActiveRecord is just so flexible
I like Hibernate a lot :)
I know it has a learning curve, but once you've mastered it, its quite nice...
Needless to say, I can't wait to get my hands on the new Entity Framework in .NET 3.5 SP1.. (I know its already available, but I am a bit lazy to type xml :) )
ActiveRecord, which is a pattern documented first (I think) in Fowler's Patterns of Enterprise Architecture. I believe it's implemented in languages other than Ruby, although it's well-known as a core technology in Rails. Whatever, it's a neat abstraction of the database, although I have to confess that I find it a bit clunky and in the findbysql area. But that may just be me.
But (putting on Grumpy Old Man hat now) all the ORMs in the world are no substitute for a good knowledge of SQL, without which I really don't like to see access to an RDBMS being permitted at all.
Hi
We are currently using ODAC to talk to the ORACLE DB and use a lot of ORACLE Packages (PL/SQL). The n-tier is done through RemObjects which means our client has no SQL in it whatsoever and only needs the ability to send HTTP requests so no installation overhead.
All this is done using Borland DELPHI and has been woking for 2 years in a production environment.
We use a mixed approach, depending on what will suit for the particular situation within the application:
This is with java/Oracle.
May favorite way is to use Smalltalk with a GemStone Object Repository. Why? No ORM problem to deal with. I would only consider something else if forced or threatened by my employer.
My favourite way is to have an object abstraction layer. Ideally, this is only place that works with SQL. But in practice, the objects sometimes need to do SQL-y things, too. But nothing outside the object.
So far, I've written such layers myself because what was available was too awkward, too slow or too big.