Visual C++ 6 Unleashed | Writing a Visual C++ ADO Application

Table of Contents

Writing a Visual C++ ADO Application

In this section, a C++ ADO application is developed. This application will show the basic functionality needed for ADO recordsets.

Step 1—Creating Connections and Recordsets ADO Objects

Connections and recordsets are the heart of ADO. A connection provides a link to a database and enables commands to execute, and a recordset uses a connection to retrieve table data for displaying and updating.

To access ADO commands, you need to include two ADO header files, adoid.h and adoint.h, at the top of your header file (Chap23Dialog.h) as follows:

//ADO header files Added by Chuck Wood for ADO support
#include <adoid.h>
#include <adoint.h>

Now you must create ADO COM variables that can be used to store connection information and recordset information. The best way to do this is to use CComPtr smart pointers. These pointers automatically do all your COM garbage collection for you so that you don't need to call routines that close the COM object. These COM smart pointers are shown in gray:

class CChap23Dialog :
    public CAxDialogImpl<CChap23Dialog>
{
private:

/////////////////////////////////////////////////
//Added by Chuck Wood for ADO support
//ADO Connection and recordset variables
    CComPtr<ADORecordset> m_pSet;
    CComPtr<ADOConnection> m_pConn;

/////////////////////////////////////////////////
public:

Because ADO is a COM interface, your application needs to initialize the COM environment for each ADO construct you use. In this case, you need to initialize the connection and the recordset with a call to CoCreateInstance(). The ADO initialization is done inside the dialog box constructor before the DoModal command is issued, as shown in gray:

CChap23Dialog() {

    //Create a new connection
    CoCreateInstance(CLSID_CADOConnection, NULL,
        CLSCTX_INPROC_SERVER, IID_IADOConnection,
        (LPVOID *) &m_pConn);
    //Create a new recordset
    CoCreateInstance(CLSID_CADORecordset, NULL,
        CLSCTX_INPROC_SERVER, IID_IADORecordset,
        (LPVOID *) &m_pSet);

    //Open Dialog box
    DoModal();
}

Step 2—Connecting to a Database Through ADO

After creating the database connection COM instance, you can use it to connect to a database through the ADOConnection.Open() method, as shown in the CChap23Dialog constructor:

CChap23Dialog() {
    //Create a new connection
    CoCreateInstance(CLSID_CADOConnection, NULL,
        CLSCTX_INPROC_SERVER, IID_IADOConnection,
        (LPVOID *) &m_pConn);
    //Create a new recordset
    CoCreateInstance(CLSID_CADORecordset, NULL,
        CLSCTX_INPROC_SERVER, IID_IADORecordset,
        (LPVOID *) &m_pSet);

    //Connect to the database
    if (FAILED(m_pConn->Open(
            (CComBSTR) "VCUnleashed", //ODBC Name
            (CComBSTR) "",    // User ID
            (CComBSTR) "",    // Password
            adOpenUnspecified))) {
        MessageBox("Could not open connection to VCUnleashed", "DB Error");
        m_pConn = NULL;
        return;
    }

    //Open Dialog box
    DoModal();
}

Step 3—Opening an ADO Recordset

Now you are ready to access your ADO database connection to connect to a database. First, you must declare fields that can hold a single row of your recordset. In the example in gray, I've declared three variables to hold the employee name, department, and salary:

/////////////////////////////////////////////////////////////////////////////
//Added by Chuck Wood for ADO support

//database fields
    char m_EmpName[51];
    char m_Dept[11];
    double m_Salary;

//ADO Connection and recordset variables
    CComPtr<ADORecordset> m_pSet;
    CComPtr<ADOConnection> m_pConn;

Next, you call the ADORecordset.Open() method, as shown in gray, to issue your SELECT (or CALL) statement, your open connection, and your options used in creating the recordset:

CChap23Dialog() {
    //Create a new connection
    CoCreateInstance(CLSID_CADOConnection, NULL,
        CLSCTX_INPROC_SERVER, IID_IADOConnection,
        (LPVOID *) &m_pConn);
    //Create a new recordset
    CoCreateInstance(CLSID_CADORecordset, NULL,
        CLSCTX_INPROC_SERVER, IID_IADORecordset,
        (LPVOID *) &m_pSet);
    //Connect to the database
    if (FAILED(m_pConn->Open(
            (CComBSTR) "VCUnleashed", //ODBC Name
            (CComBSTR) "",    //
            (CComBSTR) "",
            adOpenUnspecified))) {
        MessageBox("Could not open connection to VCUnleashed", "DB Error");
        m_pConn = NULL;
        return;
    }

    //Open a new recordset
    if (FAILED(m_pSet->Open(CComVariant((CComBSTR)
    "SELECT EmpName, Dept, Salary FROM Employee"),
            CComVariant(m_pConn),
            adOpenKeyset, adLockOptimistic, adCmdText))) {
        MessageBox("Could not open a recordset", "DB Error");
        m_pSet = NULL;
        return;
    }

    //Open Dialog box
    DoModal();
}

Step 4—Closing the Connection and Recordset

It's important to close the connection and recordset after opening them. Although the COM smart pointers "garbage-collect" the memory used by the COM interface, the database itself might be corrupted or left either unusable or less efficient if not specifically closed by the application. In the following code, you see how the recordset (m_pSet) and the connection (m_pConn) are closed in the destructor for the CChap23Dialog class:

~CChap23Dialog() {
    //Added by Chuck Wood to close the connection and recordset
    if (m_pSet != NULL) {
        m_pSet->Close();
    }
    if (m_pConn != NULL) {
        m_pConn->Close();
    }
}

Step 5—Writing an `UpdateData` Routine for ADO

MFC enables you to call an UpdateData routine that enables you to either update the database with information in the dialog box or update the dialog box with information from the table. In this section, a new UpdateData routine is written to function with ADO.

Step 5a—Retrieving Recordset Field Information

Before writing the UpdateData() routine, you need to understand how to retrieve recordset information. This involves three steps:

Retrieve the ADOFields collection object from the recordset using the ADORecordset.get_Fields() method:
```
CComPtr<ADOFields> pFields = NULL;
m_pSet->get_Fields(&pFields);
```
Retrieve each ADOField from the ADOFields collection using the ADOFields.get_Item() method:
```
CComPtr<ADOField> pEmpName = NULL;
pFields->get_Item(CComVariant(0), &pEmpName);
```

Finally, retrieve information from each ADOField and store it in a CComVariant using the ADOField.get_Value() method:

CComVariant dbValue;    //Variant holder for db values
//Get the value of EmpName
pEmpName->get_Value(&dbValue);
//Store EmpName and get the value of Dept
sprintf(m_EmpName, "%S", dbValue.bstrVal);

This functionality can be viewed in the retrieveRecordsetFields() function:

HRESULT retrieveRecordsetFields() {
    //Written by Chuck Wood for Visual C++ Unleashed
    //Retrieves recordset information
    HRESULT hr;        //Returns error
    CComPtr<ADOFields> pFields = NULL;
    CComPtr<ADOField> pEmpName = NULL;
    CComPtr<ADOField> pDept = NULL;
    CComPtr<ADOField> pSalary = NULL;
    //Get fields
    hr = m_pSet->get_Fields(&pFields);
    //Get individual fields
    if (!FAILED(hr))    //Get EmpName (field 0)
        hr = pFields->get_Item(CComVariant(0), &pEmpName);
    if (!FAILED(hr))    //Get Dept (field 1)
        hr = pFields->get_Item(CComVariant(1), &pDept);
    if (!FAILED(hr))    //Get Salary (field 2)
        hr = pFields->get_Item(CComVariant(2), &pSalary);
    if (FAILED(hr))    //Return if error
        return hr;
    //Put values in individual fields
    CComVariant dbValue;    //Variant holder for db values
    //Get the value of EmpName
    hr = pEmpName->get_Value(&dbValue);
    if (!FAILED(hr)) {
         //Store EmpName and get the value of Dept
        sprintf(m_EmpName, "%S", dbValue.bstrVal);
        hr = pDept->get_Value(&dbValue);
    }
    if (!FAILED(hr)) {
         //Store dept and get the value of Salary
        sprintf(m_Dept, "%S", dbValue.bstrVal);
        hr = pSalary->get_Value(&dbValue);
    }
    if (!FAILED(hr)) { //Store salary
        //Currency is stored a little weird.  Check out MSDN.
        m_Salary = (double) dbValue.cyVal.int64 / 10000.0;
    }
    return hr;
}

Note

Some providers enable you to cache any changes made to the recordset locally and send them to the data source in one batch. This is known as batch update mode. If batch update mode is not supported, you will always be working in immediate update mode. You can determine whether the provider you are using supports batch update mode by calling the Supports() method of the recordset, with CursorOptions set to adUpdateBatch. To send the batch of updates to the datasource, you need to call the UpdateBatch() method of the recordset:

HRESULT UpdateBatch ( enum AffectEnum AffectRecords );

The AffectEnum parameter is used to determine which records will be updated in the datasource. This can be set to one of the following constants:

adAffectCurrent sends changes for only the current row.
adAffectGroup sends changes for all modified records that satisfy the Filter property.
adAffectAll sends all pending changes to the data source.

If batch updates interest you, check out the MSDN documentation for more information.

Step 5b—Updating Recordset Field Information

An UpdateData() method must not only retrieve information from the recordset, but also must allow updates to the database from the current dialog box. To update recordset information, you must perform the following steps:

Retrieve the ADOFields collection object from the recordset using the ADORecordset.get_Fields() method:
```
CComPtr<ADOFields> pFields = NULL;
m_pSet->get_Fields(&pFields);
```
Retrieve each ADOField from the ADOFields collection using the ADOFields.get_Item() method:
```
CComPtr<ADOField> pEmpName = NULL;
pFields->get_Item(CComVariant(0), &pEmpName);
```
Store information in each ADOField using the ADOField.put_Value() method:
```
pEmpName->put_Value(CComVariant(m_EmpName));
```
Update the recordset using the ADORecordset.Update() method. This requires a variant parameter, but you can pass "null variants" by creating a variant with VT_ERROR for the vt member and DISP_E_PARAMNOTFOUND for the scode member:
```
//Initialize nullVariant
VARIANT nullVariant;
nullVariant.vt = VT_ERROR;
nullVariant.scode = DISP_E_PARAMNOTFOUND;
//Perform update
m_pSet->Update(nullVariant, nullVariant);
```

This functionality can be viewed in the updateRecordset() function:

HRESULT updateRecordset() {
    //Written by Chuck Wood for Visual C++ Unleashed
    //Updates the database with values in the class variables
    HRESULT hr;        //Returns error
    CComPtr<ADOFields> pFields = NULL;
    CComPtr<ADOField> pEmpName = NULL;
    CComPtr<ADOField> pDept = NULL;
    CComPtr<ADOField> pSalary = NULL;
    //Get fields
    hr = m_pSet->get_Fields(&pFields);
    //Get individual fields
    if (!FAILED(hr))    //Get EmpName (field 0)
        hr = pFields->get_Item(CComVariant(0), &pEmpName);
    if (!FAILED(hr))    //Get Dept (field 1)
        hr = pFields->get_Item(CComVariant(1), &pDept);
    if (!FAILED(hr))    //Get Salary (field 2)
        hr = pFields->get_Item(CComVariant(2), &pSalary);
    //Put values in individual fields
    if (!FAILED(hr))    //Set the value of EmpName
        hr = pEmpName->put_Value(CComVariant(m_EmpName));
    if (!FAILED(hr))    //Set the value of Dept
        hr = pDept->put_Value(CComVariant(m_Dept));
    if (!FAILED(hr))    //Set the value of Salary
        hr = pSalary->put_Value(CComVariant(m_Salary));
    //Update database
    if (!FAILED(hr)) {
        //Initialize nullVariant
        VARIANT nullVariant;
        nullVariant.vt = VT_ERROR;
        nullVariant.scode = DISP_E_PARAMNOTFOUND;
        //Perform update
        hr = m_pSet->Update(nullVariant, nullVariant);
    }
    return hr;
}

Step 5c—Pulling It Together in One `UpdateData()` Routine

At last you are ready to write the UpdateData function. You can use the GetDlgItemText() and SetDlgItemText() to retrieve or display, respectively, information to and from the dialog box.

You can call the retrieveRecordsetFields() and the updateRecordset() functions as appropriate to query or update information in the class variables. Here is the completed UpdateData() function:

void UpdateData(BOOL saveChanges=TRUE) {
    //Written by Chuck Wood for Visual C++ Unleashed
    //Mimics the MFC UpdateData function
    if (saveChanges) {
        //Write data from the dialog box to the database
        VARIANT_BOOL vb;
        //Check to see whether recordset is positioned at BOF
        m_pSet->get_BOF(&vb);
        if (vb) return;
        //Check to see whether recordset is positioned at EOF
        m_pSet->get_EOF(&vb);
        if (vb) return;
        //Check to see whether current record is deleted
        EditModeEnum eme;
        m_pSet->get_EditMode(&eme);
        if (eme == adEditDelete) return;
        //Continue with update
        char salaryHolder[25];
        GetDlgItemText(IDC_EMPNAME, m_EmpName, 51);
        GetDlgItemText(IDC_DEPT, m_Dept, 11);
        GetDlgItemText(IDC_SALARY, salaryHolder, 25);
        m_Salary = atof(salaryHolder);
        if (FAILED(updateRecordset())) {
            showMessage ("Update failed");
            MessageBox("Could not update employee table","Update error");
        }
    }
    else {
        //Write data from the database to the dialog box
        if (FAILED(retrieveRecordsetFields())) {
            showMessage ("Display fields failed");
            MessageBox("Could not retrieve information from the  employee table","Query error");
        }
        else {
            char salaryHolder[25];
            sprintf(salaryHolder, "%.2f", m_Salary);
            SetDlgItemText(IDC_EMPNAME, m_EmpName);
            SetDlgItemText(IDC_DEPT, m_Dept);
            SetDlgItemText(IDC_SALARY, salaryHolder);
        }
    }
}

Now you can control database or dialog box updates simply by calling the UpdateData() function as you would with MFC. In the following code, you can see that the UpdateData() function is called before allowing the dialog box to close:

LRESULT OnCancel(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled)
{
    UpdateData();     //Added by Chuck Wood to update the database
    EndDialog(wID);
    return 0;
}

Step 6—Navigating Through a Recordset

Writing the UpdateData function was a little time-consuming, but it sure makes it worth it when navigating through a recordset. The four move functions—MoveFirst(), MovePrevious(), MoveNext(), and MoveLast()—are used to scroll through an ADO recordset. In our example, each move function is called a menu function. This enables the user to control navigation through the recordset. Use the following steps for each move function:

Update the current information in case any changes are made using the UpdateData() function.
Scroll to the appropriate position in the database indicated by the user.
Display new information in the dialog box using the UpdateData(FALSE) function.

The move functions are shown in the following code. Basic error trapping is added to display relevant information to the user:

//ADO Move functionality added by Chuck Wood
LRESULT OnFirst(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    VARIANT_BOOL vb;
    UpdateData();
    m_pSet->MoveFirst();
    m_pSet->get_BOF(&vb);
    if (vb) {
        showMessage("No records found");
    }
    else {
        UpdateData(FALSE);
    }
    return 0;
}
LRESULT OnPrev(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    VARIANT_BOOL vb;
    showMessage("");
    UpdateData();
    m_pSet->MovePrevious();
    m_pSet->get_BOF(&vb);
    if (vb) {
        showMessage("No previous records found");
        return OnFirst(wNotifyCode, wID, hWndCtl, bHandled);
    }
    else {
        UpdateData(FALSE);
    }
    return 0;
}
LRESULT OnNext(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    VARIANT_BOOL vb;
    showMessage("");
    UpdateData();
    m_pSet->MoveNext();
    m_pSet->get_EOF(&vb);
    if (vb) {
        showMessage("No more records found");
        return OnLast(wNotifyCode, wID, hWndCtl, bHandled);
    }
    else {
        UpdateData(FALSE);
    }
    return 0;
}
LRESULT OnLast(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    VARIANT_BOOL vb;
    UpdateData();
    m_pSet->MoveLast();
    m_pSet->get_EOF(&vb);
    if (vb) {
        showMessage("No records found");
    }
    else {
        UpdateData(FALSE);
    }
    return 0;
}

Step 7—Inserting into the Recordset

ADO inserts involve simply calling the ADORecordset.AddNew() method. After this method is called, a new row is added to the recordset, and an ADORecordset.Update() method call updates the database with the new record. Any move away from the newly created row in the recordset aborts the add, and a call to the ADORecordset. CancelUpdate() method also cancels the insert.

In a recordset browser such as the one in our example, inserting a new record involves three steps:

Update the current record with a call to the UpdateData() function.
Clear the dialog box fields by using the SetDlgItemText() function.
Call the ADORecordset.AddNew() method.

The implementation for these steps can be seen in the following code:

LRESULT OnInsert(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    UpdateData();
    //Clear dialog box fields
    SetDlgItemText(IDC_EMPNAME, "");
    SetDlgItemText(IDC_DEPT, "");
    SetDlgItemText(IDC_SALARY, "");
    //Initialize nullVariant
    VARIANT nullVariant;
    nullVariant.vt = VT_ERROR;
    nullVariant.scode = DISP_E_PARAMNOTFOUND;
    HRESULT hr = m_pSet->AddNew(nullVariant, nullVariant);
    if (FAILED(hr)) {
        MessageBox("Cannot insert a new record", "Database Error");
        showMessage("Cannot insert a new record");
        return OnLast(wNotifyCode, wID, hWndCtl, bHandled);
    }
    return 0;
}

Step 8—Deleting from the Recordset

You need to be careful when deleting rows from a database. Often, the user might have hit the delete menu option by accident. Also, the user might often be deleting a new record before the ADORecordset.Update() is called. Deletes involve several steps:

Check to make sure that a delete is desired.
Check to make sure that you aren't in the middle of an AddNew command. If you are, simply cancel the add.
Delete the current record.
Now that the current record is deleted, you need to move the record pointer off the deleted record. I usually try the following steps:
1. Go to the next record.
2. If there is no next record, go to the last record.
3. If there are no more records, start a new add

The following OnDelete() function implements these steps:

LRESULT OnDelete(WORD wNotifyCode, WORD wID, HWND hWndCtl, BOOL& bHandled) {
    showMessage("");
    if (MessageBox("Are you sure you want to delete?",
            "Confirm Delete",
            MB_YESNO) != IDYES) {
        return 0;
    }
    EditModeEnum eme;
    m_pSet->get_EditMode(&eme);
    if (eme == adEditAdd) {    //Just cancel add
        m_pSet->CancelUpdate();
    }
    else if (FAILED(m_pSet->Delete(adAffectCurrent))) {
        MessageBox("Could not delete record", "Database Error");
        showMessage("Could not delete record");
    }
    if (FAILED(m_pSet->MoveNext())) {
        if (FAILED(m_pSet->MoveLast())) {
            return OnInsert(wNotifyCode, wID, hWndCtl, bHandled);
        }
    }
    UpdateData(FALSE);
    return 0;
}

After implementing these eight steps, you should have a fully functional database application, as shown in Figure 23.1.

Figure 23.1 Database applications can be easily created using ADO, although OLE DB is even easier and preferred.

Previous Section

Processing ADO Errors | Next Section