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 justone second! Whereas, using the CRecordset, it takes around 20 seconds. These numbers are based on 266 MHz Pentium with 64MB RAM..
- 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:
**CONSULTANTS Definition file
#pragma message("Include AfxTempl.h in StdAfx.h for faster Compilation")
#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
//structure to hold the final data
CConsultantsSet(CDatabase* pDB = NULL);
CTypedPtrArray <CPtrArray, pDB_CONSULTANTS_FIELDS> m_aData;
** 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.
. . .
int nCount = tblSet.Load();
for (int I = 0; I < nCount; I++)
pDB_CONSULTANT_FIELDS pdbFields = tblSet.m_aData.GetAt(I);
If you want to insert an item to the table,
dbFields.m_ID = 100;
dbFields.m_Name = "Some Name";
// Fill in rest of the fields
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.
I would like to thank the CodeGuru contributors for List Control and Bitmap buttons I have used in this tool.