ODBC, OLE DB, ADO

In SSIS, there are a number of types when you select Connction Manager. The following 4 connection types will be found in ODBC, OLE DB, ADO, and. What are these 4 things, what are they related to, what is the difference, how to choose?

This article explains these 4 things about their evolution ...

Evolution History:

Prehistory = ODBC = OLE DB = ADO

Prehistoric

Initially, the relational databases of different vendors had different calling interfaces (APIs), such as the OCI of SQL Server's dblib,oracle. At that time, developers had to use different APIs to access different databases ...

Odbc

In 1992, in order to solve this prehistoric problem, the Microsoft-led industry participants worked out the ODBC (Open database Connectivity), and ODBC was able to handle almost all database systems.

ODBC is an interface developed using standard C to access the database system (DBMS). The ODBC design is designed to be independent of database systems and operating systems, and applications can access data through ODBC regardless of the database used.

Different vendors provide their own drivers for ODBC, and when the database needs to be changed, only the driver can be changed.

DAO

At the end of 1992, DAO1.0 released. A DAO (data access object) that accesses objects. DAO can be used to access the Jet database engine databases (Microsoft access) and to access all ODBC-enabled databases. The first to support Microsoft Access. The final version is DAO3.6 and is no longer supported on 64-bit operating systems and 2013 in Microsoft Access.

RDO

In 1995, Microsoft released the RDO Remote Data object, which was first used for VB applications on Windows 95. RDO is based on ODBC and relies on the ODBC API, the selected ODBC driver, and the backend database engine for most of the functionality. Contains database connections, queries, stored procedures, result actions, and change commits. RDO has a basic ODBC processing method, so you can directly execute most of the ODBC API functions. RDO is derived from DAO, and the biggest difference with DAO is its database processing mode. DAO is for records and fields, and RDO is handled according to rows and columns. That is, DAO is the ISAM mode, and RDO is the relational pattern. In addition DAO is an interface to the Jet engine (Jet is ISAM) that accesses access, and RDO is an interface that accesses ODBC. It can be seen that RDO is a combination of Dao/jet, ODBC and other advantages. RDO final version is 2.0

OLE DB

In 1997, Microsoft released the OLE DB (Object linking and embedding, database) object link embedded in the databases. The generation of OLE DB is known to replace ODBC (also compatible with ODBC).

OLE DB has relationships with data sources through various provider (different data sources correspond to different provider), and OLE DB can access other data, such as text files, e-mail, Excel, etc., in addition to the database system.

The essence of Microsoft UDA (Universal data access) policy is that data can be stored in a variety of places, and access to a variety of data sources can be achieved through unified data access (OLE DB).

Ado

In October 1996, Microsoft released the ADO (ActiveX data Objects) activity data object. ADO is actually a "simplified" version of OLE DB. The ADO connection also encapsulates OLE DB Data source and OLE DB seesion. The ADO command and recordset encapsulate the command and rowset of OLE DB, respectively.

ADO and OLE DB are actually two aspects of a technology. OLE DB provides a low-level connection of data through a COM interface, and ADO provides an object model with more properties and methods to cut the process of retrieving data in OLE DB from the application.

ADO brings simplicity to OLE DB. OLE DB is a call-oriented API that requires a number of different API calls in a specific order to operate directly with OLE DB. And ADO is an object-oriented API, without learning hundreds of API calls, only need to understand how to manipulate a few objects.

Connection, Command, Error, Field, Parameter, property, Record, Recordset, Stream

ado

Microsoft, Microsoft, 2000. NET plan began to take shape, and many Microsoft products were crowned. NET tags, ado+ is no exception, renamed to ADO and wrapped in the. NET Framework class library.

Ado. NET has become part of the. NET Framework Foundation Class library and can access relational, non-relational data. It is an evolution of ADO technology and has now been recognized as a new product/technology.

ODBC, OLE DB, ADO