Appendix A covers some of the basics of databases, such as locking,
SQL is a
This appendix answers some of the commonly asked questions
Activex data objects for the .NET Framework (ADO.NET) is the latest database access technology from Microsoft. ADO.NET addresses issues with previous database access technologies and provides the foundation for future scalability. Although ADO.NET stands for
Active Data Objects .NET
, it's perhaps misnamed because unlike ActiveX Data Objects (ADO), ADO.NET isn't an ActiveX/Component Object Model (COM) technology. As the business world is moving onto the Internet, one of the main goals of ADO.NET is allow developers to write high-performance, reliable, and scalable database applications over the Internet. ADO.NET uses .NET Common Language Runtime (CLR) services to manage the library, and it utilizes Extensible Markup Language (XML) to cache the data and exchange data among applications over the Internet as well as
Our goal in this chapter is simply to provide you with a high-level overview of ADO.NET. In it, we provide the basics of ADO.NET, describe its advantages over current data access technologies, and briefly introduce ADO.NET classes and namespaces as well as show how to use them to write simple database applications using Microsoft Visual Studio .NET (VS .NET). VS .NET provides tremendous support for writing database applications in no time, using its wizards and utilities. (Chapter 2 covers VS .NET and ADO.NET support.) In this chapter, we also
The .NET Base Class Library is also called the .NET Runtime Library or the .NET Framework Class Library .
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
Microsoft Data Access
Data Access Objects (DAO)
Microsoft Foundation Classes (MFC) ODBC and DAO classes
Remote Data Objects (RDO)
Object Linking and Embedding Database (OLE DB)
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.
In early 1988, Microsoft's Applications
By spring 1989, the emerging ODBC specification had gone through several drafts. Geiger and
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
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
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 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
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
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
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 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.
 Karen Watterson, "ODBC," Microsoft SQL Server Connection , July 1992.