ODBC Class generator

(Designed and developed by Shekar Narayanan)

I have developed this Class Generator after using the CRecordset
for quite some time. CRecordset is great but it has lot of limitations and is SLOW.

Most of the time, I had to display thousands of records in a list view. Using the
CRecordset, it takes a lot of time. MFC also provides a way to do ‘bulk fetch
but it is tedious to implement.

So, I created a class which will do the bulk fetching and also allow easy
inserts, update and delete. After creating tens of these kind of classes, it was no
difficult to automate the class creation process.

A simple comparison:

The code generated by this tool along with virtual list view gets and displays
5000 records from SQL server database in just
one second! Whereas, using the CRecordset, it takes around 20 seconds. These
numbers are based on 266 MHz Pentium with 64MB RAM..

 

Features

  • Easily create a class which uses Bulk Row Fetching for faster data retrieval
  • Uses direct ODBC SDK calls which are easy to understand and flexible to modify
  • More methods can be easily added to the object
  • Creating Joins and using other SQL features is very easy, since the object uses direct
    SQL statements

 

Usage of the Product

Using the Class Generator is easy.

  • Click Open to display the list of Data Sources
  • Select the Data Source and click Next
  • Select the Table or View you want to use and click Next
  • Click Finish

A list view displays the columns in the table along with the default member variables.
You can easily change the name of the member variable and if you want to exclude any
column from the object, you can do that by simply clicking Remove button.

 After changing /removing the variable names, click the Create button to open the
Generate Dialog box. It shows the default values based the on the table name. After
modifying the values click the OK button to generate the code. You can also print this
list.

 

Following is a sample header file generated by this tool:

/*

**ConsultantsSet.h

**CONSULTANTS Definition file

*/

#if !defined(__DB_CONSULTANTS_FIELDS__)

#define __DB_CONSULTANTS_FIELDS__

#ifndef __AFXTEMPL_H__

#pragma message("Include AfxTempl.h in StdAfx.h for faster Compilation")

#include <afxtempl.h>

#endif

#define CONSULTANTS_NAME_SIZE 31

#define CONSULTANTS_HOME_ADDRESS_SIZE 81

#define CONSULTANTS_CLIENT_NAME_SIZE 31

#define CONSULTANTS_CLIENT_ADDRESS_SIZE 81

 

//Internal Cache for the data

typedef struct

{

long m_ID;

SDWORD m_IDInd;

char m_Name[CONSULTANTS_NAME_SIZE];

SDWORD m_NameInd;

char m_HomeAddress[CONSULTANTS_HOME_ADDRESS_SIZE];

SDWORD m_HomeAddressInd;

char m_ClientName[CONSULTANTS_CLIENT_NAME_SIZE];

SDWORD m_ClientNameInd;

char m_ClientAddress[CONSULTANTS_CLIENT_ADDRESS_SIZE];

SDWORD m_ClientAddressInd;

}FF_DB_CONSULTANTS_FIELDS, *pFF_DB_CONSULTANTS_FIELDS;

 

//structure to hold the final data

typedef struct

{

long m_ID;

CString m_Name;

CString m_HomeAddress;

CString m_ClientName;

CString m_ClientAddress;

}DB_CONSULTANTS_FIELDS, *pDB_CONSULTANTS_FIELDS;

 

//Class Definition

class CConsultantsSet

{

public:

//Standard constructor

CConsultantsSet(CDatabase* pDB = NULL);

//Standard Destructor

~CConsultantsSet();

//Operations

bool GetSpecific(pDB_CONSULTANTS_FIELDS);

bool Insert(pDB_CONSULTANTS_FIELDS);

bool Update(pDB_CONSULTANTS_FIELDS);

bool Delete(pDB_CONSULTANTS_FIELDS);

int Load();

//Attributes

CTypedPtrArray <CPtrArray, pDB_CONSULTANTS_FIELDS> m_aData;

private:

CDatabase* m_pDatabase;

HSTMT m_hstmt;

int m_nRowSetSize;

};

#endif

/*

** end file

*/

 

The header file contains two structures FF_DB_CONSULTANTS_FIELDS and
DB_CONSULTANTS_FIELDS. The first one is used for bulk row fetching. The second is used to
in a CTypedPtrArray and contains the actual data.

 

Using the Generated Code in your Application

In order to use this class in your application, create a regular MFC application with
database header support. The View can be either CFormView or CListView . In this example,
m_List is a CListCtrl.

#include "ConsultantSet.h"

 

void OnInitialUpdate()

{

    . . .

    CConsultantSet tblSet(&theApp.m_DB);

    int nCount = tblSet.Load();

    for (int I = 0; I < nCount; I++)

    {

        pDB_CONSULTANT_FIELDS pdbFields =
tblSet.m_aData.GetAt(I);

m_List.InsertItem(I, pdbFields->m_Name;

m_List.SetItemText(I, pdbFields->m_ClientName);

    }

}

 

If you want to insert an item to the table,

    CConsultantSet tblSet(&theApp.m_DB);

    DB_CONSULTANT_FIELDS dbFields;

    dbFields.m_ID = 100;

    dbFields.m_Name = "Some Name";

    // Fill in rest of the fields

    tblSet.Insert(&dbFields);

 

 

Rest of the functions, follow the same standard and very easy to understand. I always
use a single CDatabase object in the Application class. Create a CDatabase member variable
in the CWinApp derived class and also make the ‘theApp’ as extern in the header file. This
way, you don’t have to use AfxGetApp() and typecast it every time.

 

This tool generates the code which can be compiled in VC++ 5.0. With minor
modification, it can be used with any C++ compiler.

 I have tested this tool with MS Access and
SQL Server only. So there may be a few bugs. If you encounter any bugs, please email me.

I would appreciate your ideas for enhancements.

 

Acknowledgments:

I would like to thank the CodeGuru contributors
for List Control and Bitmap buttons I have used in this tool.

Download 274K

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read