1. CodeSmith is very powerful and very easy. If your project is in rush (around three month?). I suggest you to use CodeSmith. It works well for the MS SQL 2003/2005. I didn't found any big problems. But for some performance issues, I customized it with stored procedure. It is very good for adding data access layer on the existing database ( couple of huge projects used this).
2. Now Spring.NET/NHibernate/RhinoMocks is the best and standard solution for the .Net data access layer development. It makes developer's life so easy. It is very flexible for any kind of databases development.(I used it on Oracle, DB2 and MS SQL)
Thanks.