Ted Posts about ways to implement a Data Layer.
I never liked Data Layer generators myself. They never actually did a good job. I fear I've become a sort of "Generator-Bigot" because of my past experiences with them. In my solutions (as well as some other solutions I know of) I use the Microsoft Application Blocks Data Layer Block. It is not a perfect piece of code, since it is fitted for the sqlClicent Namespace, but after refactoring it for an hour, I managed ,without too much fuss, to make it use strictly Interface based approach meaning it can now, beneath, use any compliant data source. (This is something I've learned from Addy Santo's previous team - they use the same type of solution in their data layer)
A great benefit you get with that, is a very handy helper class, that actually does dynamic filling of parameters, but then it goes further - it caches them for re-use. the result is you pay the penalty of round-trip generation only once per stored procedure. the next time you call it - you're all set to go.
As for generating the stored procedures - What I do is create a throwaway project, and start dragging data adapters from the Server explorer onto the design surface. You can then tell the wizard to automatically generate Create,Insert,Update and Delete stored procedures for you. then you just throw away the project, and you get to keep the automatically generated procedures!
The larger up side to not using Automatic generators is that you keep control of what's happening. It scares me (Maybe I shouldn't but I am) to think of using such a generator, and then one day , for some reason (And they occur, don't they..) , I'll need to do something in the data layer, something that is not supported by the generator. Now I'll need to read code generated by a machine, which is not bad by itself, except for that horrible learning curve (That's the good case - where the code is even half-readable).
Perhaps I'm mistaken with the new generation of code generators that's coming out, But if I am - prove me wrong. I'll be the first to use it if I'm convinced (It's not that I'm an early adopter as much as I'm plain lazy! I want something else to do the dirty work for me just like the next guy..)
I can also recommend a good book that depicts building a solid foundation for a 3-tierd application. Although it deals with building a web application - the parts about the Data Layer and the Business layer are by themselves worth more then the cost of the book . Any developer wanting to learn a good way of doing 3-tiers will benefit from reading it . It's called ASP.NET Website Programming: Problem - Design - Solution C# Edition