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

• Select 'New' from the 'File' menu.
• Select 'ATL COM AppWizard' as the project. Type 'AdoServer' as the project name and click 'OK' to continue.
• Select type of module as 'Dynamic Link Library', click 'Finish'.

(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:

• Select 'Insert | New ATL Object' menu item. This would display the 'ATL Object Wizard'
• Select 'Simple Object' from the various object categories and click on 'Next'.
• A 'ATL Object Wizard Properties' dialog is displayed.
• Enter the 'Short Name' as 'Customer'. As soon as you do this all other edit controls would be filled automatically. Click on OK.

• The component that has been added does not contain any functionality. To provide functionality we should add five methods namely, AddRecord( ), UpdateRecord( ),DeleteRec-ord( ), Getrsetbyid( ) and Getrsetbysort( ). Before that we should add the following two function to the 'Customer.h' file.

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.

• Switch to class view tab. Select the interface 'ICustomer' and click the right mouse button. From the menu that pops up, select 'Add Method'.
• In the 'Add Method to Interface' dialog specify the method name as 'AddRecord' and fill the parameters edit control with

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

• Click on 'OK'.
• Similarly add the following methods to the IDL file.

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 );

• Adding the AddRecord( ) method creates a function definition in 'Customer.cpp' as shown below:

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

{

// TODO: Add your implementation code here

returnS_OK ;

}

• Add the following code to the AddRecord( ) method by double clicking on this method from the Class view tab:

_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_V ARIANT, 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.

• Add a method called DeleteRecord( ) and add the following code to it

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 ;

}

• Now add the method UpdateRecord( ) containing the following code:

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 ;

}

• Now we must add the method that searches for a specific record. Here it is...

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.

• In the 'stdafx.h' file add the following statements:

#include <comdef.h>

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