I agree that .NET Tiers, with CodeSmith, are an option for most. It does
generate a DAL, including sprocs for the database. It is simple to use for
standard one to one mapping, where each object looks like a table.

I worked with .NET Tiers on one project and I am not sure I would use it
again. Part of the reason is the project's failure to map directly to
tables. This was brought on by differences in modeling between our company
and a vendor that worked on part of the project. If you are in complete
control of your database schema, this may not be an issue. On another
project, we found some scalability issues with .NET Tiers, but we found
similar scalability issues with LINQ on this project. Understand this second
project is a project that watches messages 24/7 and more easily bottlenecks.

Decisions about which OR/M to choose are difficult. nHibernate, and the
like, are extremely good at customization, but have a heavier learning
curve. This learning curve may not be an issue for you.

Products like LLBLGen Pro (MVP Franz Bouma) have better data context, and
great tool support, but are not free and do not generate stored procedures.
Free may not be an issue to you and you may find that streamlined SQL
outside of sprocs works, as well. You have stated it as one of your desires,
however.

I do not consider LINQ a replacement for a good DAL. I am sure some will
disagree with me, but it has some severe limitations when you use it for
data transport. It is an excellent way to work with object collections that
adhere to IEnumerable, however, so I am not saying "do not use LINQ". I can
even see LINQ to SQL as a OR/M like solution, in some instances, so this
might be useful for you.

Entity Objects look like a decent enough OR/M, but you have to determine
when you need your project out, as they will not be ready until later this
year. You also have to ask yourself how good the tool support will be when
released, as Microsoft has been better at pushing out new technology than
providing decent tools to work with it.

There are a variety of OR/M products listed on SharpToolBox. I would do some
research and make a decision:
http://sharptoolbox.com/categories/object-relational-mappers

*************************************************