SAMPLE: Dynamic Column Binding With MFC Database Classes

The information in this article applies to:

• The Microsoft Foundation Classes (MFC), included with:

      - Microsoft Visual C++ for Windows, version 1.5
  

SUMMARY

The DYNCOL sample demonstrates how to dynamically determine the number, types, and names of each column in a given table and then bind these columns to dynamically allocated objects in your CRecordset derived class.

To obtain the sample, download DYNCOL.EXE, a self-extracting file, from the Microsoft Software Library (MSL) on the following services:

• Microsoft Download Service (MSDL)

        Dial (206) 936-6735 to connect to MSDL
        Download DYNCOL.EXE (size: 47764 bytes) 
  

• Internet (anonymous FTP)

        ftp ftp.microsoft.com
        Change to the SOFTLIB\MSLFILES directory
        Get DYNCOL.EXE (size: 47764 bytes) 
  

   DYNCOL.EXE -d

MORE INFORMATION

DYNCOL follows the basic procedures outlined in the documentation titled, "Dynamically Binding Data Columns", found in Database Encyclopedia of the Books OnLine. However, DYNCOL has no permanent members, therefore only one list is generated that contains information for all the columns in the table. If you have a subset of columns that are always present and are therefore member variables of the CRecordset derived class, you will need to follow the instructions in the article on generating only a list of the columns not already bound. DYNCOL only constructs a single list and implements methods for performing DoFieldExchange() and DoDataExchange() for this list of dynamically allocated objects.

Generating the List

The first thing DYNCOL must do is determine how many columns are in the database, and of what type. To do this, the CRecordset-derived object (CDynaSet) contains a CColumns object to query this information. In CDynaSet, the Open member function has been overridden so that the column information can be retrieved before the base-class CRecordset::Open function is called to open the database. After this information has been accessed, a list of CColumnData objects is allocated to hold a description of each column in the table, plus a pointer to a storage object allocated for that column (to be used in the record field exchange routines).

BOOL CDynaSet::Open(UINT nOpenType, LPCSTR lpszSQL, DWORD dwOptions) {

  // Specify the table to look at
  m_Columns.m_strTableNameParam = "DYNABIND_SECTION";

  // Set the database to be the CDynaSet's database if one hasn't
  // already been set (this should be the case)
  if (m_Columns.m_pDatabase == NULL)
    m_Columns.m_pDatabase = m_pDatabase;

  // Open the recordset to get the column info
  if (!m_Columns.Open(CRecordset::forwardOnly, NULL,
         CRecordset::readOnly))
    return FALSE;

  // Initialize the number of fields dynamically allocated to CDynaSet
  m_nFields = 0;

  // Loop until we've seen all the columns
  while (!m_Columns.IsEOF())
  {
    // Allocate a new CColumnData object for the current column
    CColumnData *pData = new CColumnData;

    // Store the colmun information
    pData->m_nDataType = m_Columns.m_nDataType;
    pData->m_strColumnName = m_Columns.m_strColumnName;

    // Allocate an object of the appropriate type to store
    // the coulmn data
    switch(pData->m_nDataType)
    {
      case SQL_CHAR:
        pData->m_pData = (void *)new CString;
      break;

      case SQL_SMALLINT:
        pData->m_pData = (void *)new int;
      break;

      case SQL_TIME:    // Fall through
      case SQL_DATE:    // Fall through
      case SQL_TIMESTAMP:
        pData->m_pData = (void *)new CTime;
      break;

      default:
        break;
    }

    // Add the column descriptor to the list and
    // increment the number of columns in the CDynaSet
    m_pList.AddTail(pData);
    m_nFields++;

    // Get the next column's information
    m_Columns.MoveNext();
  }

   // Free the HSTMT used to get the table info
  RETCODE nRetCode;
  AFX_SQL_SYNC(::SQLFreeStmt(m_Columns.m_hstmt, SQL_CLOSE));

  // Return the base class if we got this far
  return CRecordset::Open(nOpenType, lpszSQL, dwOptions);

Getting the Column Data Into the Recordset

Once the CColumnData list has been generated, DoFieldExchange() must be overridden to traverse this list and call the appropriate RFX routine for each CColumnData's storage object.

void CDynaSet::DoFieldExchange(CFieldExchange* pFX)

  // Set the type of exhange; same as AppWizard generated
  pFX->SetFieldType(CFieldExchange::outputColumn);

  // Get a pointer to the first CColumnData object in the list
  POSITION rPos = m_pList.GetHeadPosition();
  CColumnData *pData = (CColumnData *)m_pList.GetNext(rPos);

  // Loop until we've traversed all the columns
  while (pData)
  {
    // Call the appropriate RFX routine for the column's type
    switch(pData->m_nDataType)
    {
      case SQL_CHAR:
           RFX_Text(pFX, pData->m_strColumnName,
                       *((CString *)(pData->m_pData)));
      break;

      case SQL_SMALLINT:
        RFX_Int(pFX, pData->m_strColumnName,
                *((int *)(pData->m_pData)));
      break;

      case SQL_TIME:    // Fall through
      case SQL_DATE:    // Fall through
      case SQL_TIMESTAMP:
        RFX_Date(pFX, pData->m_strColumnName,
                   *((CTime *)(pData->m_pData)));
      break;

      default:
        break;
    }

    // Set pData to NULL if that was the last column
    if (rPos)
      pData = (CColumnData *)m_pList.GetNext(rPos);
    else
      pData = NULL;
  }

Getting the Recordset Data into the View

After getting the data into the recordset, the last thing to do is to get this information into the CRecordView-derived class to be displayed. In the case of DYNCOL, it simply uses a GRID VBX control to do a straight dump of the data onto the view. In order to do this, the CColumnData list must again be traversed. Normally an appropriate DDX routine would be called for each element in the list, but DYNCOL only puts the information into the cells of a GRID VBX control to be displayed.

void CDynaView::DoDataExchange(CDataExchange* pDX)

   CRecordView::DoDataExchange(pDX);
   //{{AFX_DATA_MAP(CDynaView)
   DDX_VBControl(pDX, IDC_GRID, m_vbxGrid);
   //}}AFX_DATA_MAP

   CPtrList *pList = &m_pSet->m_pList;

   // Get the first element
   POSITION rPos = pList->GetHeadPosition();
   CColumnData *pData = (CColumnData *)pList->GetNext(rPos);

   // Allocate a string buffer to be used in conversions
   CString strBuffer;

   // Set the intial row to fill in
   int row = 1;

   // Set the GRID to have the correct number of rows
   m_vbxGrid->SetNumProperty("Rows", m_pSet->m_nFields + 1);

   // Loop until we've traversed all the CColumnData objects
   while (pData)
      {
        // Insert the column name in the first column and move
        // to the second column to insert the data
        m_vbxGrid->SetNumProperty("Row", row);
        m_vbxGrid->SetNumProperty("Col", 1);
        m_vbxGrid->SetStrProperty("Text", pData->m_strColumnName);
        m_vbxGrid->SetNumProperty("Col", 2);

        // Call the appropriate DDX
        switch(pData->m_nDataType)
        {
          case SQL_CHAR:
   m_vbxGrid->SetStrProperty("Text", *((CString *)(pData->m_pData)));
          break;

          case SQL_SMALLINT:
            {
              // Convert the int to a string
              char *pBuffer = strBuffer.GetBuffer(32);
              _itoa(*((int *)(pData->m_pData)), pBuffer, 10);
            }
            strBuffer.ReleaseBuffer();
            m_vbxGrid->SetStrProperty("Text", strBuffer);
          break;

          case SQL_TIME:    // Fall through
          case SQL_DATE:    // Fall through
          case SQL_TIMESTAMP:
            // Convert the CTime to a string
            strBuffer = ((CTime *)(pData->m_pData))->Format( "%c" );
            m_vbxGrid->SetStrProperty("Text", strBuffer);
          break;

          default:
            break;
        }
        // Increment the row and check to see if that was our
        // last CColumnData in the list
        row++;
        if (rPos)
          pData = (CColumnData *)pList->GetNext(rPos);
        else
          pData = NULL;
      }

The most fundamental concepts of the sample are contained within the CColumns and CColumnData objects. The first allows DYNCOL to determine the name and data type for each column in the table. With this information it is able to allocate a linked list of CColumnData objects. Each CColumnData object is a descriptor that contains the the name, type and a pointer to a data object of the correct type. Once this list has been constructed, traversal routines must be put in the DoFieldExchange() and DoDataExchange() routines to perform the appropriate RFX/DDX function with the allocated data object.