Click here to Skip to main content
12,747,141 members (31,804 online)
Click here to Skip to main content
Add your own
alternative version

Stats

24.8K views
163 downloads
22 bookmarked
Posted 24 Aug 2007

The Ultimate Toolbox Database Classes

, 25 Aug 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Ultimate Toolbox Database classes include DAO and CSV file classes.

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

Contents

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 samples\database\CsvTest sample in action.

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 samples\database\DAOClass sample in action.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

The Ultimate Toolbox
Web Developer
Canada Canada
In January 2005, David Cunningham and Chris Maunder created TheUltimateToolbox.com, a new group dedicated to the continued development, support and growth of Dundas Software’s award winning line of MFC, C++ and ActiveX control products.

Ultimate Grid for MFC, Ultimate Toolbox for MFC, and Ultimate TCP/IP have been stalwarts of C++/MFC development for a decade. Thousands of developers have used these products to speed their time to market, improve the quality of their finished products, and enhance the reliability and flexibility of their software.
Group type: Organisation

435 members


You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170215.1 | Last Updated 25 Aug 2007
Article Copyright 2007 by The Ultimate Toolbox
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid