ADO.NET is the latest in a long line of database-access technologies that began with the Open Database Connectivity (ODBC) API several years ago. Written as a C-style library, ODBC was designed to provide a uniform API to issue SQL calls to various database servers. In the ODBC model, database-specific drivers hide any difference and discrepancy between the SQL language used at the application level and the internal query engine. Next, COM landed in the database territory and started a colonization process that culminated with OLE DB.
OLE DB has evolved from ODBC and, in fact, the open database connectivity principle emerges somewhat intact in it. OLE DB is a COM-based API aimed at building a common layer of code for applications to access any data source that can be exposed as a tabular rowset of data. The OLE DB architecture is composed of two elements a consumer and a provider. The consumer is incorporated in the client and is responsible for setting up COM-based communication with the data provider. The OLE DB data provider, in turn, receives calls from the consumer and executes commands on the data source. Whatever the data format and storage medium are, an OLE DB provider returns data formatted in a tabular layout that is, with rows and columns. OLE DB uses COM to make client applications and data sources to communicate.
Because it isn't especially easy to use and is primarily designed for coding from within C++ applications, OLE DB never captured the hearts of programmers, even though it could guarantee a remarkable mix of performance and flexibility. Next came ADO roughly, a COM automation version of OLE DB just to make the OLE DB technology accessible from Microsoft Visual Basic and classic Active Server Pages (ASP) applications. When used, ADO acts as the real OLE DB consumer embedded in the host applications. ADO was invented in the age of connected, two-tier applications, and the object model design reflects that. ADO makes a point of programming redundancy: it usually provides more than just one way of accomplishing key tasks, and it contains a lot of housekeeping code. For all these reasons, although it's incredibly easy to use, an ADO-based application doesn't perform as efficiently as a pure OLE DB application.
Note
Using ADO in .NET applications is still possible, but for performance and consistency reasons its use should be limited to a few very special cases. For example, ADO is the only way you have to work with server cursors. In addition, ADO provides a schema management API to .NET Framework 1.x applications. On the other hand, ADO recordsets can't be directly bound to ASP.NET or Microsoft Windows Forms data-bound controls. We'll cover ASP.NET data binding in Chapter 9 and Chapter 10. The key improvements in ADO.NET are the rather powerful disconnected model exposed through the DataSet object, the strong integration with XML, and the seamless integration with the rest of the .NET Framework. Additionally, the performance of ADO.NET is very good, and the integration with Microsoft Visual Studio .NET is unprecedented. If you're writing a new application in the .NET Framework, deciding whether to use ADO.NET is a no-brainer.
.NET Managed Data Providers
A key architectural element in the ADO.NET infrastructure is the managed provider, which can be considered the .NET counterpart of the OLE DB provider. A managed data provider enables you to connect to a data source and retrieve and modify data. Compared to the OLE DB provider, a .NET managed provider has a simplified data-access architecture made of a smaller set of interfaces and based on .NET Framework data types.
Building Blocks of a .NET Data Provider
The classes in the managed provider interact with the specific data source and return data to the application using the data types defined in the .NET Framework. The logical components implemented in a managed provider are those graphically featured in Figure 7-1.
Figure 7-1: The .NET Framework classes that form a typical managed provider and their interconnections.
The functionalities supplied by a .NET data provider fall into a couple of categories:
Support for disconnected data that is, the capability of populating ADO.NET container classes with fresh data
Support for connected data access, which includes the capability of setting up a connection and executing a command
Table 7-1 details the principal components of a .NET data provider.
Table 7-1: Principal Components of a .NET Data Provider
Component
Description
Connection
Creates a connection with the specified data source, including Microsoft SQL Server, Oracle, and any data source for which you can indicate either an OLE DB provider or an ODBC driver
Transaction
Represents a transaction to be made in the source database server
Command
Represents a command that hits the underlying database server
Parameter
Represents a parameter you can pass to the command object
DataAdapter
Represents a database command that executes on the specified database server and returns a disconnected set of records
CommandBuilder
Represents a helper object that automatically generates commands and parameters for a DataAdapter
DataReader
Represents a read-only, forward-only cursor created on the underlying database server
Each managed provider that wraps a real-world database server implements all the objects in Table 7-1 in a way that is specific to the data source.
Caution
You won't find any class named Connection in the .NET Framework. You'll find instead several connection-like classes, one for each supported .NET managed provider for example, SqlConnection and OracleConnection. The same holds true for the other objects listed in Table 7-1.
Interfaces of a .NET Data Provider
The components listed in Table 7-1 are implemented based on methods and properties defined by the interfaces you see in Table 7-2.
Table 7-2: Interfaces of .NET Data Providers
Interface
Description
IDbConnection
Represents a unique session with a data source
IDbTransaction
Represents a local, nondistributed transaction
IDbCommand
Represents a command that executes when connected to a data source
IDataParameter
Allows implementation of a parameter to a command
IDataReader
Reads a forward-only, read-only stream of data created after the execution of a command
IDataAdapter
Populates a DataSet object, and resolves changes in the DataSet object back to the data source
IDbDataAdapter
Supplies methods to execute typical operations on relational databases (such as insert, update, select, and delete)
Note that all these interfaces except IDataAdapter are officially considered to be optional. However, any realistic data provider that manages a database server would implement them all.
Note
Individual managed providers are in no way limited to implementing all and only the interfaces listed in Table 7-2. Based on the capabilities of the underlying data source and its own level of abstraction, each managed provider can expose more components. A good example of this is the data provider for Microsoft SQL Server that you get in the .NET Framework 2.0. It adds several additional classes to handle special operations such as bulk copy, data dependency, and connection string building.
Managed Providers vs. OLE DB Providers
OLE DB providers and managed data providers are radically different types of components that share a common goal to provide a unique and uniform programming interface for data access. The differences between OLE DB providers and .NET data providers can be summarized in the following points:
Component technology. OLE DB providers are in-process COM servers that expose a suite of COM interfaces to consumer modules. The dialog between consumers and providers takes place through COM and involves a number of interfaces. A .NET data provider is a suite of managed classes whose overall design looks into one particular data source rather than blinking at an abstract and universal data source, as is the case with OLE DB.
Internal implementation. Both types of providers end up making calls into the datasource programming API. In doing so, though, they provide a dense layer of code that separates the data source from the calling application. Learning from the OLE DB experience, Microsoft designed .NET data providers to be more agile and simple. Fewer interfaces are involved, and the conversation between the caller and the callee is more direct and as informal as possible.
Application integration. Another aspect of .NET that makes the conversation between caller and callee more informal is the fact that managed providers return data using the same data structures that the application would use to store it. In OLE DB, the data-retrieval process is more flexible, but it's also more complex because the provider packs data in flat memory buffers and leaves the consumer responsible for mapping that data into usable data structures.
Calling into an OLE DB provider from within a .NET application is more expensive because of the data conversion necessary to make the transition from the managed environment of the common language runtime (CLR) to the COM world. Calling a COM object from within a .NET application is possible through the COM interop layer, but doing so comes at a cost. In general, to access a data source from within a .NET application, you should always use a managed provider instead of OLE DB providers or ODBC drivers. You should be doing this primarily because of the transition costs, but also because managed providers are normally more modern tools based on an optimized architecture.
Some data sources, though, might not have a .NET data provider available. In these cases, resorting to old-fashioned OLE DB providers or ODBC drivers is a pure necessity. For this reason, the .NET Framework encapsulates in managed wrapper classes the logic needed to call into a COM-style OLE DB provider or a C-style ODBC driver.
Data Sources You Access Through ADO.NET
The .NET data provider is the managed component of choice for database vendors to expose their data in the most effective way. Ideally, each database vendor should provide a .NET-compatible API that is seamlessly callable from within managed applications. Unfortunately, this is not always the case. However, at least for the major database management systems (DBMS), a managed data provider can be obtained from either Microsoft or third-party vendors.
As of version 2.0, the .NET Framework supports the data providers listed in Table 7-3.
Table 7-3: Managed Data Providers in the .NET Framework
Data Source
Namespace
Description
SQL Server
System.Data.SqlClient
Targets various versions of SQL Server, including SQL Server 7.0, SQL Server 2000, and the newest SQL Server 2005
OLE DB providers
System.Data.OleDb
Targets OLE DB providers, including SQLOLEDB, MSDAORA, and the Jet engine
ODBC drivers
System.Data.Odbc
Targets several ODBC drivers, including those for SQL Server, Oracle, and the Jet engine
Oracle
System.Data.OracleClient
Targets Oracle 9i, and supports all of its data types
The OLE DB and ODBC managed providers listed in Table 7-3 are not specific to a physical database server, but rather they serve as a bridge that gives instant access to a large number of existing OLE DB providers and ODBC drivers. When you call into OLE DB providers, your .NET applications jumps out of the managed environment and issues COM calls through the COM interop layer.
Accessing SQL Server
As mentioned, Microsoft supplies a managed provider for SQL Server 7.0 and newer versions. Using the classes contained in this provider is by far the most effective way of accessing SQL Server. Figure 7-2 shows how SQL Server is accessed by .NET and COM clients.
Figure 7-2: Accessing SQL Server by using the managed provider for OLE DB adds overhead because the objects called must pass through the COM interop layer.
A .NET application should always access a SQL Server database using the native data provider. Although it's possible to do so, you should have a good reason to opt for an alternative approach such as using the OLE DB provider for SQL Server (named SQLOLEDB). A possible good reason is the need to use ADO rather than ADO.NET as the data-access library. The SQL Server native provider not only avoids paying the performance tax of going down to COM, but it also implements some small optimizations when preparing the command for SQL Server.
Accessing Oracle Databases
The .NET Framework 1.1 and 2.0 include a managed provider for Oracle databases. The classes are located in the System.Data.OracleClient namespace in the System.Data.OracleClient assembly. Instead of using the managed provider, you can resort to the COM-based OLE DB provider (named MSDAORA) or the ODBC driver. Note, though, that the Microsoft OLE DB provider for Oracle does not support Oracle 9i and its specific data types. In contrast, Oracle 9i data types are fully supported by the .NET managed provider. So by using the .NET component to connect to Oracle, you not only get a performance boost but also increased programming power.
Note
The .NET data provider for Oracle requires Oracle client software (version 8.1.7 or later) to be installed on the system before you can use it to connect to an Oracle data source.
Microsoft is not the only company to develop a .NET data provider for Oracle databases. Data Direct, Core Lab, and Oracle itself also shipped one. Each provider has its own set of features; for example, the Oracle provider (named ODP.NET) has many optimizations for retrieving and manipulating Oracle native types, such as any flavor of large objects (LOBs) and REF cursors. ODP.NET can participate in transactional applications, with the Oracle database acting as the resource manager and the Microsoft Distributed Transaction Coordinator (DTC) coordinating transactions.
Using OLE DB Providers
The .NET data provider for OLE DB providers is a data-access bridge that allows .NET applications to call into data sources for which a COM OLE DB provider exists. While this approach is architecturally less effective than using native providers, it is the only way to access those data sources when no managed providers are available.
The classes in the System.Data.OleDb namespace, though, don't support all types of OLE DB providers and have been optimized to work with only a few of them, as listed in Table 7-4.
Table 7-4: OLE DB Providers Tested
Name
Description
Microsoft.Jet.OLEDB.4.0
The OLE DB provider for the Jet engine implemented in Microsoft Office Access
MSDAORA
The Microsoft OLE DB provider for Oracle 7 that partially supports some features in Oracle 8
SQLOLEDB
The OLE DB provider for SQL Server 6.5 and newer
Table 7-4 does not include all the OLE DB providers that really work through the OLE DB .NET data provider. However, only the components in Table 7-4 are guaranteed to work well in .NET. In particular, the classes in the System.Data.OleDb namespace don't support OLE DB providers that implement any of the OLE DB 2.5 interfaces for semistructured and hierarchical rowsets. This includes the OLE DB providers for Exchange (EXOLEDB) and for Internet Publishing (MSDAIPP).
In general, what really prevents existing OLE DB providers from working properly within the .NET data provider for OLE DB is the set of interfaces they actually implement. Some OLE DB providers for example, those written using the Active Template Library (ATL) or with Visual Basic and the OLE DB Simple Provider Toolkit are likely to miss one or more COM interfaces that the .NET wrapper requires.
Using ODBC Drivers
The .NET data provider for ODBC lets you access ODBC drivers from managed, ADO.NET-driven applications. Although the ODBC .NET data provider is intended to work with all compliant ODBC drivers, it is guaranteed to work well only with the drivers for SQL Server, Oracle, and Jet. Although ODBC might appear to now be an obsolete technology, it is still used in several production environments, and for some vendors it is still the only way to connect to their products.
You can't access an ODBC driver through an OLE DB provider. There's no technical reason behind this limitation it's just a matter of common sense. In fact, calling the MSDASQL OLE DB provider from within a .NET application would drive your client through a double data-access bridge one going from .NET to the OLE DB provider, and one going one level down to the actual ODBC driver.
The Provider Factory Model
Unlike ADO and OLE DB, ADO.NET takes into careful account the particularity of each DBMS and provides a programming model tailor-made for each one. All .NET data providers share a limited set of common features, but each has unique capabilities. The communication between the user code and the DBMS takes place more directly using ADO.NET. This model works better and faster and is probably clearer to most programmers.
But until version 2.0 of the .NET Framework, ADO.NET has one key snag. Developers must know in advance the data source they're going to access. Generic programming that is, programming in which the same code targets different data sources at different times is hard (but not impossible) to do. You can create a generic command object and a generic data reader, but not a generic data adapter and certainly not a generic connection. However, through the IDbConnection interface, you can work with a connection object without knowing the underlying data source. But you can never create a connection object in a weakly typed manner that is, without the help of the new operator.
Instantiating Providers Programmatically
ADO.NET 2.0 enhances the provider architecture and introduces the factory class. Each .NET data provider encompasses a factory class derived from the base class DbProviderFactory. A factory class represents a common entry point for a variety of services specific to the provider. Table 7-5 lists the main methods of a factory class.
Table 7-5: Principal Methods of a Factory Class
Method
Description
CreateCommand
Returns a provider-specific command object
CreateCommandBuilder
Returns a provider-specific command builder object
CreateConnection
Returns a provider-specific connection object
CreateDataAdapter
Returns a provider-specific data adapter object
CreateParameter
Returns a provider-specific parameter object
How do you get the factory of a particular provider? By using a new class, DbProviderFactories, that has a few static methods. The following code demonstrates how to obtain a factory object for the SQL Server provider:
The GetFactory method takes a string that represents the invariant name of the provider. This name is hard-coded for each provider in the configuration file where it is registered. By convention, the provider name equals its unique namespace.
GetFactory enumerates all the registered providers and gets assembly and class name information for the matching invariant name. The factory class is not instantiated directly. Instead, the method uses reflection to retrieve the value of the static Instance property of the factory class. The property returns the instance of the factory class to use. Once you hold a factory object, you can call any of the methods listed earlier in Table 7-5.
The following pseudocode gives an idea of the internal implementation of the CreateConnection method for the SqlClientFactory class the factory class for the SQL Server .NET data provider:
public DbConnection CreateConnection() { return new SqlConnection(); }
Enumerating Installed Data Providers
In the .NET Framework 2.0, you can use all .NET data providers registered in the configuration file. The following excerpt is from the machine.config file:
<system.data> <DbProviderFactories> <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data "/> <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleFactory, System.Data.OracleClient" /> ... </DbProviderFactories> </system.data>
Each provider is characterized by an invariant name, a description, and a type that contains assembly and class information. The GetFactoryClasses method on the DbProviderFactories class returns this information packed in an easy-to-use DataTable object. The following sample page demonstrates how to get a quick list of the installed providers:
Figure 7-3: The list of the installed .NET data providers.
Database-Agnostic Pages
Let's write out some sample code to demonstrate how to craft database-agnostic pages. The sample page will contain three text boxes to collect the name of the provider, connection string, and command text:
protected void RunButton_Click(object sender, EventArgs e) { string provider = ProviderNameBox.Text; string connString = ConnectionStringBox.Text; string commandText = CommandTextBox.Text; // Get the provider DbProviderFactory fact = DbProviderFactories.GetFactory(provider); // Create the connection DbConnection conn = fact.CreateConnection(); conn.ConnectionString = connString; // Create the data adapter DbDataAdapter adapter = fact.CreateDataAdapter(); adapter.SelectCommand = conn.CreateCommand(); adapter.SelectCommand.CommandText = commandText; // Run the query DataTable table = new DataTable(); adapter.Fill(table); // Shows the results Results.DataSource = table; Results.DataBind(); }
By changing the provider name and properly adapting the connection string and command, the same core code can now be used to work on other database servers.
Caution
Nothing presented here is invented; no magic and no tricks apply. This said, though, don't be fooled by the apparent simplicity of this approach. Be aware that in real-world applications data access is normally insulated in the boundaries of the Data Access Layer (DAL) and that practice suggests you have one DAL per supported data source. This is because the complexity of real problems needs to be addressed by getting the most out of each data server. In the end, you need optimized data access code to exploit all the features of the DBMS rather than generic code that you write once and which queries everywhere and everything.
