Who should read this article: Beginning .NET programmers, especially those used to
ADO programming, who are confused about the new ADO.NET classes and how they are
to be used
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.
Data 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.
Database Classes
- 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.
