ODBC Dynamic Connection

Many times it is neccessary to access databases without knowing the scheme of the database at design time.
Think that you are developing an application that supports ISE (Incremental Search and Edit) for a certain purpose.
The application reads a column of a database table to determine if there is any match to the letters that are allready typed in
a combo box. If it finds any then it displays the nearest one in the edit part of the combo.

When you do not know from where to get the information that should be displayed,or when the the source column
is being changed from time to time, the only solution is to bound your data columns at run time.
Of course you can also hard code the names of the columns, but you do not come far that way.

To solve a similar problem I developed a database connection class that can access any ODBC-database by using its DSN.
The CDBConnection is a class that takes care of all access to a database, regardless of the purpose. When it needs to fetch data
it uses Microsofts CDynamicBulkSet, and when it needs to edit data ( New, Update or Delete ) it uses CDynamicRow.

I used the CDynamicBulkSet here just to show several ways of accessing data dynamiclly and not at least to show also how to use multiple row fetch.

Where to use these classes?

  • When you do not want to create a CRecordSet derived class for each table you access.
  • When you read tables setup from a file. eg. Ini file.
  • When you want just to be able to reuse database access code in different projects.

    What do you need to use these classes?

    To use MFC database and collections support add the following two lines to your stdafx.h

  • afxdb.h MFC database classes
  • afxtempl.h To be able to use MFC collection templates

      Then add the follwing files to your project :

    • DBField.h
    • BulkSet.h and BulkSet.cpp
    • DynamicRow.h and DynamicRow.cpp
    • DBConnection.h and DBConnection.cpp

      And if you feel for it use the following class to keep connections while application is running.

    • ConnectionPool.h and ConnectionPool.cpp


    Before I begin explaining the usage of these classes, I would like you to notice that only strings are supported in this version.
    I Usually start by initializing all database connections and keeping them into the connectionpool.


    // Add a connection to the connectionpool if it does not exist allready
    if(!m_pConnectionPool->GetConnection(“TestDSN2″))
    {
    CDBConnection *pCon2 = new CDBConnection();
    // If you want to force logon dialog use this
    // pCon2->ConnectToDataBase(TestDSN2,””,””,TRUE);

    // Otherwise use this
    pCon2->ConnectToDataBase(“TestDSN2″,””,””,FALSE);
    m_pConnectionPool->AddConnection(pCon2);
    }//if(!m_pConPool->GetConnection(“TestDSN2”))

    In your GUI (if you want to show some data) you have to connect your components to columns in the databases!
    Here is a way of doing it.


    m_Combo2.m_sDSN = _T(“TestDSN2”); // We are getting values by using TestDSN2
    m_Combo2.m_sTableName = _T(“T_DemoTable”); // Connectiong to table T_DemoTable
    m_Combo2.m_sColumnName = _T(“C_Second”); // And column C_Second
    m_Combo2.SetConnectionPool(m_pConnectionPool); // We past a pointer to the connectionpool, so we can access memebermethods

    In its turn this GUI-component (CISECombobox in this case) takes advantage of methods in the CDBConnection to retrive string values and add them into its stringlist.
    In the next few lines I show how easy it is to ask for values from the database. This code is from “GetConcatinationString”.

    // sCurValue is the currentvalue entered in the combobox
    // We test for <= 1 just for performance reasons if( sCurValue.GetLength() <= 1) { // delete list of strings while (!m_StringList.IsEmpty()) m_StringList.RemoveHead(); // Construct the SQL-Query sSql.Format("SELECT DISTINCT %s FROM %s WHERE %s LIKE '%s*' ORDER BY %s",m_sColumnName, m_sTableName,m_sColumnName,sCurValue,m_sColumnName); CDBConnection * pCon = m_pConPool->GetConnection(m_sDSN);

    // Exceute the Sql
    if(pCon->ExecuteStatement(sSql))
    {
    // Fetch datarows ( here it is preferaed to call GetFieldValue())
    // I wanted just to show this function
    sRet = pCon->GetNextRow();
    while(sRet.GetLength())
    {
    m_StringList.AddTail(sRet);
    sRet = pCon->GetNextRow();
    }//while(sRet.GetLength())
    }
    }//if( sCurValue.GetLength() <= 1)

    Let us now take a look at how to add a new row ( that contains only one field ) to a database table.


    try
    {
    // Look for a connection called TestDSN2.
    // If it is not in the connectionpool make it!
    CDBConnection * pCon = m_pConnectionPool->GetConnection(“TestDSN2”);

    // Prepare this connection
    pCon->PrepareFieldExchange();

    // Construct a special Databasefield object and initialize it
    CDBField *pF1 = new CDBField();
    pF1->m_sColumn = “C_Second”; // Column name where this field fits
    pF1->m_sValue = m_sSecondString; // Cell value
    pCon->m_pFields.Add(pF1);

    // Add more fields as needed

    // Ask the connection to add this field to the T_DemoTable.
    if(!pCon->AddNewRow(“T_DemoTable”))
    {
    return;
    }
    }//try
    catch(CDBException e)
    {
    AfxMessageBox(e.m_strError,MB_OK | MB_ICONSTOP);
    e.Delete();
    return;
    }//catch(CDBException e)

    That was actually everything you need to know before beginning using the class-set enclosed here.

    What do you need to run the test application?

    • Add two DSNs to your system
      1-TestDSN1 points to .\Databases\FirstDB
      2-TestDSN2 points to .\Databases\SecondDB

    • Compile the program and run it
    • NOTE : The database enclosed in the demo project is in MSAccess 2000 format, you may have to create a one that matches your own access driver.

    Downloads

    Download demo project – 186 Kb
    Download source – 12 Kb

  • More by Author

    Get the Free Newsletter!

    Subscribe to Data Insider for top news, trends & analysis

    Must Read