Data Access Layer

Hi Guys,

I'm looking into new ways for me to handle the data access layer.
The way i used to do it is have a set of classes that reflect the data, and then one or more classes that did the actual database communication and used the data classes for reference.

I find this method a bit tedious when the application gets larger however.

If i look around Google for best practices on this topic, everyone talks about DataSets, but i highly dislike DataSets and find them pretty impractical.

I definitely like having my own custom classes the most, however it hurts me deep when i have to have traits that reflect the database model in my classes. (relational stuff, like FK_OtherTableID).

I know that the entity framework will solve this for us when it's done, but there's one thing i don't like about the entity framework, and that's the inability to separate the mapping completely from the classes you're working with, and only have the XML files to guide the framework.
This means that you have to have specialized classes, with interface implementations and special attributes, which I'm not a fan of either.

I might just be very picky, and need to come down to earth and pick one of the above. Or maybe there are completely other ways to go.

So to round this up, I'm interested in knowing how you all like to do data access.

You can either

1. Use your method where you create your own business classes which is cumbersome
2. Use n-tier datasets which are the most practical, because the system is mature and Visual Studio 2008 has sorted out the major pain points. Look at this http://channel9.msdn.com/Showpost.aspx?postid=340881 note a more rounded version is available at http://msdn2.microsoft.com/en-us/vbasic/bb466226.aspx. Another advantage is you can use the offline synchronisation services, made for datasets, check this http://channel9.msdn.com/Showpost.aspx?postid=347021
3. Use an OR mapping technology like Linq (or Subsonic). Linq is new to .NET 3.5 and there are still a lot of issues/features yet to be added. I would use it for a small project, but for a big one I would go with datasets. A Google search for n-tier Linq will leave you frustrated because no-one has yet quite figured (or shared) an ideal way to use it. The best source I have found is http://www.subsonicproject.com/view/soniccast-6---linqtosql.aspx where there is a pretty good explanation on how you can impelment it, but there are still one or two implementation problems

You're right, DataSets do have potential, but as soon as you don't have a user interface to manipulate the data with, it quickly becomes cumbersome.
Or perhaps I'm just not that experienced with using DataSets? But as far as i know there's no real easy way to change single instances of data via code, since it's all contained within DataTables.

Linq-to-SQL is not really a method i consider, i see it mostly as sort of a technology demo, if anything, i would use it's big brother, the ADO.NET Entity Framework, but as mentioned, it has some disadvantages i dislike, and it's still in beta.

I'm mainly developing windows services, so what I'm most interested in is a strong way to interact with the business logic.