When I began learning about the .NET Framework, one thing that confused me was ADO.NET, all the new classes, and what they were all for. When do you use a SqlCommand object? What's the SqlDataAdapter class for? Should I use one or the other or both? It was like spaghetti to me--a tangled mess. One of the presenters at the .NET Developer's Training Tour that I attended in Fall 2001 really knew his stuff and cleared it up for me in a big way.

Basically, there are two sets of classes used for data access, data classes and database classes.

• These are dumb containers for data. They know nothing about getting data from the database.
• DataSet is the primary class. DataTable, DataRelation, DataRow, DataColumn, etc. as well as DataView are all examples as well.

• These are used to read and write data from the data source, e.g. SQL Server.
• These are all back-end specific. For instance, you've got the SqlConnection class for SQL Server, and OleDbConnection for OLE DB data sources--Access, FoxPro and so forth.
• SqlConnection and OleDbConnection primarily just connect to the data source. Very 1:1 with ADO's ADODB.Connection object.
• SqlDataAdapter and OleDbDataAdapter are for retrieving data to populate a DataSet; that's all these classes are for. That's something I'd never been told or read before then.
• SqlCommand and OleDbCommand are for anything else you want to get out of a database, such as a scalar (e.g. an integer from SELECT COUNT(*) FROM MyTable), a DataReader (see below), or nothing (e.g. INSERT/UPDATE/DELETE statements which don't return records).
• SqlDataReader and OleDbDataReader are like very specialized, very small ADODB.Recordset objects. One of the most popular things to do with a Recordset in ADO was loop through all the records one time, or alternatively call GetRows on it. That's what the DataReader classes are for. Once you get your DataReader back, you can bind it to a control (e.g. a grid or drop-down list), or you can step through it just like Recordset.MoveNext and While Not Recordset.EOF.

The basic theme is that instead of having one big class that can do everything (the ADO Recordset object), you have a dozen specialized classes that do one or two things well (DataAdapter and Command for getting data, DataReader for displaying data as-is or stepping through it row by row, etc.).

Charles Carroll has more on the differences in the new ADO.NET method of data access, as well as a lot of simple, useful sample code, here.