I've been playing around with Paul Wilson's ORMapper (an Object-Relational Mapping tool) for quite some time. I’m currently working on a project that uses the ORMapper, and I think I’m nearly sold on using it full-time. This is awesome stuff, and hope you will take the time to check it out.
As you may have already discovered, you can use CodeSmith to automatically generate most of your basic stored procedures and data access layer. However, the ORMapper gives you enormous power over your data with very little effort. Filtering, sorting, paging, recursive saving entire object graphs (parent + children + children-children, etc.), transactional support, updating only the entities/columns that have changed, and the list goes on. To do all of this with stored procedures alone would result in a vast number of procedures to handle every scenario for every table.
Objects can be retrieved in lazy-load fashion, meaning they aren't retrieved from the database until they are needed. Loaded objects are typically cached internally for faster access. You can change database providers (MS SQL, Oracle, OleDb, ODBC, MySQL, PostgreSQL, and so on) for the ORMapper by simply updating your web.config/app.config. You don't lose ad-hoc control over your database. You can still retrieve DataSets in a database-agnostic way with the help of the built-in QueryHelper. If you still need to use stored procedures for some tasks, the ORMapper provides support for that, too.
I’ve been using Paul Welter’s CodeSmith templates to generate entity classes that use the ORMapper. These entity classes are very easy to work with and provide methods for common DB tasks, such as saving, retrieving, creating, deleting and filtering objects. I highly recommend that you use these templates as your starting point with the ORMapper.
My experience with the ORMapper hasn't been entirely rosy. It's taken some time to get myself in the right "mindset" with regards to how I approach my data access layer. I've also encountered a few growing pains where I wasn't using the ORMapper correctly. As Paul Wilson will readily admit, the documentation for the ORMapper is lacking, to say the least. However, Paul does a fantastic job of answering questions posted to his ORMapper Forum.
Performance and security are valid concerns. There has been a lot of debate over the use of O/R mappers, some reaching near "religious" intensity. I'm not going to rehash the pros/cons here, but I believe that an application developed using an O/R mapper can be made just as secure and perform just as well as an application that uses an all-stored-procedures (or whatever architecture du jour) approach.
Overall, Paul Wilson's ORMapper is a very smart and remarkable tool. It may not have as many features as some of the other O/R mapping tools available for .NET, but at just $50, it is an incredible value. There's also something to be said about Paul's objective to keep it simple. As Paul puts it, it "just works."
Additional reading: