How to Learn Visual C++ : VC++ Tutorial: 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.

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 like AdCmdTable, 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.

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 parametersedit 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 herereturnS_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_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.

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

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.

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

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

Create a dialog-based project using AppWizard (EXE).

Add controls to the dialog box as shown in Figure 1.

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.

Using ClassWizard add three variables m_id, m_name and m_bal of type integer, string and integer respectively for the edit controls in the dialog box.

Similarly using ClassWizard add member variables m_list, m_rad, of type CListCtrl and CButton for the list view control and radio button in the dialog box respectively.

Similarly using ClassWizard aqdd member variables m_hide, m_commit, and m_search, all of type CButton for the ‘Hide’, ‘Commit’ and ‘Search’ buttons respectively.

From the Class view tab add the following member variables

to ‘AdoClientDlg.h’.

int m_oper ;

CEdit *name, *acc, *bal ;

Import the server’s Type Library into the client. This is done by adding the following statements to the file ‘StdAfx.h’.

# include

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

Add an interface pointer cust of the type ICustomer * to ‘CAdoClientDlg.h’ from the class view tab.

Add the regulation code in the OnInitDialog( ) function as shown below:

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

}

Add handlers for the six buttons add, delete, update, commit, list and hide and add the code shown below to them.

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

}

Uninitialize the COM library by the function CoUninitialize( ) at the end of InitInstance( ) function as shown below:

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

How to Learn Visual C++ : VC++ Tutorial

ActiveX Data Object

No comments:

Infolinks In Text Ads

About Me