ADO.NET: Part 3 – Architecture

Data processing has traditionally relied primarily on a connection-based, two-tier model. As data processing increasingly uses multi-tier architectures, programmers are switching to a disconnected approach to provide better scalability for their applications.

The following diagram illustrates the components of ADO.NET architecture.

XML and ADO.NET

ADO.NET leverages the power of XML to provide disconnected access to data. ADO.NET was designed hand-in-hand with the XML classes in the .NET Framework — both are components of a single architecture.

ADO.NET and the XML classes in the .NET Framework converge in the DataSet object. The DataSet can be populated with data from an XML source, whether it is a file or an XML stream. The DataSet can be written as World Wide Web Consortium (W3C) compliant XML, including its schema as XML Schema definition language (XSD) schema, regardless of the source of the data in the DataSet. Because the native serialization format of the DataSet is XML, it is an excellent medium for moving data between tiers making the DataSet an optimal choice for remoting data and schema context to and from an XML Web service.

The DataSet can also be synchronized with an XmlDataDocument to provide relational and hierarchical access to data in real time.

The ADO.NET Object Model

ADO.NET is designed to help developers build efficient multi-tiered database applications across intranets and the Internet, and the ADO.NET object model provides the means.

 

Figure above shows the classes that comprise the ADO.NET object model. A dotted line separates the object model into two halves. The objects to the left of the line are “connected” objects. These objects communicate directly with your database to manage the connection and transactions as well as to retrieve data from and submit changes to your database. The objects to the right of the line are “disconnected” objects that allow a user to work with data offline.

The objects that comprise the disconnected half of the ADO.NET object model do not communicate directly with the connected objects. This is a major change from previous Microsoft data access object models. In ADO, the Recordset object stores the results of your queries. You can call its Open method to fetch the results of a query and call its Update (or UpdateBatch) method to submit changes stored within the Recordset to your database.

The ADO.NET object model consists of two fundamental components: the DataSet, which is disconnected from the data source and doesn’t need to know where the data it holds came from; and the .NET data provider. The .NET data providers allow us to connect to the data source, and to execute SQL commands against it.

.NET Data Providers

A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in a DataSet in order to be exposed to the user as needed, combined with data from multiple sources, or remoted between tiers. .NET Framework data providers are lightweight, creating a minimal layer between the data source and code, increasing performance without sacrificing functionality.

The following table lists the data providers that are included in the .NET Framework.

.NET Framework data provider	Description
.NET Framework Data Provider for SQL Server	Provides data access for Microsoft SQL Server version 7.0 or later. Uses the System.Data.SqlClient namespace.
.NET Framework Data Provider for OLE DB	For data sources exposed by using OLE DB. Uses the System.Data.OleDb namespace.
.NET Framework Data Provider for ODBC	For data sources exposed by using ODBC. Uses the System.Data.Odbc namespace.
.NET Framework Data Provider for Oracle	For Oracle data sources. The .NET Framework Data Provider for Oracle supports Oracle client software version 8.1.7 and later, and uses the System.Data.OracleClient namespace.
EntityClient Provider	Provides data access for Entity Data Model (EDM) applications. Uses the System.Data.EntityClient namespace.

Core Objects of .NET Framework Data Providers

The following table outlines the four core objects that make up a .NET Framework data provider.

Object	Description
Connection	Establishes a connection to a specific data source. The base class for all Connection objects is the DbConnection class.
Command	Executes a command against a data source. Exposes Parameters and can execute in the scope of a Transaction from a Connection. The base class for all Command objects is the DbCommand class.
DataReader	Reads a forward-only, read-only stream of data from a data source. The base class for all DataReader objects is the DbDataReader class.
DataAdapter	Populates a DataSet and resolves updates with the data source. The base class for all DataAdapter objects is the DbDataAdapter class.

The Connection object represents the physical connection to a data source. Its properties determine the data provider (in the case of the OLE DB Data Provider), the data source and database to which it will connect, and the string to be used during connecting. Its methods are fairly simple: You can open and close the connection, change the database, and manage transactions.

The Command object represents a SQL statement or stored procedure to be executed at the data source. Command objects can be created and executed independently against a Connection object, and they are used by DataAdapter objects to handle communications from a DataSet back to a data source. Command objects can support SQL statements and stored procedures that return single values, one or more sets of rows, or no values at all.

A DataReader is a fast, low-overhead object for obtaining a forward-only, read-only stream of data from a data source. They cannot be created directly in code; they are created only by calling the ExecuteReader method of a Command.

The last main component of the .NET data provider is the DataAdapter. The DataAdapter acts as a bridge between the disconnected DataSet and the data source. It exposes two interfaces; the first of these, IDataAdapter, defines methods for populating a DataSet with data from the data source, and for updating the data source with changes made to the DataSet on the client. The second interface, IDbDataAdapter, defines four properties, each of type IDbCommand. These properties each set or return a command object specifying the command to be executed when the data source is to be queried or updated:

 

In addition to the core classes listed in the table earlier in this document, a .NET Framework data provider also contains the classes listed in the following table.

Object	Description
Transaction	Enlists commands in transactions at the data source. The base class for all Transaction objects is the DbTransaction class. ADO.NET also provides support for transactions using classes in the System.Transactions namespace.
CommandBuilder	A helper object that automatically generates command properties of a DataAdapter or derives parameter information from a stored procedure and populates the Parameters collection of a Command object. The base class for all CommandBuilder objects is the DbCommandBuilder class.
ConnectionStringBuilder	A helper object that provides a simple way to create and manage the contents of connection strings used by the Connection objects. The base class for all ConnectionStringBuilder objects is the DbConnectionStringBuilder class.
Parameter	Defines input, output, and return value parameters for commands and stored procedures. The base class for all Parameter objects is the DbParameter class.
Exception	Returned when an error is encountered at the data source. For an error encountered at the client, .NET Framework data providers throw a .NET Framework exception. The base class for all Exception objects is the DbException class.
Error	Exposes the information from a warning or error returned by a data source.
ClientPermission	Provided for .NET Framework data provider code access security attributes. The base class for all ClientPermission objects is the DBDataPermission class.

.NET Framework Data Provider for SQL Server (SqlClient)

The .NET Framework Data Provider for SQL Server (SqlClient) uses its own protocol to communicate with SQL Server. It is lightweight and performs well because it is optimized to access a SQL Server directly without adding an OLE DB or Open Database Connectivity (ODBC) layer. The following illustration contrasts the .NET Framework Data Provider for SQL Server with the .NET Framework Data Provider for OLE DB. The .NET Framework Data Provider for OLE DB communicates to an OLE DB data source through both the OLE DB Service component, which provides connection pooling, and transaction services, and the OLE DB provider for the data source.

Comparison of the dnprdnshort Data Provider for ssNoVersionr and the dnprdnshort Data Provider for OLE DB

To use the .NET Framework Data Provider for SQL Server, you must have access to SQL Server 7.0 or later versions. The .NET Framework Data Provider for SQL Server classes is located in the System.Data.SqlClient namespace. For earlier versions of SQL Server, use the .NET Framework Data Provider for OLE DB with the SQL Server OLE DB provider System.Data.OleDb.

The .NET Framework Data Provider for SQL Server supports both local and distributed transactions. For distributed transactions, the .NET Framework Data Provider for SQL Server, by default, automatically enlists in a transaction and obtains transaction details from Windows Component Services or System.Transactions.

The following code example shows how to include the System.Data.SqlClient namespace in your applications.

Visual Basic

Imports System.Data.SqlClient

 

C#

using System.Data.SqlClient;

.NET Framework Data Provider for OLE DB

The .NET Framework Data Provider for OLE DB (OleDb) uses native OLE DB through COM interop to enable data access. The .NET Framework Data Provider for OLE DB supports both local and distributed transactions. For distributed transactions, the .NET Framework Data Provider for OLE DB, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services.

The following table shows the providers that have been tested with ADO.NET.

Driver	Provider
SQLOLEDB	Microsoft OLE DB provider for SQL Server
MSDAORA	Microsoft OLE DB provider for Oracle
Microsoft.Jet.OLEDB.4.0	OLE DB provider for Microsoft Jet

The .NET Framework Data Provider for OLE DB does not support OLE DB version 2.5 interfaces. OLE DB Providers that require support for OLE DB 2.5 interfaces will not function correctly with the .NET Framework Data Provider for OLE DB. This includes the Microsoft OLE DB provider for Exchange and the Microsoft OLE DB provider for Internet Publishing.

The .NET Framework Data Provider for OLE DB does not work with the OLE DB provider for ODBC (MSDASQL). To access an ODBC data source using ADO.NET, use the .NET Framework Data Provider for ODBC.

.NET Framework Data Provider for OLE DB classes is located in the System.Data.OleDb namespace. The following code example shows how to include the System.Data.OleDb namespace in your applications.

Visual Basic

Imports System.Data.OleDb

 

C#

using System.Data.OleDb;

.NET Framework Data Provider for ODBC

The .NET Framework Data Provider for ODBC (Odbc) uses the native ODBC Driver Manager (DM) to enable data access. The ODBC data provider supports both local and distributed transactions. For distributed transactions, the ODBC data provider, by default, automatically enlists in a transaction and obtains transaction details from Windows 2000 Component Services.

The following table shows the ODBC drivers tested with ADO.NET.

Driver

SQL Server

Microsoft ODBC for Oracle

Microsoft Access Driver (*.mdb)

.NET Framework Data Provider for ODBC classes is located in the System.Data.Odbc namespace.

The following code example shows how to include the System.Data.Odbc namespace in your applications.

Visual Basic

Imports System.Data.Odbc

 

C#

using System.Data.Odbc;

.NET Framework Data Provider for Oracle

The .NET Framework Data Provider for Oracle (OracleClient) enables data access to Oracle data sources through Oracle client connectivity software. The data provider supports Oracle client software version 8.1.7 or a later version. The data provider supports both local and distributed transactions.

The .NET Framework Data Provider for Oracle requires Oracle client software (version 8.1.7 or a later version) on the system before you can connect to an Oracle data source.

.NET Framework Data Provider for Oracle classes is located in the System.Data.OracleClient namespace and are contained in the System.Data.OracleClient.dll assembly. You must reference both the System.Data.dll and the System.Data.OracleClient.dll when you compile an application that uses the data provider.

The following code example shows how to include the System.Data.OracleClientnamespace in your applications.

Visual Basic

Imports System.Data

Imports System.Data.OracleClient

 

C#

using System.Data;

using System.Data.OracleClient;

Choosing a .NET Framework Data Provider

Depending on the design and data source for your application, your choice of .NET Framework data provider can improve the performance, capability, and integrity of your application. The following table discusses the advantages and limitations of each .NET Framework data provider.

EntityClient Provider

The EntityClient provider is used for accessing data based on an Entity Data Model (EDM). Unlike the other .NET Framework data providers, it does not interact directly with a data source. Instead, it uses Entity SQL to communicate with the underlying data provider.

Provider	Notes
.NET Framework Data Provider for SQL Server	Recommended for middle-tier applications that use Microsoft SQL Server 7.0 or a later version. Recommended for single-tier applications that use Microsoft Database Engine (MSDE) or SQL Server 7.0 or a later version. Recommended over use of the OLE DB provider for SQL Server (SQLOLEDB) with the .NET Framework Data Provider for OLE DB. For SQL Server 6.5 and earlier, you must use the OLE DB provider for SQL Server with the .NET Framework Data Provider for OLE DB.
.NET Framework Data Provider for OLE DB	Recommended for middle-tier applications that use SQL Server 6.5 or earlier. For SQL Server 7.0 or a later version, the .NET Framework Data Provider for SQL Server is recommended. Also recommended for single-tier applications that use Microsoft Access databases. Use of an Access database for a middle-tier application is not recommended.
.NET Framework Data Provider for ODBC	Recommended for middle and single-tier applications that use ODBC data sources.
.NET Framework Data Provider for Oracle	Recommended for middle and single-tier applications that use Oracle data sources.

ADO.NET DataSets

The DataSet object is central to supporting disconnected, distributed data scenarios with ADO.NET. The DataSet is a memory-resident representation of data that provides a consistent relational programming model regardless of the data source. It can be used with multiple and differing data sources, with XML data, or to manage data local to the application. The DataSet represents a complete set of data, including related tables, constraints, and relationships among the tables. The following illustration shows the DataSet object model.

DataSet object model

The DataSet is composed of two primary objects: the DataTableCollection and the DataRelationCollection. The DataTableCollection contains zero or more DataTable objects, which are in turn made up of three collections: Columns, Rows, and Constraints.The DataRelationCollection contains zero or more DataRelations.

The DataTable’s Columns collection defines the columns that compose the DataTable. In addition to ColumnName and DataType properties, a DataColumn’s properties allow you to define such things as whether or not it allows nulls (AllowDBNull), its maximum length (MaxLength), and even an expression that is used to calculate its value (Expression).

The DataTable’s Rows collection, which may be empty, contains the actual data as defined by the Columns collection. For each Row, the DataTable maintains its original, current, and proposed values. As we’ll see, this ability greatly simplifies certain kinds of programming tasks.

The DataTable’s Constraints collection contains zero or more Constraints. Just as in a relational database, Constraints are used to maintain the integrity of the data. ADO.NET supports two types of constraints: ForeignKeyConstraints, which maintain relational integrity (that is, they ensure that a child row cannot be orphaned), and UniqueConstraints, which maintain data integrity (that is, they ensure that duplicate rows cannot be added to the table). In addition, the PrimaryKey property of the DataTable ensures entity integrity (that is, it enforces the uniqueness of each row).

Finally, the DataSet’s DataRelationCollection contains zero or more DataRelations. DataRelations provide a simple programmatic interface for navigating from a master row in one table to the related rows in another. For example, given an Order, a DataRelation allows you to easily extract the related OrderDetails rows.

Note: however, that the DataRelation itself doesn’t enforce relational integrity. A Constraint is used for that.

The methods and objects in a DataSet are consistent with those in the relational database model.

The DataSet can also persist and reload its contents as XML, and its schema as XML schema definition language (XSD) schema.

The DataTableCollection

An ADO.NET DataSet contains a collection of zero or more tables represented by DataTable objects. The DataTableCollection contains all the DataTable objects in a DataSet.

A DataTable is defined in the System.Data namespace and represents a single table of memory-resident data. It contains a collection of columns represented by a DataColumnCollection, and constraints represented by a ConstraintCollection, which together define the schema of the table. A DataTable also contains a collection of rows represented by the DataRowCollection, which contains the data in the table. Along with its current state, a DataRow retains both its current and original versions to identify changes to the values stored in the row.

The DataView Class

A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data-binding applications. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.

The DataRelationCollection

A DataSet contains relationships in its DataRelationCollection object. A relationship, represented by the DataRelation object, associates rows in one DataTable with rows in another DataTable; A relationship is analogous to a join path that might exist between primary and foreign key columns in a relational database. A DataRelation identifies matching columns in two tables of a DataSet.

Relationships enable navigation from one table to another in a DataSet. The essential elements of a DataRelation are the name of the relationship, the name of the tables being related, and the related columns in each table. Relationships can be built with more than one column per table by specifying an array of DataColumn objects as the key columns. When you add a relationship to the DataRelationCollection, you can optionally add a UniqueKeyConstraint and a ForeignKeyConstraint to enforce integrity constraints when changes are made to related column values.

XML

You can fill a DataSet from an XML stream or document. You can use the XML stream or document to supply to the DataSet either data, schema information, or both. The information supplied from the XML stream or document can be combined with existing data or schema information already present in the DataSet.

ExtendedProperties

The DataSet, DataTable, and DataColumn all have an ExtendedProperties property. ExtendedProperties is a PropertyCollection where you can place custom information, such as the SELECT statement that was used to generate the result set, or the time when the data was generated. The ExtendedProperties collection is persisted with the schema information for the DataSet.

LINQ to DataSet

LINQ to DataSet provides language-integrated querying capabilities for disconnected data stored in a DataSet. LINQ to DataSet uses standard LINQ syntax and provides compile-time syntax checking, static typing, and IntelliSense support when you are using the Visual Studio IDE.