There are different tools and different patterns.
Tools
ADO.NET
ADO.NET is much more than DataSet
and DataTable
. I would not use those classes in any project but instead use the IDataReader
to populate pocos as I describe here: http://blog.gauffin.org/2013/01/ado-net-the-right-way/
Data mappers
The next step is to use a data mapper. What they do is to let you write SQL statements, but they will automatically return POCOs to you. Either as a dynamic
representation or as T4 generated classes. (You can also map your own classes)
Some examples are Massive, PetaPoco and Simple.Data.
OR/Ms
Object/Relation mappers are the most complex approach (complex as in creating). They take care of everything for you. Most OR/Ms also has a LINQ to SQL provider. That is, you can write LINQ statements which then are converted to SQL.
Entity framework for instance has a visual designer in which you can point at the tables/columns that you want to get classes for. Hence it's quite easy to get started.
Examples: Lightspeed, Entity Framework and nhibernate.
Patterns
Patterns help you structure the code in the data access layer.
Repository pattern
Repository pattern is quite old but still a relevant pattern. The idea is to abstract away the data access to reduce coupling and complexity.
Query object
The query object pattern is used to create a class per query. For instance, it makes it easier to see which parameters are required (constructor arguments) and which are optional.
CQS
Command/Query separation is a way to separate querying (fetching) and commands (storing) information. It's not just a data access pattern but a way to structure your application.
CQRS
CQRS is similar to CQS but takes it a step further. It uses separate models (databases) for reads and writes. The read model is typically generated with the help of events from the writes.
WPF
In WPF you have view models which you use to map the GUI to your data access. You use ObservableCollection
to get a GUI which responds to changes.
In the view model you'll use any of the tools that I described above (directly or through one of the described patterns).
Additional info
I've written a blog post about the data layer here: http://blog.gauffin.org/2013/01/data-layer-the-right-way/ where I discuss the data access.
imho stored procedures are obsolete. Parameterized queries are in many databases as fast as stored procedures.