An application can choose from a variety of data access techniques available for managing and maintaining data. These include Open DataBase Connectivity (ODBC), Data Access Objects (DAO), Remote Data Objects (RDO), ActiveX Data Objects (ADO) and Object Linking and Embedding DataBase (OLE DB). Some of the important factors that govern the choice of these technologies are Functionality, Programming ease, Deployment issues and Performance.
Open DataBase Connectivity (ODBC): is an API that provides access to a relational database source. ODBC is used to connect and send SQL statements to the source and retrieve results from the data source. ODBC permits the application to interact with different RDBMS sources using the common code.
Data Access Object(DAO): is a data access technology primarily designed to use the Jet databases like Microsoft Access or ISAM databases like dBase, FoxPro, etc
Remote Data Objects (RDO): is a thin layer over ODBC API and provides functionality for connection to the database, creating complex result sets, cursors, and executing complex stored procedures over the network with high speed and flexibility. RDO was created to access data sources like SQL Server and Oracle using ODBC.
OLE DB:Today, applications run on variety of platforms like desktops, mainframes and Internet. The data that the applications access is available in a wide variety of data sources like spreadsheets, Email, personal databases etc. Technologies like ODBC, DAO and RDO were not able to access data from all possible data sources. To tackle this situation Microsoft developed Universal Data Access Strategy (UDA). This strategy is based on COM. OLE DB is a set of COM interfaces based on UDA for encapsulating the various database services. It is designed to provide access relational and non-relational data source including mainframe ISAM databases, e-mail, file systems, text files, graphic files etc. regardless of the location of data.
The OLE DB architecture consists of Data Consumers, Data Providers and Server Components. The consumer is a software component that uses an OLE DB interface. Development tools like Power builder, Delphi and languages like Visual Basic, Visual C++ are examples of Consumers.
A Provider is a software component that exposes an OLE DB interface. A provider can either be a Data Provider or a Service Provider. A data provider is a component that exposes its data in a uniform tabular form called rowsets. RDBMS, E-mail, ISAM databases etc, are examples of data providers. A data provider owns the data it exposes. A Service Provider is a component that encapsulates services like the query processor and cursor engine. Service Providers do not own the data.
ActiveX Data Objects (ADO): DAO was optimized to access Microsoft Access Database using the jet database engine. RDO was created to access data sources like SQL Server and Oracle using ODBC. ActiveX Data Object (ADO) is a successor to the DAO and RDO object. ADO combines the best features of DAO and RDO. ADO uses the OLE DB technology to gain access to any type of data source.
Though OLE DB is a powerful interface for accessing and manipulating data, programming directly with OLE DB is complicated. This is where ADO comes in. It provides a high level, object-oriented interface to OLE DB.
The ADO object model is based on three types of objects namely Connection, Command and Recordset. The connection object holds data source connection information like data source name, its location, the user id and password, the name of the OLE DB provider, etc. The command object is used to execute SQL commands, queries, stored procedures etc. The Recordset object holds the results returned by the queries. Given below are the steps involved in using these three objects.
Steps to connect to a data source:
Declare a connection object pointer
ConnectionPtr p ;
Create the Connection object
p.CreateInstance ( __uuiof ( Connection ) ) ;
Open the data source
p->Open ( data provider, data source name, user name, password ) ;
Close the connection
p->Close( ) ;
Steps to create a command:
Declare a Command object pointer
_CommandPtr p1 ;
Create the Command object
p1.CreateInstance ( __uuidof ( Command ) ) ;
Construct the command to be executed
p1->CommandText = " …. Actual command …"
Specify the type of the command
p1->CommandType = adCmdText ;
CommandType is a property that can take other values likeAdCmdTable, AdCmdStoredProc, AdCmdUnknown.
Submit the command for execution
p1 -> Execute( ) ;
Steps to create and use a RecordSet object:
Declare a Recordset pointer
_RecordsetPtr p2 ;
Create a Recordset object
p2.CeateInstance ( __uuidof ( recordset ) ) ;
Construct a command for execution
p2 -> CommandText = "… Actual command …." ;
Collect the results of the executed command
p2->Execute( ) ;
Close the Recordset
p2->Close( ) ;
The Recordset object implements several methods that permit the user to update the database and to move the record pointer. It also implements properties that hold the number of records, cursor type, lock type etc.