MFC data access using OLE DB

Hello Boys and Girls,

Today I want to give an example on how to access and retrieve data from a relational database , in this case Sybase SQL Anywhere, using MFC.

Yes MFC. MFC is an early attempt by Microsfot on creating a Object Oriented API for Windows development. Basically MFC is wrapper API for the underlaying Windows API.
For this example I am using Tools For Men (AKA Visual Studio 2010) and C++.

The high level steps are:

1. Establishing connection to the database. For this I will be using OLE DB
2. Call a store procedure.

Establishing a connection to a database using OLE DB.

For opening a connection to our SQL Anywhere a CDatasource,CSession and initialisation string is needed:

CDataSource _db;

CSession session

LPTSTR connString = _T("Provider=MSDASQL.1;Persist Security Info=False;User ID=admin;Extended Properties=\"DSN=hoteldb;UID=admin;\"");

Since OLE DB is made of  set of interfaces that implement COM a HRESULT variable is needed to keep track of the result of our operation.

HRESULT hr;

hr = _db.OpenFromInitializationString(connString);

if(FAILED(hr)){

AfxMessageBox(_T("It can't connect to database"),0,0);

}

hr = session.Open(_db);

if(FAILED(hr)){

AfxMessageBox(_T("it can't open database session"),0,0);

}

Once that we have succesfull open a session we can use this instance to access our database.

Call a Sybase SQL Anywhere store procedure

In this example my recordset is bound to the data source via my CAccessor class CCount. For an explanation of what a CAccesors are and their role in OLE DB you can go
here

When opening the recordset I am passing a store procedure, togehter with session. Store procedures are an elegant way of executing SQL operations on databases.

CCommand<CAccessor<CCount>> recordset;

hr = recordset.Open(session,storeProcCall );

if(FAILED(hr)){

AfxMessageBox(_T("Can't open table"),0,0);

}

while(recordset.MoveNext() == S_OK){

passes= recordset.m_count;

}

After getting the result of the record set is a good idea to close the session and recordset object.

CloseSession();

recordset.Close();

Then I can pass or return my result to a calling method:

return passes;

The code for the accessor type is very simple:

Class CCount{

public:

int m_count;

//input

TCHAR m_From[50];

TCHAR m_To[50];

TCHAR m_PassCode[50];

//Parameter Accessor

BEGIN_PARAM_MAP(CCount)

SET_PARAM_TYPE(DBPARAMIO_INPUT)

COLUMN_ENTRY(1, m_From)

SET_PARAM_TYPE(DBPARAMIO_INPUT)

COLUMN_ENTRY(2, m_To)

SET_PARAM_TYPE(DBPARAMIO_INPUT)

COLUMN_ENTRY(3, m_PassCode)

END_PARAM_MAP()

BEGIN_COLUMN_MAP(CCount)

COLUMN_ENTRY(1,m_count)

END_COLUMN_MAP()

};

I found CString MFC class very handy when building the store procedure parameters:

CString storeProcCall ("CALL admin.GuestPassDetails(");

storeProcCall.Append(from);

storeProcCall.Append(_T(","));

storeProcCall.Append(to);

storeProcCall.Append(_T(","));

storeProcCall.Append(passesCode);

storeProcCall.Append(_T(") "));

This CString is passed to the recordset as I mentioned previoulsy:

hr = recordset.Open(session,storeProcCall );

See you next time.