MFC - Database Classes

A database is a collection of information that is organized so that it can easily be accessed, managed, and updated. The MFC database classes based on ODBC are designed to provide access to any database for which an ODBC driver is available. Because the classes use ODBC, your application can access data in many different data formats and different local/remote configurations.

You do not have to write special-case code to handle different database management systems (DBMSs). As long as your users have an appropriate ODBC driver for the data they want to access, they can use your program to manipulate data in tables stored there. A data source is a specific instance of data hosted by some database management system (DBMS). Examples include Microsoft SQL Server, Microsoft Access, etc.

CDatabase

MFC provides a class CDatabase which represents a connection to a data source, through which you can operate on the data source. You can have one or more CDatabase objects active at a time in your application.

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

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

Retrieve Data From DB

Step 2 − Add click 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.

Employees Table

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.

Insert Queue

Retrieve Record

To retrieve the above table in MFC application, 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::OnBnClickedButtonRead() {
   // 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 list 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 : public CDialogEx {
   // Construction
   public:
      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()
   public:
      CListCtrl m_ListControl;
      afx_msg void OnBnClickedButtonRead();
};

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

Retrieve Record

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

Retrieve Record

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 click event.

void CMFCDatabaseDemoDlg::OnBnClickedButtonRead() {
   // 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_listCtrl,LVS_EX_GRIDLINES);

      // Column width and heading
      m_listCtrl.InsertColumn(0,L"Emp ID",LVCFMT_LEFT,-1,0);
      m_listCtrl.InsertColumn(1,L"Name",LVCFMT_LEFT,-1,1);
      m_listCtrl.InsertColumn(2, L"Age", LVCFMT_LEFT, -1, 1);
      m_listCtrl.SetColumnWidth(0, 120);
      m_listCtrl.SetColumnWidth(1, 200);
      m_listCtrl.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 list control
         iRec = m_listCtrl.InsertItem(0,strID,0);
         m_listCtrl.SetItemText(0,1,strName);
         m_listCtrl.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.

Retrieve Record

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

Update Record

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 click event.

void CMFCDatabaseDemoDlg::OnBnClickedButtonRead() {
   // 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_listCtrl,LVS_EX_GRIDLINES);
      // Column width and heading
      m_listCtrl.InsertColumn(0,L"Emp ID",LVCFMT_LEFT,-1,0);
      m_listCtrl.InsertColumn(1,L"Name",LVCFMT_LEFT,-1,1);
      m_listCtrl.InsertColumn(2, L"Age", LVCFMT_LEFT, -1, 1);
      m_listCtrl.SetColumnWidth(0, 120);
      m_listCtrl.SetColumnWidth(1, 200);
      m_listCtrl.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 list control
         iRec = m_listCtrl.InsertItem(0,strID,0);
         m_listCtrl.SetItemText(0,1,strName);
         m_listCtrl.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.

Retrieve Record

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

Update Record
Advertisements