ADO.NET Overview

ADO.NET is more than just a thin veneer over some existing API. The similarity to ADO is fairly minimal - the classes and methods of accessing data are completely different.

ADO (ActiveX Data Objects) is a library of COM components that has had many incarnations over the last few years. Currently at version 2.7, ADO consists primarily of the Connection, Command, Recordset, and Field objects. Using ADO, a connection is opened to the database, some data is selected into a record set consisting of fields, that data is then manipulated and updated on the server, and the connection is closed. ADO also introduced a so-called disconnected record set, which is used when keeping the connection open for long periods of time is not desirable.

There were several problems that ADO did not address satisfactorily, most notably the unwieldiness (in physical size) of a disconnected record set. This support was more necessary than ever with the evolution of Web-centric computing, so a fresh approach was required. A number of similarities exist between ADO.NET programming and ADO (not only the name), so upgrading from ADO shouldn’t be too difficult. What’s more, if you’re using SQL Server, there’s a fantastic new set of managed classes that are tuned to squeeze maximum performance out of the database. This alone should be reason enough to migrate to ADO.NET.

ADO.NET ships with four database client namespaces: one for SQL Server, another for Oracle, the third for ODBC data sources, and the fourth for any database exposed through OLE DB. If your database of choice is not SQL Server or Oracle, the OLE DB route should be taken unless you have no other choice than to use ODBC.

Namespaces

All of the examples in this chapter access data in one way or another. The following namespaces expose the classes and interfaces used in .NET data access.

Open table as spreadsheet

Namespace	Brief Description
System.Data	All generic data access classes
System.Data.Common	Classes shared (or overridden) by individual data providers
System.Data.Odbc	ODBC provider classes
System.Data.OleDb	OLE DB provider classes
System.Data.ProviderBase	New base classes and connection factory classes
System.Data.Oracle	Oracle provider classes
System.Data.Sql	New generic interfaces and classes for SQL Server data access
System.Data.SqlClient	SQL Server provider classes
System.Data.SqlTypes	SQL Server data types

The main classes in ADO.NET are listed in the following subsections.

Shared Classes

ADO.NET contains a number of classes that are used regardless of whether you are using the SQL Server classes or the OLE DB classes.

The following classes are contained in the System.Data namespace.

Open table as spreadsheet

Class	Description
DataSet	This object is designed for disconnected use and can contain a set of DataTables and include relationships between these tables.
DataTable	A container of data that consists of one or more DataColumns and, when populated, will have one or more DataRows containing data.
DataRow	A number of values, akin to a row from a database table, or a row from a spreadsheet.
DataColumn	This object contains the definition of a column, such as the name and data type.
DataRelation	A link between two DataTable classes within a DataSet class. Used for foreign key and master/detail relationships.
Constraint	This class defines a rule for a DataColumn class (or set of data columns), such as unique values.

The following classes can be found in the System.Data.Common namespace:

Open table as spreadsheet

Class	Description
DataColumnMapping	Maps the name of a column from the database with the name of a column within a DataTable.
DataTableMapping	Maps a table name from the database to a DataTable within a DataSet.

Database-Specific Classes

In addition to the shared classes introduced in the previous section, ADO.NET contains a number of database-specific classes. These classes implement a set of standard interfaces defined within the System.Data namespace, allowing the classes to be used in a generic manner if necessary. For example, both the SqlConnection and OleDbConnection classes implement the IDbConnection interface.

Open table as spreadsheet

Classes	Description
SqlCommand, OleDbCommand, OracleCommand, and ODBCCommand	Used as wrappers for SQL statements or stored procedure calls. Examples for the SqlCommand class are shown later in the chapter.
SqlCommandBuilder, OleDbCommandBuilder, OracleCommandBuilder, and ODBCCommandBuilder	Used to generate SQL commands (such as INSERT, UPDATE, and DELETE statements) from a SELECT statement.
SqlConnection, OleDbConnection, OracleConnection, and ODBCConnection	Used to connect to the database. Similar to an ADO Connection. Examples are shown later in the chapter.
SqlDataAdapter, OleDbDataAdapter, OracleDataAdapter, and ODBCDataAdapter	Used to hold select, insert, update, and delete commands, which are then used to populate a DataSet and update the database. Examples of the SqlDataAdapter are presented in this chapter.
SqlDataReader, OleDbDataReader, OracleDataReader, and ODBCDataReader	Used as a forward only, connected data reader. Some examples of the SqlDataReader are shown in this chapter.
SqlParameter, OleDbParameter, OracleParameter, and ODBCParameter	Used to define a parameter to a stored procedure. Examples of how to use the SqlParameter class are shown in this chapter.
SqlTransaction, OleDbTransaction, OracleTransaction, and ODBCTransaction	Used for a database transaction, wrapped in an object.

As you can see from the previous list, there are four classes for each type of object - one for each of the providers that are part of .NET version 1.1. In the rest of this chapter, unless otherwise stated, the prefix <provider> is used to indicate that the particular class used is dependent on the database provider in use. With version 2.0 of .NET, the designers have updated the class hierarchy for these classes significantly. In 1.1, all that was common between the various connection classes was the implementation of the IConnection interface. This has changed in .NET 2.0 because now both share a common base class. Similarly the other classes such as Commands, DataAdapters, DataReaders, and so on also share common base classes.

The most important feature of the ADO.NET classes is that they are designed to work in a disconnected manner, which is important in today’s highly Web-centric world. It is now common practice to architect a service (such as an online bookshop) to connect to a server, retrieve some data, and then work on that data on the client before reconnecting and passing the data back for processing. The disconnected nature of ADO.NET enables this type of behavior.

ADO 2.1 introduced the disconnected record set, which would permit data to be retrieved from a database, passed to the client for processing, and then reattached to the server. This used to be cumbersome to use, because disconnected behavior was not part of the original design. The ADO.NET classes are different - in all but one case (the <provider>DataReader) they are designed for use offline from the database.

Tip 

The classes and interfaces used for data access in the.NET Framework are introduced in the course of this chapter. The focus is mainly on the SQL classes used when connecting to the database, because the Framework SDK samples install an MSDE database (SQL Server). In most cases, the OLE DB, Oracle and ODBC classes mimic the SQL code exactly.