Overview of Microsoft Data Access Technologies

To understand the importance of ADO.NET, it's useful to take a quick look at existing Microsoft database access technologies before ADO.NET.

Since the first release of Open Database Connectivity (ODBC) to access databases for Windows applications, Microsoft has been introducing and improving ways to make database programmers' lives easier. In this series of database technologies, it introduced many new technologies and improved existing technologies. Each of these technologies has its own pros and cons. Some of the technologies are specific to a particular kind of data source, and some of them are common to all kinds of data sources. Together these technologies are called Microsoft Data Access Technologies or Microsoft Data Access Components (MDAC). These technologies include the following:

• ODBC

• Data Access Objects (DAO)

• Microsoft Foundation Classes (MFC) ODBC and DAO classes

• Remote Data Objects (RDO)

• Object Linking and Embedding Database (OLE DB)

• ADO

ODBC

ODBC was the first formal database access technology introduced by Microsoft. ODBC provides a low-level C/C++ Application Programming Interface (API) to retrieve data from relational databases such as SQL Server, Access, or Oracle by using ODBC drivers and ODBC Administration. With the help of ODBC drivers and the ODBC Administration utility, you can connect to any ODBC-compliant data source using the ODBC API. However, ODBC is relational in nature, so it's difficult to use ODBC to communicate with nonrelational data sources such as object databases, network directory services, email stores, and so on.

Figure 1-1 shows ODBC connectivity to a database through an ODBC driver and ODBC Administration, also called the ODBC driver manager.

Figure 1-1: ODBC architecture

Using ODBC functions isn't a fun way of programming for high-level programmers. ODBC is a hierarchical model, which means you have to create environment and database objects before creating a recordset object with which you can start accessing the data. Other problems with ODBC are multiple connections and multiple data sources. An ODBC connection doesn't allow you to connect to two data sources using the same connection. You need to use two separate connection objects to do so. Being non-object-oriented in nature and having poor memory management are other drawbacks of ODBC.

For the History Buffs: How ODBC Came to Be ^[1]

In early 1988, Microsoft's Applications Group realized it had a problem. Microsoft had a whole slew of applications that needed to share data. Enter Kyle Geiger. Geiger, fresh from eight years of experience at Wang Laboratories—primarily in PC databases—recalls that he was given this sort of ill-defined problem and told to go "think about it for awhile." Well, Geiger thought about it for awhile and restated the problem as one of delivering data "from anywhere" into Microsoft applications, producing a first specification by spring 1988.

By spring 1989, the emerging ODBC specification had gone through several drafts. Geiger and members of Microsoft's Applications and Systems groups wrestled with how much reliance they should put on the just-inherited Sybase DB-Library API. Concurrently, Geiger heard about what was eventually to become the SQL Access Group (SAG). Then came three grueling years of consensus building as a member of the SAG. The culmination of the first phase was the March 1992 ODBC Developers Conference where the ODBC 1.0 Software Development Kit (SDK) was demonstrated and distributed.

The design goals of ODBC were to provide location, a Database Management System (DBMS), and particular ODBC driver transparency; to provide SQL as a language for universal data access, even to non-SQL data sources; to leverage existing and evolving standards; and to provide tools for easy ODBC setup and administration.

Although ODBC was designed as a Windows-specific API, because it was built on top of the SAG's Call Level Interface specification, the potential for cross-platform, ODBC-compliant APIs was always present. In fact, in 1994, ODBC links to both the Unix and Macintosh worlds (via Apple Data Access Language, or DAL) began to appear.

Meanwhile, Microsoft revised the ODBC specification and introduced it as the ODBC 2.0 SDK. The main difference between ODBC 1.0 and ODBC 2.0 was new 32-bit support and enhanced functionality for the so-called "navigational" model. The latter essentially maps SQL functionality to non-SQL databases and data sources including xBASE and Paradox.

DAO

DAO is a set of COM interfaces for accessing databases; it uses the Microsoft Jet database engine to connect. DAO is best suited for accessing desktop databases including Access, FoxPro, and SQL Server because it can directly connect to these databases through the Jet database engine. Figure 1-2 shows the connectivity between a client application, an Access database, and ODBC data sources using DAO.

Figure 1-2: DAO model

DAO is easier to use than the ODBC API, but it doesn't provide the degree of low-level control and flexibility afforded by the ODBC API. Therefore, you could classify DAO as a high-level database interface. To access non-Access databases, DAO uses ODBC internally, which adds one more layer of API to access ODBC data sources. This degrades the performance of DAO while accessing ODBC data sources.

MFC ODBC and DAO Classes

MFC is a set of C++ classes that provides DAO and ODBC classes to access databases that use DAO and ODBC. These classes provide a high-level programming model for Visual C++ developers working with DAO and ODBC. These classes are a nice wrapper around DAO and ODBC APIs. A drawback of these classes is that they aren't flexible enough. Also, they are slow in performance because of too many layers.

RDO

RDO is an object interface for ODBC that provides an OLE layer on the top of the ODBC API. The main advantage of RDO is that it gives developers an object interface for ODBC developers, but the disadvantage is that it's slow in performance because of too many layers. It also suffers the ODBC limitation of not being well suited for nonrelational data sources.

OLE DB

OLE DB was designed to access all kinds of data including relational, nonrelational, text data, graphical data, and directory services. OLE DB components consist of data providers, which expose their data; data consumers, which use data; and service components, which process and transport data (for example, query processors and cursor engines). OLE DB is a low-level programming model and provides the most flexibility and control over data. Figure 1-3 shows the OLE DB model.

Figure 1-3: OLE DB model

As you can see from Figure 1-3, a client application accesses a database through an OLE DB provider and consumers. You must have an OLE DB provider installed to use OLE DB in your application; database venders provide this.

ADO

ActiveX Data Objects (ADO) is a set of ActiveX components that provide object-based programmatic access to OLE DB. ADO is a COM wrapper around OLE DB libraries. ADO is much simpler to use than OLE DB; you can classify it as a high-level database interface.

In comparison to other data access technologies, ADO provides many benefits. ADO is easy to program and, with a few exceptions, is faster than ODBC and DAO. ADO also provides more flexibility and control over data. You don't have to go through a hierarchy of objects to access data as you do in DAO or ODBC.

ADO (OLE DB) is a set of COM interfaces. This means ADO provides a binary standard to access any kind of data source. You can use ADO with any language that supports COM, such as Visual Basic (VB) or C++. ADO also supports XML data access with the help of Microsoft XML (MSXML).

ADO.NET

ADO.NET is an evolution of ADO. The ADO.NET and XML .NET APIs construct a uniform model to work with XML data from any type of data store. The ADO.NET API is managed code, which means you get all the advantages of .NET, including automatic memory management and garbage collection. Unlike DAO or ODBC, though, you don't have to install new libraries to make an ADO.NET program work if you are already running .NET applications on a machine. We discuss ADO.NET in more detail in the following section.

^[1]Karen Watterson, "ODBC," Microsoft SQL Server Connection, July 1992.