OLE DB consumer using basic C++�

• Download source files - 170 Kb
• Download Northwind database - 603 Kb

Introduction

This article is the first of a series OLE DB articles that cover database access using the OLE DB Templates. This is aimed primarily at beginners while later articles will cover the intermeadiate and advanced aspects of using the OLE DB Consumers. The code for these articles is developed on Win 2k under Developer Studio 6 and Developer Studio 7. It will be stated and in some cases obvious which version of developer studio the code was written in. The code for this article was written in Developer Studio 6. 

First of all an ODBC Driver needs to be specified on the system. To do this open the Control Panel and double click on the ODBC Data Sources. This is located in the Control Panel on Windows 98 and in Control Panel\Administrative Tools in Windows 2000. Click on the System DSN tab and click on the add button. If you are following this using the Microsoft NWind database supplied select a Microsoft Access Driver ( *.mdb ) and call the Driver "AccessNWind" in the DataSource Name box and click O.K. What this does is create an ODBC DataSource that uses the access driver to access the database. All this will work transparently behind the scenes once we create the OLE DB Consumer class.

To Insert an OLE DB Consumer class in the project first of all create a standard MFC project. I've created a simple dialog project but in a real working environment you'd probably want a better display system, that is, if you wanted a display system at all. Once you have your project go to the Insert menu and clin the "New ATL Object" menu. You will be asked if you want to add ATL support to your MFC project. Answer yes to this and the main ATL dialog box will open.

Select the Consumer and click next.

 

The image above shows the completed dialog for now yours wont look too much like this so Click on the Select Datasource button and we'll set it up.

Although the picture above shows the Microsoft OLE DB Provider for ODBC Drivers it's better to select one of the Microsoft Jet drivers as the Microsoft OLE for ODBC drivers is no longer supported in the beta release ( 2 ) of Developer Studio 7. I don't know if this will be the case in the final release but presume it will.. Click on the Next button,

Select the ODBC Driver for the AccessNWind Database that was set up earlier in the use datasource name option. The next two pages of the dialog are simple so I'll go through them quickly here. Click the Test Connection button just to make sure that the connection to the Database is working correctly. As long as you've set up the ODBC driver properly there shouldn't be any problem. If you click on the Advanced tab the page presents you with the options that you require for accessing the database. Note that these options are to tell the driver the options that you require and at this point they are not going to be reflected in your codes access to the the database. For this example it will be fine if you select read/write access to the database. The final page in the dialog displays the results of what you have selected.

Once you click on the O.K. button you will be given the dialog below

This dialog gives you the tables that are available in this database so that the required code can be generated to access the database from your code. You dont have to choose the Suppliers Database here but it might help if you want to follow the examples coming up later.

Once you have selected the database table you will be taken back to the starting dialog. which will be filled in and almost ready to finish and get on with the code.

On the dialog you should select the table type. The two types will in fact make little difference within this example but I tend to stick to the guideline that tables are for when you want to go through the entire set of data within the table on a row by row basis and the Command type is for when you want to customize the data that you get back from the table. There will be a lot more on commands in later examples as these will will be primarily what we will use.

The final part of this dialog is the Support that we are going to add to the database. In this example I've clicked them all but you should always remember at this point that there's no point specifying a delete option here if you didn't tell the driver to have write access to the database. At this point if you've forgotten something you can change it easily.

Once you're happy with the dialog and the settings click on O.K.

The Generated Code

You should now have a Suppliers.h file added to your project and this is the code that will do all your database access for you. If you look at the file you will see there are two classes generated the first is the Accessor class and the second is the CSuppliers class. If you knew to OLE DB the accessor class comes as something of a surprise because the first you know about it is when you open the generated header file.

The Accessor Class

An accessor class is well, basically an accessor to the database. It defines the structure of the database for your database class, which in this case is CSuppliers

class CSuppliersAccessor
{
public:
	LONG m_SupplierID;	// Number automatically assigned to new supplier.

	TCHAR m_CompanyName[41]; // Number automatically assigned to new supplier.

	TCHAR m_ContactName[31]; // Number automatically assigned to new supplier.

	TCHAR m_ContactTitle[31]; // Number automatically assigned to new supplier.

	TCHAR m_Address[61];	// Street or post-office box.

	TCHAR m_City[16];	// Street or post-office box.

	TCHAR m_Region[16];	// State or province.

	TCHAR m_PostalCode[11];	// State or province.

	TCHAR m_Country[16];	// State or province.

	TCHAR m_Phone[25];	// Phone number includes country code or area code.

	TCHAR m_Fax[25];	// Phone number includes country code or area code.

	TCHAR m_HomePage[1024];	// Supplier's home page on World Wide Web.


BEGIN_COLUMN_MAP(CSuppliersAccessor)
END_COLUMN_MAP()


	// You may wish to call this function if you are inserting a record and wish to

	// initialize all the fields, if you are not going to explicitly set all of them.

	void ClearRecord()
	{
		memset(this, 0, sizeof(*this));
	}
};

The class above is map that the code will use for a generated table. As you can see it is nothing more than a copy of the columns in the database and once it is initialised you will be able to move through the database using CRowset::MoveNext();. The only thing that stands out here is the macros BEGIN_COLUMN_MAP( classname ) and END_COLUMN_MAP(). These macros are used for the positioning of the member variables in a Command Accessor. If when we were at the dialogs we had clicked on the Command radio button our class would have looked like this,

class CSuppliersAccessor
{ 
public:
	LONG m_SupplierID; // Number	automatically assigned to new supplier. 

	TCHAR m_CompanyName[41]; // Number automatically assigned to new supplier. 

	TCHAR m_ContactName[31]; // Number automatically assigned to new supplier. 

	TCHAR m_ContactTitle[31]; // Number automatically assigned to new supplier. 

	TCHAR m_Address[61]; // Street	or post-office box. 

	TCHAR m_City[16]; // Street	or post-office box. 

	TCHAR m_Region[16]; // State	or province.

	TCHAR m_PostalCode[11]; // State	or province. 

	TCHAR m_Country[16]; // State	or province. 

	TCHAR m_Phone[25]; // Phone	number includes country code or area code. 

	TCHAR m_Fax[25]; // Phone	number includes country code or area code. 

	TCHAR m_HomePage[1024]; // Supplier's	home page on World Wide Web. 

		
BEGIN_COLUMN_MAP(CSuppliersAccessor) 
	COLUMN_ENTRY( 1, m_SupplierID )
	COLUMN_ENTRY( 2, m_CompanyName )
	COLUMN_ENTRY( 3, m_ContactName )
	COLUMN_ENTRY( 4, m_Address )
	COLUMN_ENTRY( 5, m_City )
	COLUMN_ENTRY( 6, m_Region )
	COLUMN_ENTRY( 7, m_PostalCode )
	COLUMN_ENTRY( 8, m_Country )
	COLUMN_ENTRY( 9, m_Phone )
	COLUMN_ENTRY( 10, m_Fax )
	COLUMN_ENTRY( 11, m_HomePage ) 
END_COLUMN_MAP()


DEFINE_COMMAND( CSuppliersAccessor, _T( " \
	SELECT \
		SupplierID, \
		CompanyName, \
		ContactName, \
		ContactTitle, \
		Address, \
		City, \
		Region, \
		PostalCode, \
		Country, \
		Phone, \
		Fax, \
		HomePage, \
		FROM Suppliers" ) )


	// You may wish to call this function if you are inserting a record and wish to

	// initialize all the fields, if you are not going to explicitly set all of them.

	void ClearRecord()
	{
		memset(this, 0, sizeof(*this));
	}
};

You can see hear that the Accessor has filled in the BEGIN_COLUMN_MAP, END_COLUMN_MAP macros with the COLUMN_ENTRY( column, variable ) macros that bind the data in the rows when it is retrieved using the SQL statement which is a new part of the class that is part of the macro, DEFINE_COMMAND( class, sqlQuery ) At the moment as I said earlier it doesn't matter if you use the Table or the Command Accessor at this point. I'll be highlighting the power of the Command Accessor in the next essay.

The CSuppliers Class

The CSupplier class is the class that you will actually use in the code. The supplier class inherits the accessor class that is defined above.

class CSuppliers : public CTable< CAccessor< CSuppliersAccessor > >
					<CAccessor<CSuppliersAccessor>
{
public:
	HRESULT Open()
	{
		HRESULT		hr;

		hr = OpenDataSource();
		if (FAILED(hr))
			return hr;

		return OpenRowset();
	}
	HRESULT OpenDataSource()
	{
		HRESULT		hr;
		CDataSource db;
		CDBPropSet	dbinit(DBPROPSET_DBINIT);

		dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
		dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("AccessNWind"));
		dbinit.AddProperty(DBPROP_INIT_MODE, (long)3); 
		dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
		dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);

		hr = db.Open(_T("MSDASQL"), &dbinit); 
		if (FAILED(hr))
			return hr;	
		return m_session.Open(db);
	}

	HRESULT OpenRowset()
	{
		// Set properties for open 

		CDBPropSet propset(DBPROPSET_ROWSET); 
		propset.AddProperty(DBPROP_IRowsetChange, true);		
		propset.AddProperty(DBPROP_UPDATABILITY, 
		                    DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_INSERT | 
		                    DBPROPVAL_UP_DELETE); 
		return CTable< CAccessor< CSupplierAccessor >
 			<CAccessor<CSuppliersAccessor> >::Open(m_session, _
			                                       T("Suppliers"), &propset);
	}
	CSession m_session;
};

			

As can be seen from the class declaration

class CSuppliers : 
	public CTable< CAccessor< CSuppliersAccessor > > <CAccessor<CSuppliersAccessor>
		

The SupplierAccessor is wrapped by the CAccessor class which in turn is wrapped by the CTable class. and the whole lot is then inherited by the CSuppliers class which has very little functionality of its own. The CSupplier class gets its functionality through the Accessor, Rowset and Table classes that it inherits from. What the CSupplier class does do is open the connection to the database through the CSuppliers::Open function. This function calls the remaining two functions declared in the class.

The CSuppliers::OpenDataSource function sets up the properties for opening the database by declaring and then Adding the properties to a CDBPropSet class object. It also creates a CDataSource object and opens the database using the CDataSource::Open method which takes two parameters the first being the name of the provider of the data which in this case is the MSDASQL provider and then as a second parameter it takes the CDBPropSet object that has been set up. 

The CSuppliers::OpenRowSet function then sets up the properties for opening the rowset by declaring another CDBPropSet object and initialising it with the DBPROPSET_ROWSET value. The properties are then added according to the access requirements that were ticked in the check boxes earlier and then it calls the CTable::Open function.

The Sample Code

The sample application uses the created files to load the data from the database and to display it in a simple listbox format. which uses a header control so that the view of each column can be spaced in order to be able to read it properly.

To get the data from the database simply click on the Get Data button in the top left hand corner of the dialog box.

The CSuppliers class is used by including it in the Suppliers.h header file in stdafx.h and then declaring an instance of the CSupplier class as a member of the COLEDBConsumer1Dlg class. The class is used in the OnbnClickedButton1 function and one the files have been generated it's use in a project like this couldn't be easier.

HRESULT hResult = m_Suppliers.Open(); 
if( FAILED( hResult ) ) 
{ 
	AfxMessageBox( _T( "Error opening the database" ) ); 
	return; 
} 

m_Suppliers.MoveFirst();
		

First of all the open function is called followed by the move first function. That's all there is too it. All we have to do from here on in is load the data into the list box like so.

int nRow = 0;
char szBuffer[ 25 ];
do
{
	m_ListBox.InsertItem( LVIF_TEXT | LVIF_STATE, nRow, 
	                      ltoa( m_Suppliers.m_SupplierID, szBuffer, 10 ), 0, 
	                      LVIS_SELECTED, 0, NULL  );
	m_ListBox.SetItemText( nRow, 1, m_Suppliers.m_CompanyName );
	m_ListBox.SetItemText( nRow, 2, m_Suppliers.m_ContactName );
	m_ListBox.SetItemText( nRow, 3, m_Suppliers.m_ContactTitle );
	m_ListBox.SetItemText( nRow, 4, m_Suppliers.m_Address );
	m_ListBox.SetItemText( nRow, 5, m_Suppliers.m_City );
	m_ListBox.SetItemText( nRow, 6, m_Suppliers.m_Region );
	m_ListBox.SetItemText( nRow, 7, m_Suppliers.m_PostalCode );
	m_ListBox.SetItemText( nRow, 8, m_Suppliers.m_Country );
	m_ListBox.SetItemText( nRow, 9, m_Suppliers.m_Phone );
	m_ListBox.SetItemText( nRow, 10, m_Suppliers.m_Fax );
	m_ListBox.SetItemText( nRow, 11, m_Suppliers.m_HomePage );

	nRow++;

}
while( m_Suppliers.MoveNext() == S_OK );

And that's how you set up and access an OLE DB database using OLE DB Templates. In the next article we'll look at saving and modifying data and how to customize queries using the Command class.