MFC - Database Classes

Let us look into a simple example by creating a new MFC dialog based apppcation.

Step 1 − Change the caption of TODO pne to Retrieve Data from Database and drag one button and one List control as shown in the following snapshot.

Step 2 − Add cpck event handler for button and control variable m_ListControl for List Control.

Step 3 − We have simple database which contains one Employees table with some records as shown in the following snapshot.

Step 4 − We need to include the following headers file so that we can use CDatabase class.

#include "odbcinst.h"
#include "afxdb.h"

Insert Query

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Step 1 − To add new records, we will use the ExecuteSQL() function of CDatabase class as shown in the following code.

CDatabase database;
CString SqlString;
CString strID, strName, strAge;
CString sDriver = L"MICROSOFT ACCESS DRIVER (*.mdb)";
CString sDsn;
CString sFile = L"D:\Test.mdb";
// You must change above path if it s different
int iRec = 0;

// Build ODBC connection string
sDsn.Format(L"ODBC;DRIVER={%s};DSN=  ;DBQ=%s", sDriver, sFile);
TRY {
   // Open the database
   database.Open(NULL,false,false,sDsn);

   SqlString = "INSERT INTO Employees (ID,Name,age) VALUES (5, Sanjay ,69)";
   database.ExecuteSQL(SqlString);
   // Close the database
   database.Close();
}CATCH(CDBException, e) {
   // If a database exception occured, show error msg
   AfxMessageBox(L"Database error: " + e→m_strError);
}
END_CATCH;

Step 2 − When the above code is compiled and executed, you will see that a new record is added in your database.

Retrieve Record

To retrieve the above table in MFC apppcation, we implement the database related operations in the button event handler as shown in the following steps.

Step 1 − To use CDatabase, construct a CDatabase object and call its Open() function. This will open the connection.

Step 2 − Construct CRecordset objects for operating on the connected data source, pass the recordset constructor a pointer to your CDatabase object.

Step 3 − After using the connection, call the Close function and destroy the CDatabase object.

void CMFCDatabaseDemoDlg::OnBnCpckedButtonRead() {
   // TODO: Add your control notification handler code here
   CDatabase database;
   CString SqlString;
   CString strID, strName, strAge;
   CString sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
   CString sFile = L"D:\Test.mdb";
   // You must change above path if it s different
   int iRec = 0;

   // Build ODBC connection string
   sDsn.Format("ODBC;DRIVER={%s};DSN=  ;DBQ=%s",sDriver,sFile);
   TRY {
      // Open the database
      database.Open(NULL,false,false,sDsn);

      // Allocate the recordset
      CRecordset recset( &database );

      // Build the SQL statement
      SqlString = "SELECT ID, Name, Age " "FROM Employees";

      // Execute the query
	  
      recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);
      // Reset List control if there is any data
      ResetListControl();
      // populate Grids
      ListView_SetExtendedListViewStyle(m_ListControl,LVS_EX_GRIDLINES);

      // Column width and heading
      m_ListControl.InsertColumn(0,"Emp ID",LVCFMT_LEFT,-1,0);
      m_ListControl.InsertColumn(1,"Name",LVCFMT_LEFT,-1,1);
      m_ListControl.InsertColumn(2, "Age", LVCFMT_LEFT, -1, 1);
      m_ListControl.SetColumnWidth(0, 120);
      m_ListControl.SetColumnWidth(1, 200);
      m_ListControl.SetColumnWidth(2, 200);

      // Loop through each record
      while( !recset.IsEOF() ) {
         // Copy each column into a variable
         recset.GetFieldValue("ID",strID);
         recset.GetFieldValue("Name",strName);
         recset.GetFieldValue("Age", strAge);

         // Insert values into the pst control
         iRec = m_ListControl.InsertItem(0,strID,0);
         m_ListControl.SetItemText(0,1,strName);
         m_ListControl.SetItemText(0, 2, strAge);

         // goto next record
         recset.MoveNext();
      }
      // Close the database
      database.Close();
   }CATCH(CDBException, e) {
      // If a database exception occured, show error msg
      AfxMessageBox("Database error: "+e→m_strError);
   }
   END_CATCH; 
}

// Reset List control
void CMFCDatabaseDemoDlg::ResetListControl() {
   m_ListControl.DeleteAllItems();
   int iNbrOfColumns;
   CHeaderCtrl* pHeader = (CHeaderCtrl*)m_ListControl.GetDlgItem(0);
   if (pHeader) {
      iNbrOfColumns = pHeader→GetItemCount();
   }
   for (int i = iNbrOfColumns; i >= 0; i--) {
      m_ListControl.DeleteColumn(i);
   }
}

Step 4 − Here is the header file.

// MFCDatabaseDemoDlg.h : header file
//

#pragma once
#include "afxcmn.h"


// CMFCDatabaseDemoDlg dialog
class CMFCDatabaseDemoDlg : pubpc CDialogEx {
   // Construction
   pubpc:
      CMFCDatabaseDemoDlg(CWnd* pParent = NULL);    // standard constructor

   // Dialog Data
   #ifdef AFX_DESIGN_TIME
      enum { IDD = IDD_MFCDATABASEDEMO_DIALOG };
   #endif

   protected:
      virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support
      void ResetListControl();

   // Implementation
   protected:
      HICON m_hIcon;

      // Generated message map functions
      virtual BOOL OnInitDialog();
      afx_msg void OnPaint();
      afx_msg HCURSOR OnQueryDragIcon();
      DECLARE_MESSAGE_MAP()
   pubpc:
      CListCtrl m_ListControl;
      afx_msg void OnBnCpckedButtonRead();
};

Step 5 − When the above code is compiled and executed, you will see the following output.

Step 6 − Press the Read button to execute the database operations. It will retrieve the Employees table.

Update Record

The SQL UPDATE Query is used to modify the existing records in a table. You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be affected.

Step 1 − Let us look into a simple example by updating the Age where ID is equal to 5.

SqlString = L"UPDATE Employees SET Age = 59 WHERE ID = 5;";
database.ExecuteSQL(SqlString);

Step 2 − Here is the complete code of button cpck event.

void CMFCDatabaseDemoDlg::OnBnCpckedButtonRead() {
   // TODO: Add your control notification handler code here
   CDatabase database;
   CString SqlString;
   CString strID, strName, strAge;
   CString sDriver = L"MICROSOFT ACCESS DRIVER (*.mdb)";
   CString sDsn;
   CString sFile =
      L"C:\Users\Muhammad.Waqas\Downloads\Compressed\ReadDB_demo\Test.mdb";
   // You must change above path if it s different
   int iRec = 0;

   // Build ODBC connection string
   sDsn.Format(L"ODBC;DRIVER={%s};DSN=  ;DBQ=%s", sDriver, sFile);
   TRY {
      // Open the database
      database.Open(NULL,false,false,sDsn);

      // Allocate the recordset
      CRecordset recset(&database);

      SqlString = L"UPDATE Employees SET Age = 59 WHERE ID = 5;";

      database.ExecuteSQL(SqlString);

      SqlString = "SELECT ID, Name, Age FROM Employees";

      // Build the SQL statement
      SqlString = "SELECT ID, Name, Age FROM Employees";

      // Execute the query
      recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);

      // Reset List control if there is any data
      ResetListControl();
      // populate Grids
      ListView_SetExtendedListViewStyle(m_pstCtrl,LVS_EX_GRIDLINES);

      // Column width and heading
      m_pstCtrl.InsertColumn(0,L"Emp ID",LVCFMT_LEFT,-1,0);
      m_pstCtrl.InsertColumn(1,L"Name",LVCFMT_LEFT,-1,1);
      m_pstCtrl.InsertColumn(2, L"Age", LVCFMT_LEFT, -1, 1);
      m_pstCtrl.SetColumnWidth(0, 120);
      m_pstCtrl.SetColumnWidth(1, 200);
      m_pstCtrl.SetColumnWidth(2, 200);

      // Loop through each record
      while (!recset.IsEOF()) {
         // Copy each column into a variable
         recset.GetFieldValue(L"ID",strID);
         recset.GetFieldValue(L"Name",strName);
         recset.GetFieldValue(L"Age", strAge);

         // Insert values into the pst control
         iRec = m_pstCtrl.InsertItem(0,strID,0);
         m_pstCtrl.SetItemText(0,1,strName);
         m_pstCtrl.SetItemText(0, 2, strAge);

         // goto next record
         recset.MoveNext();
      }

      // Close the database
      database.Close();
   }CATCH(CDBException, e) {
      // If a database exception occured, show error msg
      AfxMessageBox(L"Database error: " + e→m_strError);
   }
   END_CATCH;
}

Step 3 − When the above code is compiled and executed, you will see the following output.

Step 4 − Press the Read button to execute the database operations. It will retrieve the following Employees table.

Step 5 − You can now see that age is updated from 69 to 59.

Delete Record

The SQL DELETE Query is used to delete the existing records from a table. You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

Step 1 − Let us look into a simple example by deleting the record where ID is equal to 3.

SqlString = L"DELETE FROM Employees WHERE ID = 3;";

database.ExecuteSQL(SqlString);

Step 2 − Here is the complete code of button cpck event.

void CMFCDatabaseDemoDlg::OnBnCpckedButtonRead() {
   // TODO: Add your control notification handler code here
   CDatabase database;
   CString SqlString;
   CString strID, strName, strAge;
   CString sDriver = L"MICROSOFT ACCESS DRIVER (*.mdb)";
   CString sDsn;
   CString sFile =
       L"C:\Users\Muhammad.Waqas\Downloads\Compressed\ReadDB_demo\Test.mdb";

   // You must change above path if it s different
   int iRec = 0;

   // Build ODBC connection string
   sDsn.Format(L"ODBC;DRIVER={%s};DSN=  ;DBQ=%s", sDriver, sFile);
   TRY {
      // Open the database
      database.Open(NULL,false,false,sDsn);

      // Allocate the recordset
      CRecordset recset(&database);

      SqlString = L"DELETE FROM Employees WHERE ID = 3;";

      database.ExecuteSQL(SqlString);

      SqlString = "SELECT ID, Name, Age FROM Employees";

      // Build the SQL statement
      SqlString = "SELECT ID, Name, Age FROM Employees";

      // Execute the query
      recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);

      // Reset List control if there is any data
      ResetListControl();
      // populate Grids
      ListView_SetExtendedListViewStyle(m_pstCtrl,LVS_EX_GRIDLINES);
      // Column width and heading
      m_pstCtrl.InsertColumn(0,L"Emp ID",LVCFMT_LEFT,-1,0);
      m_pstCtrl.InsertColumn(1,L"Name",LVCFMT_LEFT,-1,1);
      m_pstCtrl.InsertColumn(2, L"Age", LVCFMT_LEFT, -1, 1);
      m_pstCtrl.SetColumnWidth(0, 120);
      m_pstCtrl.SetColumnWidth(1, 200);
      m_pstCtrl.SetColumnWidth(2, 200);

      // Loop through each record
      while (!recset.IsEOF()) {
         // Copy each column into a variable
         recset.GetFieldValue(L"ID",strID);
         recset.GetFieldValue(L"Name",strName);
         recset.GetFieldValue(L"Age", strAge);

         // Insert values into the pst control
         iRec = m_pstCtrl.InsertItem(0,strID,0);
         m_pstCtrl.SetItemText(0,1,strName);
         m_pstCtrl.SetItemText(0, 2, strAge);

         // goto next record
         recset.MoveNext();
      }
      // Close the database
      database.Close();
   }CATCH(CDBException, e) {
      // If a database exception occured, show error msg
      AfxMessageBox(L"Database error: " + e→m_strError);
   }
   END_CATCH;
}

Step 3 − When the above code is compiled and executed, you will see the following output.

Step 4 − Press the Read button to execute the database operations. It will retrieve the Employees table.