ADO.NET in a Nutshell

Connected and Disconnected Data

As mentioned earlier, ADO.NET supports two different programming environments: connected and disconnected.

The connected environment provides forward-only, read-only access to data in the data source and the ability to execute commands against the data source. The connected classes provide a common way to work with connected data regardless of the underlying data source. They include Connection, Command, DataReader, Transaction, ParameterCollection, and Parameter classes.

The disconnected environment allows data retrieved from the data source to be manipulated and later reconciled with the data source. The disconnected classes provide a common way to work with disconnected data regardless of the underlying data source. They include the DataSet, DataTable, DataColumn, DataRow, Constraint, DataRelationship, and DataView classes.

Finally, ADO.NET introduces the connected DataAdapter class to bridge the data source and disconnected classes by way of the connected classes. The DataAdapter is an abstraction of the connected classes that simplifies filling the disconnected DataSet or DataTable classes with data from the data source and updating the data source to reflect any changes made to the disconnected data. Figure 1-1 shows the relationship between the connected and disconnected classes in ADO.NET.

Figure 1-1. The connected and disconnected ADO.NET classes

Connected Classes

The following classes are used by ADO.NET to communicate directly with the data source:

Connection

Maintains information required to connect to the data source through a connection string. The connection string contains information such as the name of the data source and its location, and authorization credentials and settings. The Connection class has methods to open and close the connection, for transactions to be initiated on the connection, as well as control other properties of the connection.

Command

Executes SQL statements or stored procedures against the data source. The command class has a ParameterCollection object containing Parameter objects that allow parameterized SQL statements and stored procedures to be used against the data source.

DataReader

Provides connected forward-only, read-only access to the data source. It is optimized for speed. The DataReader is instantiated through a Command object.

Parameter

Allows parameters for both parameterized queries and stored procedures to be defined and set to appropriate values. The Parameter class is accessed through the ParametersCollection object within a Command object. It supports input and output parameters as well as return values from stored procedures.

Transaction

Allows transactions to be created on a connection so that multiple changes to data in a data source are treated as a single unit of work and either all committed or cancelled.

DataAdapter

Bridges the data source and the disconnected DataSet or DataTable classes. The DataAdapter wraps the connected classes to provide this functionality. It provides a method to retrieve data into a disconnected object and a method to reconcile modified data in the disconnected object with the data source. The CommandBuilder class can generate the logic to reconcile changes in simple situations; custom logic can be supplied to deal with complex situations and optimize performance.

Disconnected Classes

The following ADO.NET classes allow data to be retrieved from the data set, examined and modified offline, and reconciled with the data source through the DataAdapter:

DataSet

Provides a consistent way to deal with disconnected data completely independently of the data source. The DataSet is essentially an in-memory relational database, serving as a container for the DataTable, DataColumn, DataRow, Constraint, and DataRelation objects.

The XML format serializes and transports a DataSet. A DataSet can be accessed and manipulated either as XML or through the methods and properties of the DataSet interchangeably; the XmlDataDocument class represents and synchronizes the relational data within a DataSet object with the XML Document Object Model (DOM).

DataTable

Allows disconnected data to be examined and modified through a collection of DataColumn and DataRow classes. The DataTable allows constraints such as foreign keys and unique constraints to be defined using the Constraint class.

DataColumn

Corresponds to a column in a table. The DataColumn class stores metadata about the structure of the column that, together with constraints, defines the schema of the table. The DataColumn can also create expression columns based on other columns in the table.

DataRow

Corresponds to a row in a table and can examine and update data in the DataTable. The DataTable exposes DataRow objects through the DataRowCollection object it contains. The DataRow caches changes made to data contained in its columns, storing both original and current values. This allows changes to be cancelled or to be later reconciled with the data source.

Constraint

Allows constraints to be placed on data stored within a DataTable. Unique and foreign key constraints can be created to maintain data integrity.

DataRelation

Provides a way to indicate a relationship between different DataTable objects within a DataSet. The DataRelation relates columns in the parent and child tables allowing navigation between the parent and child tables and referential integrity to be enforced through cascading updates and deletes.

DataView

Allows data, once retrieved into a DataSet or DataTable, to be viewed in different ways. It allows data to be sorted based on column values and for a subset of the data to be filtered so that only rows matching specified criteria are displayed.

Chapter 2 examines .NET data providers in more detail and describes the way in which they provide connected functionality and serve as a bridge to disconnected functionality.