The Ultimate Toolbox Database Classes

• Download the Ultimate Toolbox source code - 3 MB
• Download the Ultimate Toolbox sample projects - 5.4 MB
• Download the Ultimate Toolbox documentation in compiled HTML help (CHM) format - 4.9 MB

Visit the Ultimate Toolbox main page for an overview and configuration guide to the Ultimate Toolbox library.

Contents

• Introduction
• Comma Separated Values
• Data Access Objects
• History
• Comments

Introduction

The Ultimate Toolbox Database category contains two classes dealing with Data Access Objects and CSV files.

Comma Separated Values

COXCsvFile extends the MFC class CStdioFile by adding functions for processing the lines of a CStdioFile as rows in a comma delimited data file (CSV file).

The following code is taken from the sample, and reads in csv data in a pre-defined format.

//


// Read in one of two predefined comma delimited files, and display their 


// contents in the multiline edit control in this dialog.


//


// Note that the input files for this example use the technique of putting 


// two separate tables into a single CSV file, which is not supported by all 


// programs. But, it can still be a very useful technique for your own use.


//


void CCsvTestDlg::OnOK() 
{
    //


    // Retrieve the settings from the dialog


    //


    UpdateData();

    COXCsvFile         inFile;
    CFileException     fe;
    LPCTSTR            lpstrInFilename=(m_bUseHeaders ? _T(
        "headers.csv") : _T("noheader.csv"));
    SData              data;

    // clear out the old data array


    m_aData.RemoveAll();

    // disable the save button


    m_ctrlSave.EnableWindow(FALSE);

    // Open the input file


    if (!inFile.Open(lpstrInFilename, CFile::modeRead, &fe))
    {
        return ;
    }
    TRY
    {
        CWaitCursor cursor;
        short nId;
        int nYesNo;
        int index;

        OXTRACE(_T("CCsvTestDlg::OnOK()"));

        // check if we shuold check for headers in the input file


        if (m_bUseHeaders)
        {
            //


            // Read in the headers from the input file. After reading the


            // headers in from the first line of the file, set the aliases


            // for the "Integer" column.


            // Note: by using the column headers, and using those headers


            // in the calls to ReadColumn(), the exact order of the columns


            // in the CSV file becomes irrelevant to your program. This is


            // shown by the fact that the columns in "headers.csv" 


            // are in a different order from "noheader.csv", but the 


            // results displayed in the edit control are the same.


            //


            inFile.GetColumns(8);
            inFile.SetAliases(headers[3], aliases);
        }
        else
        {
            //


            // Since there are no headers in the input file, set the names


            // of the columns that we will use in later calls, and let


            // the COXCsvFile object know how many columns to expect.


            // Note, if you want to only refer to the columns by their column


            // indicies, this call does not need to be made, as the first 


            // call to ReadLine() will set the number of columns in the 


            // table.


            //


            inFile.SetColumns(headers);
        }
        //


        // Read the individual records from one file to the other.


        //


        // NOTE : I said records, not lines, since quoted strings can


        // contain new lines in them. This is a feature supported by programs


        // like MS Access, but not by MS Excel.


        //


        while (inFile.ReadLine())
        {
            data.Clear();

            if (inFile.IsLineEmpty())
            {
                // Blank lines can either be ignored, or they can be used 


                // like here to mark the end of one table, and the start of 


                // another one.


                //


                OXTRACE(_T("Reached the end of the first table"));
                break;
            }
            OXTRACE(_T("Reading next line"));

            // Read the data from the various columns into the members of the


            // SData structure.


            //


            inFile.ReadColumn(_T("ID"), data.nId);
            OXTRACE_WRITEVAL(_T("ID"), data.nId);

            inFile.ReadColumn(_T("Name"), data.strName);
            OXTRACE_WRITEVAL(_T("Name"), data.strName);

            inFile.ReadColumn(_T("Byte"), data.ucByte);
            OXTRACE_WRITEVAL(_T("Byte"), data.ucByte);

            inFile.ReadColumn(_T("Integer"), data.nInt);
            OXTRACE_WRITEVAL(_T("Integer"), data.nInt);

            inFile.ReadColumn(_T("Float"), data.fFloat);
            OXTRACE_WRITEVAL(_T("Float"), data.fFloat);

            inFile.ReadColumn(_T("Double"), data.fDouble);
            OXTRACE_WRITEVAL(_T("Double"), data.fDouble);

            inFile.ReadColumn(_T("String"), data.strString);
            OXTRACE_WRITEVAL(_T("String"), data.strString);

            inFile.ReadColumn(_T("Valid"), nYesNo, lpstrYesNo);
            OXTRACE_WRITEVAL(_T("Valid"), nYesNo);
            data.bValid = (nYesNo == 0);

            m_aData.Add(data);
        }
        // Read in the second table, merging its data with the first


        if (m_bUseHeaders)
        {
            // Read in the headers for the second table in this file.


            //


            inFile.GetColumns(3);
        }
        else
        {
            // Set the names, and number of columns to expect, for the


            // second table in this file


            //


            inFile.SetColumns(headers2);

            // Read the records in one at a time from the second table.


            while (inFile.ReadLine())
            {
                if (inFile.IsLineEmpty())
                {
                    // Blank lines can either be ignored, like here, or they 


                    // can be used to mark the end of one table, and the 


                    // start of another one.


                    //


                    continue;
                }
                OXTRACE(_T("Reading next line"));

                // Read the ID field for this record, and search for it in 


                // the SData array, to match the records from the two tables


                // up to each other.


                //


                inFile.ReadColumn(_T("ID"), nId);
                OXTRACE_WRITEVAL(_T("ID"), nId);

                for (index = 0 ; index < m_aData.GetSize() ; ++index)
                {
                    if (m_aData[index].nId == nId)
                    {
                        // found the matching record from the previous table


                        //


                        break;
                    }
                }
                if (index >= m_aData.GetSize())
                {
                    // skip this record, as this ID did not exist in the 


                    // other table


                    //


                    OXTRACE_WRITEVAL(_T(
                        "ID not found from earlier table"), nId);
                    continue;
                }
                // Read the remaining columns into the SData structure from 


                // the previous table


                //


                inFile.ReadColumn(_T("Last Name"), 
                    m_aData[index].strLastName);
                OXTRACE_WRITEVAL(_T("Last Name"), 
                    m_aData[index].strLastName);

                inFile.ReadColumn(_T("Address"), 
                    m_aData[index].strAddress);
                OXTRACE_WRITEVAL(_T("Address"), 
                    m_aData[index].strAddress);
            }
            // format the data for the multiline edit control


            //


            m_strData.Empty();
            for (index = 0 ; index < m_aData.GetSize() ; ++index)
            {
                CString strTemp;
                data = m_aData[index];

                strTemp.Format(_T("%u. %s %s, %s\r\n")
                    _T("    %u, %d, %f, %f, %s\r\n")
                    _T("    \"%s\"\r\n"),
                    data.nId, data.strName, data.strLastName, 
                    data.strAddress, data.ucByte, data.nInt, 
                    data.fFloat, data.fDouble,
                    (data.bValid ? _T(
                        "Valid") : _T("Invalid")),
                        data.strString);
                m_strData += strTemp;
            }
            UpdateData(FALSE);

            // enable the Save button


            //


            m_ctrlSave.EnableWindow(m_aData.GetSize() > 0);
        }
        CATCH_ALL(e)
        {
            inFile.Abort();
        }
        END_CATCH_ALL
    }

See the compiled HTML help for more on using COXCsvFile.

Data Access Objects

The COXDao class wraps all of MFC's standard DAO classes (CDaoDatabase, CDaoTableDef, CDaoQueryDef and CDaoRecordset) into one easy to use class.

All of the necessary DAO classes are created and maintained internally. The most commonly used features are exposed through this class, plus there is easy access to the internal objects if extra functionality is required. For a general DAO overview please refer to the On-Line help supplied with Visual C++.

This class is very straightforward to use and allows for common database tasks to be accomplished in only a couple lines of code. The first step is to create a database object of type COXDao. Next, open the database using either the COXDao::Open or COXDao::OpenSQL commands. Once open, the database is ready for use. Once finished with the database the object can be destroyed, this closes any open database automatically. Also you can reuse the same database object multiple times, since the COXDao::Open or COXDao::OpenSQL commands will automatically close any previously opened database.

Usage

COXDao dao;
CString str;
dao.OpenSQL("C:\\MyDatabase.mdb", "SELECT * from MyTable");
dao.GetField("LastName", str);

See the Database | COXDao section of the compiled HTML help file for a complete COXDao class reference.

History

Initial CodeProject release August 2007.