Connecting to a database from C++ using ADO

• Download DatabaseAdo.zip-55.85KB

What is ADO?

Microsoft's ActiveX Data Objects (ADO) is a set of Component Object Model (COM) objects for accessing data sources. It provides a layer between programming languages and OLE DB, an easy way to data access and manipulation that is independent of data stores, tools, and languages.

Adding ADO Support to a C++ Program

The best way to use ADO in C++ is to import ADO type libraries. The list of type libraries available can be found in most cases at the following path:

 C:\Program Files\Common Files\System\ado 

On my system the list includes the following type libraries:

• msado20.tlb
• msado21.tlb
  
• msado25.tlb
  
• msado26.tlb
  
• msado27.tlb

I this tutorial I had decided to use the last version of ADO type library (msado27.tlb).
You can import this type library by using the #import directive:

#import "c:\program files\common files\system\ado\msado27.tlb" 
no_namespace rename( "EOF", "A_EOF" )

If we don't specify the no_namespace option, the directive generates a type library header (msado27.tlh) that contains typedef declarations, smart pointers for interfaces, and enumerators under the ADODB namespace.

rename( "EOF", "A_EOF" ) tells the preprocessor to replace the EOF constants by A_EOF.

Creating a database connection

Before using any ADO objects we should initialize the COM library by calling CoInitialize or CoInitializeEx:

HRESULT hr = ::CoInitialize(NULL);

Let’s begin by creating a connection object.

_ConnectionPtr pConnection; HRESULT hr = pConnection.CreateInstance(__uuidof(Connection)); if (FAILED(hr)) return FALSE;

And now let’s try to make the database connection using the ADO connection object created above.

_bstr_t strConnectionString =_T("Driver={Microsoft Access Driver (*.mdb)};Dbq=database.mdb;Uid=username;Pwd=my_password;"); try { pConnection->Open(strConnectionString, _T(""), _T(""), adOpenUnspecified); } catch (_com_error& e) { #ifdef _DEBUG AfxMessageBox(e.ErrorMessage()); AfxMessageBox(e.Description()); #endif return FALSE; }

Now let’s put the above source code in a single function which can be used to create a database connection.

BOOL CAdoConnection::InitConnection() { HRESULT hr = ::CoInitialize(NULL); if (FAILED(hr)) return FALSE; hr = pConnection.CreateInstance(__uuidof(Connection)); if (FAILED(hr)) return FALSE; _bstr_t strConnectionString =_T("Driver={Microsoft Access Driver (*.mdb)};" "Dbq="+strDatabase+";Uid=;Pwd="+strPassword+";"); try { pConnection->Open(strConnectionString, _T(""), _T(""), adOpenUnspecified); } catch (_com_error& e) { #ifdef _DEBUG AfxMessageBox(e.ErrorMessage()); AfxMessageBox(e.Description()); #endif return FALSE; } return TRUE; }

Construct and execute a simple SQL statement

For example let’s say we want to add a new record to our database. We can use the following function:
BOOL CAdoConnection::AddNewRecord(CString strName , CString birthdayStr, UINT ageDw, BOOL bMarryed) { CString szSQL; szSQL.Format("INSERT INTO `employees`(`name`,`birthday`,`age`,`married`) VALUES('%s','%s',%d,%d)",(LPSTR)(LPCSTR)strName,(LPSTR)(LPCSTR)birthdayStr,ageDw,bMarryed); try { pConnection->Execute(szSQL.GetBuffer(),NULL,adExecuteNoRecords); } catch(_com_error& e) { #ifdef _DEBUG AfxMessageBox(e.Description()); #endif return FALSE; } return TRUE; }

In the same mode we can execute different SQL queries which not return any results, like UPDATE, DELETE, etc.

Execute the statement, retrieve and iterate over the results

First we have to create a function which will return the value for a specified column in the recordset.
CString CAdoConnection::FieldToChar(_RecordsetPtr recset, _variant_t Fieldname) { _variant_t tmpvariant; CString tmpChar; tmpvariant = recset->GetCollect(Fieldname); tmpChar.SetString((_bstr_t)tmpvariant); return (tmpChar); }

And now let’s create a function which iterate over the recordset and save all the records in a vector container.
BOOL CAdoConnection::SelectAllRows(std::vector<EmplyeeRow>& emplyeesArray) { emplyeesArray.clear(); CString szSQL="SELECT * FROM `employees` ORDER BY `id` ASC"; try { _RecordsetPtr pRecordSet(__uuidof(Recordset)); pRecordSet->Open(szSQL.GetBuffer(), pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText); while(!pRecordSet->A_EOF) { EmplyeeRow row; row.primaryKey = atol((LPSTR)(LPCSTR)FieldToChar(pRecordSet,L"id")); row.nameStr = FieldToChar(pRecordSet,L"name"); row.dateBirthday = FieldToChar(pRecordSet,L"birthday"); row.dwAge = atoi((LPSTR)(LPCSTR)FieldToChar(pRecordSet,L"age")); row.isMarryed = (atoi((LPSTR)(LPCSTR)FieldToChar(pRecordSet,L"married"))!=0); emplyeesArray.push_back(row); pRecordSet->MoveNext(); } pRecordSet->Close(); } catch(_com_error& e) { #ifdef _DEBUG AfxMessageBox(e.Description()); #endif return FALSE; } return TRUE; }

After we finish with the database we should close the connection and release the resources allocated for COM.
if((pConnection != NULL) && (pConnection->State == 1)) pConnection->Close(); ::CoUninitialize();

License

This article, along with any associated source code and files, is licensed under The Intelliproject Open License (IPOL)

About the author