The ADO.NET Architecture

I l @ ve RuBoard

ADO.NET provides the interface between applications written in Common Language Specification “compliant languages and data sources. These data sources include not only relational databases but other data sources such as the contents of an XML document or information contained in messages held in a Microsoft Exchange information store. In addition, ADO.NET allows you to manage data from multiple sources. For example, you might want to work with data from two different tables held in a SQL Server database in combination with XML data received from a remote component. The functionality provided under ADO.NET allows you to work with these diverse data sources as if they originated from a single source.

The ADO.NET model is based on disconnected access, unlike its predecessor ADO, which provided more limited support for disconnected working. Using the disconnected model, an application creates a connection to a data store and then keeps that connection open only for the time required to update or retrieve data. When the application retrieves data, you can choose whether to create a local cache of any data retrieved or access it in a read-only, forward-only manner. (We'll examine both of these methods later in this chapter.) If you keep a local cache of the data, the data is held in tables, which you can work with in a way that's similar to working with tables in a relational database. If you update the locally cached data, the application can once again create a connection to the data store and persist those changes to the database.

The disconnected data access model offers a number of advantages over the traditional connected data access model. The two primary advantages relate to scalability and the distributed nature of enterprise applications. The first advantage is that connections to a database are held open for a shorter time than in a connected model. Because resources are freed up quickly, they are available to other clients. Hence, more clients can share the same number of database connections, leading to improved application scalability.

The second advantage is that the disconnected model suits the disconnected, heterogeneous nature of enterprise applications. Many corporate applications cross over domain boundaries. In addition, clients on the Web, such as Web browsers, tend to work in a disconnected way to make the best use of available server resources and available bandwidth. The disconnected nature of ADO.NET makes it ideal for use in Web applications built with ASP.NET. ( Chapter 16 explores ASP.NET and illustrates data access over the Web.)

ADO.NET Components

At a high level, the structure of a program that uses ADO.NET to manipulate data in a data store is similar to one that uses Java Database Connectivity (JDBC). Although the precise ordering might differ a little, the following sequence of steps is required:

1. Get a connection to the data store.

2. Create a command that packages an SQL statement or stored procedure.

3. Execute the command and retrieve the result.

4. Close the connection.

However, ADO.NET and JDBC differ at the object level. Figure 7-1 shows the major components of ADO.NET.

Figure 7-1. Major components of ADO.NET

ADO.NET revolves around the System.Data namespace and its four subnamespaces, which together contain the classes and interfaces that allow you to connect to data stores and manage those connections. The four subnamespaces are

• System.Data.Common

  Contains classes that are shared by the .NET data providers

• System.Data.OleDb

  Contains the classes that together allow access to and management of OLE DB data sources

• System.Data.SqlClient

  Contains the classes that allow access to and management of the SQL Server data source

• System.Data.SqlTypes

  Contains classes for native data types for SQL Server

As Figure 7-1 shows, the three main components are the DataSet , the data adapter, and the connection to the data source. The DataSet is an instance of System.Data.DataSet , and it provides a disconnected cache of data that is exposed in a format similar to a relational database. This cache contains tables, columns , and rows that you can navigate and perform operations against. The data adapter acts as a bridge between the DataSet and the actual data source. Because a data adapter must interact with the data source, it provides functionality specific to that data source (in the same way that you have specific JDBC drivers for different data sources). Therefore, a data adapter object will be an instance of a class that implements the System.Data.IDataAdapter interface, which defines the behavior common to all data adapter classes.

Third parties and Microsoft will make a range of adapters available that integrate with Visual Studio .NET, but currently the only adapters that ship with Visual Studio .NET are the System.Data.OleDb.OleDbDataAdapter , which accesses any OLE DB source, and the System.Data.SqlClient.SqlDataAdapter , which accesses SQL Server 7.0 or later.

Note

You can access SQL Server by using the OleDbDataAdapter , but the SqlDataAdapter provides better performance because it is refined for use specifically with SQL Server and it also does not go through the OLE DB layer.

Connections, like data adapters, are specific to a data provider. Data adapter implementations must implement IDataAdapter to provide common access to their underlying data. Similarly, connection objects must implement System.Data.IDbConnection , which provides access to common control functionality for database connections. The two concrete connection classes that ship with Visual Studio .NET are OleDbConnection and SqlConnection .

Before we look at how you implement these components in code, let's take a more detailed look at the structures and roles of the DataSet and the data adapter.

DataSet Objects

As previously mentioned, a DataSet is a cache of data retrieved from one or more data sources. The DataSet is at the heart of the ADO.NET disconnected data access model. When you retrieve data from a data store, it becomes accessible through a table within a DataSet . The DataSet can contain multiple tables, and each of these tables can contain data retrieved from multiple data sources. Once the DataSet holds data for its tables, that data has no physical connection to the underlying data source ”it is completely disconnected. The DataSet exposes an object hierarchy of tables, columns, and rows, which you can use to access and manage the data inside the tables. We'll explore this hierarchy later in the chapter.

Connections and Data Adapters

As noted earlier, a data adapter provides a bridge between a data source and a DataSet . The classes that represent a data adapter must implement the System.Data.IDataAdapter interface. You also learned that two implementations ship with Visual Studio ”one for OLE DB data sources and one for SQL Server. Both implementations belong to their own subnamespaces of the System.Data namespace. Table 7-1 shows the main classes of these namespaces.

Table 7-1. The OleDb and SqlClient Namespaces

System.Data Namespace Interface	OleDb Namespace Implementation	SqlClient Namespace Implementation
IDbCommand	OleDbCommand	SqlCommand
IDbConnection	OleDbConnection	SqlConnection
IDataAdapter	OleDbDataAdapter	SqlDataAdapter
IDataReader	OleDbDataReader	SqlDataReader
IDataParameter	OleDbParameter	SqlParameter

To give you a taste of what functionality these classes expose, we'll discuss the common functionality the interfaces define and then start coding with these classes.

A data adapter requires a connection to connect to a data store; the ADO.NET connection object represents this connection. The connection object implements the IDbConnection interface, through which you can manipulate the connection. The connection object exposes properties that relate the underlying connection to the given data source ”for example, the connection string used when you access the database. The object also exposes methods to open or close a connection and to manage transactions (as explained later in the chapter), and it exposes a method that returns a command object, which allows you to execute SQL statements against a database or invoke stored procedures within a database.

The command object is an instance of a class that implements the IDbCommand interface. This interface exposes properties that allow you to set items such as the command type, command text, and timeouts. The interface also exposes methods that allow you to execute SQL statements or stored procedures against a connection.

Note

The IDbCommand interface exposes a Prepare method, which compiles a statement in advance of execution, like a JDBC PreparedStatement does.

I l @ ve RuBoard