CODBCRecordset Class

• Download source files - 12.7 KB

Table of Contents

• Introduction
• Examples of how to use CODBCRecordset
• How does CODBCRecordset work?

Introduction

In a usual MFC database project, we have a lot of CRecordset derived classes generated by ClassWizard. The class presented here, CODBCRecordset is a very easy to use replacement of all these CRecordset classes. CODBCRecordset is derived from CRecordset but it does not have hardcoded inside the number and the type of the database fields.

I have seen some implementations trying to solve this problem. But CODBCRecordset has at least two advantages:

1. CODBCRecordset can be used not only to get values from the database, but to write values into the database using the MFC way.
2. The other implementations cannot simultaneously open more than one recordset through one database connection when the database is MS SQL Server. In some cases, this could be a very big problem because opening a new connection is expensive in terms of time and resources.

Because CODBCRecordset is derived from CRecordset and uses its data exchange mechanism, it is fully compatible with MFC ODBC class CDatabase .

Each field value is stored in a CDBField object. The CDBField class is inherited from CDBVariant and has some added functionality so it's simple to use.

CODBCRecordset and CDBField classes support all database data types. CDBField makes implicit type conversion where it is appropriate to supply the data in the requested format.

Here is a list of CODBCRecordset and CDBField methods:

CDBField& Field( LPCTSTR )<br />
			CDBField& Field( int )

CDBField& operator( LPCTSTR )<br />
			CDBField& operator( int )

Examples of How to Use CODBCRecordset

You should include the files ODBCRecordset.h and ODBCRecordset.cpp in your project.

I usually include this line in my StdAfx.h file.

#include "ODBCRecordset.h"

Here is a simple code showing how CODBCRecordset can be used.

/////////////////////////////////////////////////////////////////////////////
CDatabase    db;
//    This connect string will pop up the ODBC connect dialog
CString        cConnect = "ODBC;";
db.Open( NULL,                //    DSN
     FALSE,                //    Exclusive
     FALSE,                //    ReadOnly
     cConnect,            //    ODBC Connect string
     TRUE                //    Use cursor lib
   );

COleDateTime    dOrderDate;

CODBCRecordset    rs( &db );
rs.Open( "SELECT * FROM Orders \
    WHERE ORDER_DATE > 'jan 1 2000' \
    ORDER BY ORDER_DATE" );
for( ; ! rs.IsEOF(); rs.MoveNext() )
{
//    The choice is yours. You may choose whatever way
//    you want to get the values
//
    //    These return COleDateTime value
    dOrderDate = rs.GetDate( "ORDER_DATE" );
    dOrderDate = rs.Field("ORDER_DATE").AsDate();

    //    These make implicit call to AsDate()
    dOrderDate = rs("ORDER_DATE");
    dOrderDate = rs.Field("ORDER_DATE");

    //    Now edit the fields in the recordset
    rs.Edit();
    rs("ORDER_DATE") = "jan 1 1999";    // Implicit conversion
    rs.Field("ORDER_DATE") = "jan 1 1999";  // Implicit conversion
    rs.Update();
}    //    for(....
//////////////////////////////////////////////////////////////////////////////

If ORDER_DATE is stored in the database as datetime or compatible data type, the value will be got directly.
If ORDER_DATE is stored in the database as string or compatible data type (char, varchar) the value will be converted via COleDateTime::ParseDateTime() method. If conversion fails, dOrderDate will be set to COleDatetime::invalid.

When opening a resultset generated by join statements, it is possible to get 2 or more columns that have the same name. CODBCRecordset leaves the name of the first column intact but other repeated columns are renamed with adding the number this columns repeats the name. Not repeated column names are left intact. E.g.

SELECT * FROM Orders, Customers WHERE Orders.CUST_ID = Customers.ID

If the table Orders have a column with name ID and Customers have a column with name ID, CODBCRecordset will rename ID from Customers to ID2 and all other not repeating column names will be intact.

Well, here is a tip: Rename columns manually to be sure what name they have, e.g.:

SELECT Orders.*, Customers.ID as CUSTOMERS_ID
    FROM Orders, Customers 
    WHERE Orders.CUST_ID = Customers.ID

How Does CODBCRecordset Work?

CODBCRecordset allocates storage for all fields in the resultset and uses MFC Record Field eXchange mechanism like it has been inherited from CRecordset using ClassWizard.