ActiveX Data Object

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. Sometimes it is important to access the data accurately, whereas some other time it is important to access it fast. It goes without saying that the data acess technology should be easy to program and deploy. Also, increasing the number of concurrent users should not be a deterrent to the performance of the system. These technologies are discussed here is brief:

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:

Steps to create a command:

Steps to create and use a RecordSet object:

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.

OLE DB is a set of COM interfaces for encapsulating the various database services. It is designed to provide access to relational and non-relational data sources including mainframe ISAM databases, e-mail, file systems, text files, graphic files etc. regardless of the location of data. Programming OLE DB is complicated and can be avoided by using ADO, which 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. In this article I would show you how to add, list, modify and delete records in an Access database using ADO. The database would contain a table containing three fields, namely Account number, Name of the Account holder and Balance amount. Here is how you should proceed to carry out the operations on this database.

The process of creating components in ATL consists of three steps:

(a) Creating Module:

          To create a module the Developer Studio provides an ATL COM AppWizard. Carry out the following steps:

          (b) Adding Component To The Module

To add component to the module we can use ‘ATL Object Wizard’. Carry out the following steps for adding a component using this wizard:

          (c) Adding Methods To The Component

HRESULT FinalConstruct( )

{

    HRESULT hr ;

    CoInitialize ( NULL ) ;

    hr = m_pconnection.CreateInstance( __uuidof ( Connection ) ) ;

    hr =m_pconnection->Open(_T("Provider=  Microsoft.Jet.OLEDB.3.51; Data        
                                                Source=d:\\table1.mdb"),"","",adOpenUnspecified ) ;

    return S_OK ;

}

HRESULT FinalRelease( )

{

    CoUninitialize( ) ;

    HRESULT hr = m_pconnection->Close( ) ;

}

The FinalConstruct( ) function is called when the component object is being built. In this function we are initializing the COM library, creating a connection object and opening the data source. In the FinalRelease( ) function we are doing the opposite: uninitializing the COM library and closing the connection. For these functions to work, we have to add a private variable m_pconnection of type _RecordsetPtr to the CCustomer class from the class view tab.

[in] int id, [in] BSTR name, [in] int balance

HRESULT DeleteRecord ( [in] int id ) ;

HRESULT UpdateRecord ( [in] int id, [in] BSTR name, [in] int balance ) ;

HRESULT Getrsetbyid ( [in] int accno, [out,retval] IDispatch ** p ) ;

HRESULT Getrsetbysort ( [in] int no, [out,retval] IDispatch ** p );

STDMETHODIMP CCustomer::AddRecord ( int id,

BSTR name, int balance)

{

// TODO: Add your implementation code herereturnS_OK ;

}

_RecordsetPtr recset ;

HRESULT hr ;

CString query ;

query.Format ( "SELECT * FROM bank WHERE id IS NULL" ) ;

CComVariant vNull ;

vNull.vt = VT_ERROR ;

vNull.scode = DISP_E_PARAMNOTFOUND ;

hr = recset.CreateInstance ( __uuidof ( Recordset ) ) ;

if ( SUCCEEDED ( hr ) )

{

    recset -> PutRefActiveConnection ( m_pconnection ) ;

    hr = recset -> Open ( query.operator LPCTSTR( ), vNull,

    adOpenForwardOnly, adLockOptimistic, adCmdText );

    if ( SUCCEEDED ( hr ) )

    {

        COleSafeArray fieldlist ;

        fieldlist.CreateOneDim ( VT_VARIANT, 3 ) ;

        long arrayindex[3] = { 0, 1, 2 } ;

        CComVariant f1 ( "id" ) ;

        CComVariant f2 ( "Name" ) ;

        CComVariant f3 ( "Balance" ) ;

        fieldlist.PutElement ( &arrayindex[0], &f1 ) ;

        fieldlist.PutElement ( &arrayindex[1], &f2 ) ;

        fieldlist.PutElement ( &arrayindex[2], &f3 ) ;

        COleSafeArray valuelist ;

        valuelist.CreateOneDim ( VT_VARIANT, 3 ) ;

        CComVariant v1 ( id ) ;

        CComVariant v2 ( name ) ;

        CComVariant v3 ( balance ) ;

        valuelist.PutElement ( &arrayindex[0], &v1 ) ;

        valuelist.PutElement ( &arrayindex[1], &v2 ) ;

        valuelist.PutElement ( &arrayindex[2], &v3 ) ;

        recset -> AddNew ( fieldlist, valuelist ) ;

        recset -> Close( ) ;
    }

}

 

Here we have created a Recordset object, connected it with the connection object (by calling the function _RecordSet::PutRefActiveConnection( )) and added a record to it by calling the function AddNew( ). The field list and the value list passed to AddNew( ) have been built as safearrays of variants.

STDMETHODIMP CCustomer::DeleteRecord ( int id )
{

    AFX_MANAGE_STATE ( AfxGetStaticModuleState( ) )

    _RecordsetPtr recset ;

    HRESULT hr ;

    CString query ;

    query.Format ( "SELECT * FROM bank WHERE id = %d",id ) ;

    CComVariant vNull ;

    vNull.vt = VT_ERROR ;

    vNull.scode = DISP_E_PARAMNOTFOUND ;

    hr = recset.CreateInstance ( _uuidof ( Recordset ) ) ;

    if ( SUCCEEDED ( hr ) )

    {

        recset->PutRefActiveConnection ( m_pconnection ) ;

        hr = recset -> Open ( query.operator LPCTSTR( ), vNull,

        adOpenForwardOnly, adLockOptimistic, adCmdText );

        if ( !recset -> GetadoEOF( ) )

        {

            recset->Delete ( adAffectCurrent ) ;

            recset->Close( ) ;

        }

    }

    return S_OK ;
}

This function is similar to AddRecord( ) method, except that the query being fired has changed and in place of AddNew( ) we are calling the _RecordSet::Delete( ) function.

STDMETHODIMP CCustomer::UpdateRecord ( int id, BSTR name, int balance )

{

    AFX_MANAGE_STATE ( AfxGetStaticModuleState( ) )

    _RecordsetPtr recset ;

    HRESULT hr ;

    CString query ;

    query.Format ( "SELECT * FROM bank WHERE id = %d", id ) ;

    CComVariant vNull ;

    vNull.vt = VT_ERROR ;

    vNull.scode = DISP_E_PARAMNOTFOUND ;

    hr = recset.CreateInstance ( __uuidof ( Recordset ) ) ;

    if ( SUCCEEDED ( hr ) )

    {

        recset -> PutRefActiveConnection ( m_pconnection ) ;

        hr = recset -> Open ( query.operator LPCTSTR( ), vNull,

        adOpenForwardOnly, adLockOptimistic, adCmdText );

      if ( ! recset -> GetadoEOF( ) )

        {

            CComVariant f1 ( name ) ;

            CComVariant f2 ( balance ) ;

            recset -> PutCollect ( L"Name", &f1 ) ;

            recset -> PutCollect ( L"Balance",&f2 ) ;

           recset -> Update ( vNull, vNull ) ;

            recset -> Close( ) ;

        }

    }

    return S_OK ;

}

STDMETHODIMP Ccustomer :: Getrsetbyid ( int accno,IDispatch **p )

{

    AFX_MANAGE_STATE ( AfxGetStaticModuleState( ) )

    _RecordsetPtr recset ;

   CComVariant v ( 0L ) ;

   CString query ;

    query.Format ( "SELECT * FROM bank where id = %d",accno );

    recset = m_pconnection -> Execute ( query.operator LPCTSTR( ), &v,  

                                               adOptionUnspecified ) ;

    *p = ( IDispatch * ) recset ;

    recset -> AddRef( ) ;

    return S_OK ;

}

Since here we are not required to change the recordset we have not opened it as we did in earlier methods. We have simply obtained the record set pointer by calling _Connection::Execute( ). Note that we must call the function AddRef( ) here, since it is the client that would be using the record set pointer to display the fields of the searched record. If we do not do this, the recordset object would die before the client can use it. That brings us to the last method; the one through which we would sort the records in the database. Here is the code for it.


STDMETHODIMP CCustomer::Getrsetbysort ( int no, IDispatch **p )

{

    AFX_MANAGE_STATE ( AfxGetStaticModuleState( ) )

    _RecordsetPtr recset ;

    CString query ;

    CComVariant v ( 0L ) ;

    if ( no == 0 )

        query.Format ( "SELECT * FROM bank order by id" ) ;

    else

        query.Format ( "SELECT * FROM bank order by Name" ) ;

    recset = m_pconnection -> Execute (

    query.operator LPCTSTR( ), &v, adOptionUnspecified ) ;

    *p = ( Idispatch * ) recset ;

    recset->AddRef( ) ;

    return S_OK;

}

This function is similar to the Getrsetbyid( ) function discussed earlier. Through the same function we are obtaining records sorted either by name or by id.

#include <comdef.h>

#import "C:\Program Files\CommonFiles\System\ado\msado15.dll"

no_namespace rename ( "EOF", "adoEOF" )

Now you can compile the component. Now let us see how to use this component in a client.

We know  how to build ADO components to access records in a data source. Now would build a client which can access the various methods in the component.

The steps to create a COM Client using MFC are as under:


 

             Figure 1.

In addition to the controls shown in Figure 1, add a list view control at the bottom. This control is filled up with data programmatically whenever the user clicks on the ‘List’ button.

to ‘AdoClientDlg.h’.

int m_oper ;

CEdit *name, *acc, *bal ;

# include <comdef.h>

# include "atlbase.h"

# import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename ( "EOF", "adoEOF" )

# import "..\AdoServer\AdoServer.tlb"

using namespace ADOSERVERLib ;

BOOL CAdoClientDlg::OnInitDialog( )

{

    // AppWizard generated code

    // TODO: Add extra initialization here

    CoInitialize ( NULL ) ;

    CLSID clsid ;

    HRESULT hr ;

    hr = CLSIDFromProgID( OLESTR ("AdoServer.Customer" ), &clsid ) ;

    hr = CoCreateInstance ( clsid, NULL, CLSCTX_ALL, __uuidof ( ICustomer ), (void **) &cust ) ;

    acc = ( CEdit * ) GetDlgItem ( IDC_EDIT1 ) ;

    name = ( CEdit * ) GetDlgItem ( IDC_EDIT2 ) ;

    bal = ( CEdit* ) GetDlgItem ( IDC_EDIT3) ;

    // insert columns in the list view control

    m_list.InsertColumn ( 0,"ID",LVCFMT_LEFT,100 ) ;

    m_list.InsertColumn ( 1,"Name",LVCFMT_LEFT,100 ) ;

    m_list.InsertColumn ( 2,"Balance",LVCFMT_LEFT,100 ) ;

    // Don’t show the list view control to begin with

    SetWindowPos ( &wndTop, 50,50,400,235,SWP_SHOWWINDOW ) ;

}

void CAdoClientDlg::OnAdd( )

{

    acc -> EnableWindow ( TRUE ) ;

    name -> EnableWindow ( TRUE ) ;

    bal -> EnableWindow ( TRUE ) ;

    m_commit.EnableWindow ( TRUE ) ;

    m_oper = 1 ;

}

void CAdoClientDlg::OnDelete( )

{

    acc -> EnableWindow ( TRUE ) ;

    m_search.EnableWindow ( TRUE ) ;

    m_oper = 3 ;

}

void CAdoClientDlg::OnUpdate( )

{

    acc -> EnableWindow ( TRUE ) ;

    m_search.EnableWindow ( TRUE ) ;

    m_oper = 2 ;

}

void CAdoClientDlg::OnList( )

{

    SetWindowPos ( &wndTop, 50, 50, 400, 390,

    SWP_SHOWWINDOW ) ;

    m_hide.ShowWindow ( SW_SHOW ) ;

    m_list.DeleteAllItems( ) ;

    UpdateData ( TRUE ) ;

    CComVariant custaccno ;

    CComVariant custname ;

    CComVariant custbal ;

    CString name,acc,bal ;

    int i = 0 ;

    _RecordsetPtr recset ;

    recset = ( _RecordsetPtr ) cust -> Getrsetbysort ( m_rad1 ) ;

    while ( !recset -> adoEOF )

    {

        custaccno = recset -> GetCollect ( L"id" ) ;

        acc.Format ( "%d", custaccno.iVal ) ;

        m_list.InsertItem ( i, acc, i ) ;

        custname = recset -> GetCollect ( L"Name" ) ;

        name = custname.bstrVal ;

        m_list.SetItemText ( i, 1, name ) ;

        custbal = recset -> GetCollect ( L"Balance" ) ;

        bal.Format ( "%d", custbal.iVal ) ;

        m_list.SetItemText ( i, 2, bal ) ;

        recset -> MoveNext( ) ;

        i++ ;

    }

    recset->Close( ) ;

}

void CAdoClientDlg::OnCommit( )

{

    UpdateData ( TRUE ) ;

    switch ( m_oper )

    {

        case 1:

            cust -> AddRecord ( m_id, _bstr_t ( m_name ), m_bal ) ;

            break ;

        case 2:

            cust -> UpdateRecord ( m_id, _bstr_t ( m_name ), m_bal ) ;

            break ;

        case 3:

            cust -> DeleteRecord ( m_id ) ;

            break ;

    }

    m_id = 0 ;

    m_name = "" ;

    m_bal = 0 ;

    acc -> EnableWindow ( FALSE ) ;

    name -> EnableWindow ( FALSE ) ;

    bal -> EnableWindow ( FALSE ) ;

    m_commit.EnableWindow ( FALSE ) ;

    m_search.EnableWindow ( FALSE ) ;

    UpdateData ( FALSE ) ;

}

void CAdoClientDlg::OnSearch( )

{

    UpdateData(TRUE) ;

    CComVariant custaccno ;

    CComVariant custname ;

    CComVariant custbal ;

    _RecordsetPtr recset ;

    recset = ( _RecordsetPtr ) cust -> Getrsetbyid ( m_id ) ;

    if ( ! recset -> adoEOF )

    {

        custaccno = recset -> GetCollect ( L"id" ) ;

        m_id = custaccno.iVal ;

        custname = recset -> GetCollect ( L"Name" ) ;

        m_name = custname.bstrVal ;

        custbal = recset -> GetCollect ( L"Balance" ) ;

        m_bal = custbal.iVal ;

        recset -> Close( ) ;

        UpdateData ( FALSE ) ;

        name -> EnableWindow ( TRUE ) ;

        bal -> EnableWindow ( TRUE ) ;

        m_commit.EnableWindow ( TRUE ) ;

    }

    else

        MessageBox ( "Record not found" ) ;

}

void CAdoClientDlg::Onhide( )

{

    SetWindowPos(&wndTop,50,50,400,230,SWP_SHOWWINDOW) ;

    m_hide.ShowWindow(SW_HIDE) ;

}

BOOL CAdoClientApp::InitInstance( )

{

    // wizard generate code

    CoUninitialize( ) ;

    return FALSE;

}

With that we are through with the creation of the client. Now you can compile and execute the client and check out whether it is able to interact with the methods in ADO component

Download