The central class in ADO .NET is the DataSet. While it is tempting to compare the DataSet to the ADO RecordSet object, it is really a different type of object entirely. The ADO .NET DataSet is really a repository for XML data. ADO .NET has more to do with reading and persisting XML data than it does accessing databases. Instead of its primary function being to access databases, its primary function is to access, store, and save XML documents from many different sources. The database is just one of those sources. Data is persisted as World Wide Web Consortium (W3C)compliant XML, so any consumer complying with the standard, no matter the platform, can use it. The DataSet can be used as a stand-alone object for storing any data you can imagine. It is also designed to be the remoting agent of choice. Since it is designed to read any XML data stream, it uses this method for marshalling data between remote sources and local programs.
The DataSet is composed of a collection of tables, relations, and constraints. Does anyone remember the in-memory database that was supposed to be released with Windows 2000 and then was pulled from the release at the last minute? Well here it is, reincarnated as the DataSet. That's right, the class has all the power you need for an in-memory database, including tables, indexes, foreign keys, and DRI. It can draw its data from a database, or you can use it as a stand-alone in-memory data cache that can be persisted to disk or sent over the network.
The other main classes are for connecting to and running queries on databases. There are two main branches to the class library, the SqlClient classes and the OleDbClient classes. As we said before, SqlClient is optimized for SQL Server 7.0 and later databases. It uses native access and does not require OLEDB. The OleDbClient classes use the OLEDB data providers. They can be used for Oracle, Access, and any other supported database except ODBC . There is a special version of the OLEDB ODBC driver available for download from the Microsoft site. More on that later.
The connection classes provide the means to connect to the database. They also manage transactions, and connection pooling. The various parameters for connecting to a database are set via the ConnectionString property. In previous versions of ADO, most of the ConnectionString values were exposed through properties. For security reasons, ADO .NET only exposes the ConnectionString. We will enumerate the details of the various values of the ConnectionString in the chapter on the Connection object.
The Command class provides methods for storing and executing SQL statements and stored procedures. It has a Parameters collection that is used to pass parameters into and out of stored procedures. The collection can also be used to set the parameters for a parameterized SQL statement. The Data-Adapter class contains four Command classes for selecting, updating, inserting, and deleting data. The class can also be used by itself for non-databound access to the database. Of all the ADO .NET classes, the Command class is the closest to the ADO Command class in functionality and remains essentially unchanged as far as properties and methods go. Of course, it is a .NET Framework class and does not use COM at all. It also has some enhancements over the old ADO Command object.
The DataReader is used to retrieve data. It is used in conjunction with the Command class to execute an SQL Select statement and then access the returned rows. It provides forward-only read-only access to the data. The DataReader is also used by the DataAdapter to pump data into a DataSet when the Fill method of the DataAdapter is called. The DataReader provides high-speed access to data. You can use a DataReader to pull data for web pages that are read-only, reports , graphs or any other application that does not require data binding and only displays data.
The DataAdapter Class
The DataAdapter is used to connect DataSets to databases. It is a go-between class. It is comprised of four Command classes and one DataReader class. The DataAdapter is most useful when using data-bound controls in Windows Forms, but it can also be used to provide an easy way to manage the connection between your application and the underlying database tables, views, and stored procedures. Another way of looking at the DataAdapter class is as a way of containing SQL statements and stored procedures for easy programmatic access. All of the SQL needed to maintain a database entity can be contained in a DataAdapter object. The DataAdapter also uses support classes that will create the SQL statements for updating the database from a predefined Select statement, and will fill the parameter collections of the Command objects contained in the class. There is also a wizard that helps create the DataAdapter and another wizard that will create a DataSet from the Command objects contained within the DataAdapter.