CDatabaseExt - A Simple Way to Connect to a Database and Retrieve Data

• Download demo 83.8 KB

Introduction

This class is designed to connect to a database through ODBC and perform basic operations such as inserts, updates, deletes (and so on), and data is retrieved into a string array or into a CDBVariant array. Also, this class could serve like a CDatabase pointer to your own derived (or not) CRecordset class.

The connection string for this control could be provided through one of the constructors or through a specific method: CDatabaseExt::InitConnectionString(...). Also, you can set up a Registry folder from where the control can automatically construct the connection string. In this case, all Registry keys have the 'DS' prefix: e.g., DSName, DSServer, DSDatabase, etc. For more details, see the demo project on the CDatasourceDlg class code.

The three constructors are: CDataBaseExt() which gets the connection string from the default Registry entry and the default recordset type (CRecordset::dynaset); CDatabaseExt(BOOL bConnectMode, LPCTSTR lpszRegistryEntry = NULL) where you can say what type of connection you need (connected all time or not) and your Registry entry where you have information about the connection string and recordset type; CDatabaseExt(CString sConnectionString, UINT nRsType = CRecordset::dynaset, BOOL bConnectMode = TRUE) where you can provide the connection string, recordset type, and connection mode. Of course, all this stuff can be set up later, after the class construction (connection mode, connection string, recordset type, or Registry entry).

Using the Code

In order to use this control, you have to add to your project four files: DatabaseExt.h, DatabaseExt.cpp, Crypt.h, and Crypt.cpp, the last two of them are used to encrypt the user password in the Registry. Then you simply #include "DatabaseExt.h" in the file where you intend to use the control. In the sample project, I declare in the CMyWinApp class a CDatabaseExt object member to have a database connection for the entire application:

//
// TestDatabase.h : main header file for the TESTDATABASE application
//
#include "DatabaseExt.h"

class CTestDatabaseApp : public CWinApp
{
public:
    CTestDatabaseApp();
    CDatabaseExt* GetDB(){return m_pDB;}

....
....
private:
    CDatabaseExt* m_pDB;
};

and initialize the control in InitInstance:

BOOL CTestDatabaseApp::InitInstance()
{
    m_pDB = new CDatabaseExt(_T("MyConnectionString"),CRecordset::snapshot);
}

int CTestDatabaseApp::ExitInstance() 
{
    // TODO: Add your specialized code here and/or call the base class

    if(m_pDB)
        delete m_pDB;

    return CWinApp::ExitInstance();
}

Suppose you need to get some data in your document class:

BOOL CTestDatabaseDoc::GetData(int& nCols,CStringArray& saItem)
{
    CString sSQL;
    sSQL.Format("SELECT * FROM mytable");

    BOOL bRet = theApp.GetDB()->GetRecordset(sSQL,saItem);
    nCols = theApp.GetDB()->GetFieldCount();
    theApp.m_sError = theApp.GetDB()->GetLastError();

    return bRet;
}

If you prefer CDBVariant instead of CStringArray, you will find a sample used in the CTestDatabaseDoc class:

BOOL CTestDatabaseDoc::GetData(int& nCols,CStringArray& saItem)
{
    CString sSQL;
    sSQL.Format("SELECT * FROM your_table");
    CTypedPtrArray<<CPtrArray,CDBVariant*>> arrItem;
    BOOL bRet = theApp.GetDB()->GetRecordset(sSQL,arrItem);

    while(arrItem.GetSize())
    {
        CDBVariant* pDBVariant = (CDBVariant*)arrItem.GetAt(0);
        // do whatever you want with pDBVariant, [for sample add items in saItem]
        delete pDBVariant;
        arrItem.RemoveAt(0);
    }
    nCols = theApp.GetDB()->GetFieldCount();
    theApp.m_sError = theApp.GetDB()->GetLastError();

    return bRet;
}

and saItem will contain all the data retrieved. Here you have to replace the SELECT ... statement with one of yours.

Another way to get data is to using CRecordset, just like that:

	CRecordset* pRs = new CRecordset(theApp.GetDB());
	bRet = theApp.GetDB()->GetRecordset(pRs, sSQL);
// do what ever you want with your CRecordset, and
	pRs->Close();
	delete pRs;
	pRs = NULL;

now, you can do whatever you want with your retrieved recordset ... But, perhaps you want to get your derived CRecordset ... let say you have a CTable, derived from CRecordset ... yes, you can have any dataset using CDatabaseExt:

	CTables* pRs = new CTables(theApp.GetDB());
	bRet = theApp.GetDB()->GetRecordset(pRs, NULL);
// do what ever you want with your CTables, and
	pRs->Close();
	delete pRs;
	pRs = NULL;

A sample code with all ways that you can use CDatabaseExt, is in CTestDatabaseDoc::GetData(...).

The demo project contains the CDataSourceDlg class that could setup the connection string values into the Registry. Here, through successive trials, I have a proper connection string to match your database. Here too, you have to replace the trial SELECT ... statements with one of yours ...

Also, in the CDataSourceDlg class implementation, you will find a very useful class CEditExt, which has a very interesting feature: can have IAutoComplete behaviour, can set up char/number filter, and limitation, can set up a custom color for text and background, etc.

• 3 Oct 2016 - updated CDatabaseExt class source code and CDataSourceDlg in order to get datasources as user DSN or system DSN
• 20 June 2017 - updated source code for CDatabaseExt
• 15 July 2021 - Update source code for CDatabaseExt
• 13 Dec 2021 - Update source code for CDatabaseExt
• 1 Feb 2022 - Update souce code for CDatabaseEx and CDatabaseExt