ADO.NET Overview

 

Chapter 9 - Data Access with .NET
bySimon Robinsonet al.
Wrox Press 2002

  

Like most of the .NET Framework, ADO.NET is more than just a thin veneer over some existing API. The similarity to ADO is in name only - the classes and method of accessing data are completely different.

ADO (Microsoft's ActiveX Data Objects) was 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. A connection would be opened to the database, some data selected into a recordset, consisting of fields, that data would then be manipulated, updated on the server, and the connection would be closed. ADO also introduced the concept of a disconnected recordset, which was used where keeping the connection open for long periods of time was not desirable.

There were several problems that ADO did not address satisfactorily, most notably the unwieldiness (in physical size ) of a disconnected recordset. This support was more necessary than ever with the evolution of " web-centric " computing, so a fresh approach was taken. There are a number of similarities 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 very highly tuned to squeeze maximum performance out of the database. This alone should be reason enough to move.

ADO.NET ships with two database client namespaces - one for SQL Server, the other for databases exposed through an OLE DB interface. If your database of choice has an OLE DB driver, you will be able to easily connect to it from .NET - just use the OLE DB classes and connect through your current database driver.

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:

• System.Data - All generic data access classes

• System.Data.Common - Classes shared (or overridden) by individual data providers

• System.Data.OleDb - OLE DB provider classes

• System.Data.SqlClient - SQL Server provider classes

• System.Data.SqlTypes - SQL Server data types

The main classes in ADO.NET are listed below:

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 are contained in the System.Data namespace:

• DataSet - This object may contain a set of DataTables , can include relationships between these tables, and is designed for disconnected use.

• DataTable - A container of data. A DataTable 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 - Contains the definition of a column, such as the name and data type.

• DataRelation - A link between two DataTables within a DataSet . Used for foreign key and master/detail relationships.

• Constraint - Defines a rule for a DataColumn (or set of data columns ), such as unique values.

These next two classes are to be found in the System.Data.Common namespace:

• 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 above, ADO.NET contains a number of database-specific classes shown below. These classes implement a set of standard interfaces defined within the System.Data namespace, allowing the classes to be used if required in a generic manner. For example, both the SqlConnection and OleDbConnection classes implement the IDbConnection interface.

• SqlCommand , OleDbCommand - A wrapper for SQL statements or stored procedure calls.

• SqlCommandBuilder , OleDbCommandBuilder - A class used to generate SQL commands (such as INSERT , UPDATE, and DELETE statements) from a SELECT statement.

• SqlConnection , OleDbConnection - The connection to the database. Similar to an ADO Connection .

• SqlDataAdapter , OleDbDataAdapter - A class used to hold select, insert, update, and delete commands, which are then used to populate a DataSet and update the Database .

• SqlDataReader , OleDbDataReader - A forward only, connected data reader.

• SqlParameter , OleDbParameter - Defines a parameter to a stored procedure.

• SqlTransaction , OleDbTransaction - A database transaction, wrapped in an object.

The most important new 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 PC 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 recordset, which would permit data to be retrieved from a database, passed to the client for processing, and then reattached to the server. This was often cumbersome to use, as disconnected behavior hadn't been designed in from the start. The ADO.NET classes are different - in all but one case (the Sql / OleDb DataReader ) they are designed for use offline from the database.

The classes and interfaces used for data access in the .NET Framework will be introduced as the chapter continues. I will mainly concentrate on the Sql classes when connecting to the database, because the Framework SDK samples install an MSDE database (SQL Server). In most cases the OleDb classes mimic exactly the Sql code.