For software development, in fact, it means dealing with data constantly, so the operation of database is indispensable. Next, it introduces how to use ADO to establish ODBC data source to access MySQL database in VC development.

From the way of database programming I come into contact with, I think it is difficult and error-prone to develop and connect database in vc. In android, the system has sqlite database, which can be operated with database only by using the abstract class of SQLiteOpenHelper. In java, mysql database is connected with jdbc, and the corresponding jar interface is downloaded to transfer to the database type. User name and password are used to operate the database. But it is very complicated to connect the database with ado. Next, let's see how to connect the database with ado.

I. Installing mysql

First, you need to make sure that mysql database is installed on your computer, and mysql is successfully used with username and password. How to install and configure mysql is not described in detail here.

If you don't know how to install and configure mysql, you can refer to this address: Click to open the link

II. ADO Connection to MySql

1. Import the ado database, add the following statement in your program to import the ado database

1. //no_namespace rename("EOF", "adoEOF") prevents name duplication and renames EOF as adoEOF  
2.   
3. #import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF")   

// no_namespace rename("EOF", "adoEOF") prevents name duplication and renames EOF to adoEOF.

#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace rename("EOF", "adoEOF") 

2. Initialization of com environment

//Initialization of com environment
AfxOleInit();

3. Create a data table structure to hold the content retrieved from the database
Assuming that the data table has only two fields: user ID and user name

typedef struct _OBJ_USER
{
	int User_ID;
	TCHAR User_Name[32];
}OBJ_USER;

4. Create Class operator for Operating Database

class CDataOperator
{
public:
	CDataOperator();
	~CDataOperator();
public:
	//Open the specified database
	BOOL OpenDatabase(CString strDbName, CString strUserName, CString strUserPwd);
public:
	//Execute sql statements, add data, delete data
	BOOL ExecuteSQL(CString sql);
	//Query data
	BOOL Select_From_User(vector<OBJ_USER> &vecObjUser);
	//Insert data, you can insert pictures, binary data (big data)
	BOOL Insert_Into_User(OBJ_USER &objUser);
	//Update data to update large data
	BOOL Update_For_User(OBJ_USER &objUser);
public:
	//Connection object
	_ConnectionPtr m_pConnection;
};

5. Definition of operator class

CDataOperator::CDataOperator()
{
	try
	{
		//Creating Connection Objects
		HRESULT hr = m_pConnection.CreateInstance(_T("ADODB.Connection"));
		if (FAILED(hr))
		{
			m_pConnection = NULL;
		}
	}
	catch (_com_error &e)
	{
		e.ErrorMessage();
	}
}

CDataOperator::~CDataOperator()
{
    if (m_pConnection)
    {
        try
        {
            //Close the connection object
            HRESULT hr = m_pConnection->Close();
        }
        catch (_com_error &e)
        {
            e.ErrorMessage();
        }
        //Release connection objects
        m_pConnection.Release();
        m_pConnection = NULL;
    }
}

//Operation of opening database
BOOL CDataOperator::OpenDatabase(CString strDbName, CString strUserName, CString strUserPwd)
{
    if (NULL == m_pConnection)
    {
        return FALSE;
    }
    if (m_pConnection)
    {
        try
        {
            CString strConnectionName;
            strConnectionName.Format(_T("DATABASE=%s;DSN=myodbc;OPTION=0;PWD=%s;PORT=0;SERVER=localhost;UID=%s;"),
                strDbName, strUserPwd, strUserName);
            HRESULT hr = m_pConnection->Open(_bstr_t(strConnectionName.GetBuffer(0)),
                _T(""), _T(""), -1);
            if (FAILED(hr))
            {
                m_pConnection = NULL;
            }
        }
        catch (_com_error &e)
        {
            e.ErrorInfo();
            return FALSE;
        }
    }
    return true;
}

Method of obtaining connection strings:

strConnectionName.Format(_T("DATABASE=%s;DSN=myodbc;OPTION=0;PWD=%s;PORT=0;SERVER=localhost;UID=%s;"),
                strDbName, strUserPwd, strUserName);

The information contained in the connection string includes the type of database, the username and password of the database. In this case, it is similar to java connecting database with jdbc to get the connection string:

"DATABASE=%s;DSN=myodbc;OPTION=0;PWD=%s;PORT=0;SERVER=localhost;UID=%s;" is as follows:

(1) To connect to MySQL database, you need to install mysql-connector-odbc-3.51.26-win32.ini first.
(2) Create a new txt file with the suffix name changed to. udl, double-click open
(3) Provider - > Microsoft OLE DB Provider for ODBC Drivers, click'Next'and select'Use Connection String'.
Click Compile, Select Machine Data Source, Click New, Click Next, Select MYSQL (if you install the application in the first step, there will be MYSQL driver) and Click Next.
Then fill in the corresponding contents, database, server, user name, password, etc. which need to be connected, click the test button, test whether the connection is successful, connect successfully, save.
(4) Open the file with notepad and get the connection string.

6. Interface of CDataOperator class to perform sql statement operation

BOOL CDataOperator::ExecuteSQL(CString sql)
{
	if (NULL == m_pConnection)
	{
		return FALSE;
	}
	if (m_pConnection)
	{
		try
		{
			HRESULT hr = m_pConnection->Execute(_bstr_t(sql), NULL, 1);
			if (FAILED(hr))
			{
				m_pConnection = NULL;
			}
		}
		catch (_com_error &e)
		{
			e.ErrorMessage();
			return FALSE;
		}
	}
	return true;
}

7. Getting data from a database

BOOL CDataOperator::Select_From_User(vector<OBJ_USER> &vecObjUser)
{
	if (NULL == m_pConnection)
		return FALSE;
	//Recordset object
	_RecordsetPtr m_pRecordSet;
	HRESULT hr = m_pRecordSet.CreateInstance(_T("ADODB.Recordset"));
	if (FAILED(hr))
	{
		return FALSE;
	}
	//Clear the data before getting it
	vecObjUser.clear();
	CString strSQL = _T("select User_ID, User_Name from user");
	hr = m_pRecordSet->Open(_bstr_t(strSQL),m_pConnection.GetInterfacePtr(),
		adOpenStatic, adLockOptimistic, adCmdText);
	if (FAILED(hr))
	{
		m_pRecordSet.Release();
		return FALSE;
	}
	//Get the position of the current cursor
	VARIANT_BOOL bRet = m_pRecordSet->GetadoEOF();
	//If the cursor fails to return at the end
	//Ergodic data
	while(!bRet)
	{
		_variant_t varUserID = m_pRecordSet->GetCollect("User_ID");
		_variant_t varUserName = m_pRecordSet->GetCollect("User_Name");
		OBJ_USER objUser;
		objUser.User_ID = varUserID.intVal;
		_tcscpy_s(objUser.User_Name, (TCHAR*)(_bstr_t)varUserName);
		vecObjUser.push_back(objUser);
		//Cursor down
		m_pRecordSet->MoveNext();
		bRet = m_pRecordSet->GetadoEOF();
	}
	m_pRecordSet->Close();
	m_pRecordSet.Release();
	m_pRecordSet = NULL;
	return true;
}

8. Insert data into the database (you can insert pictures, binary big data, etc.)

Through CDataOperator:: Execute sql method, we can insert data into the data by inserting sql statements, but inserting pictures or binary large data is not suitable. So insertion and update operations are implemented separately to support the operation of large data.

1. BOOL CDataOperator::Insert_Into_User(OBJ_USER &objUser)  
2. {  
3.     if (NULL == m_pConnection)  
4.         return FALSE;  
5.     //Recordset object  
6.     _RecordsetPtr m_pRecordSet;  
7.     HRESULT hr = m_pRecordSet.CreateInstance(_T("ADODB.Recordset"));  
8.     if (FAILED(hr))  
9.     {  
10.         return FALSE;  
11.     }  
12.     CString strSQL = _T("select User_ID, User_Name from user");  
13.     hr = m_pRecordSet->Open(_bstr_t(strSQL), m_pConnection.GetInterfacePtr(),  
14.         adOpenStatic, adLockOptimistic, adCmdText);  
15.     if (FAILED(hr))  
16.     {  
17.         m_pRecordSet.Release();  
18.         return FALSE;  
19.     }  
20.       
21.     try  
22.     {  
23.         //Add a row  
24.         m_pRecordSet->AddNew();  
25.     }  
26.     catch (_com_error &e)  
27.     {  
28.         e.ErrorMessage();  
29.         return FALSE;  
30.     }  
31.     try  
32.     {  
33.         m_pRecordSet->PutCollect(_T("User_ID"), _variant_t(objUser.User_ID));  
34.         m_pRecordSet->PutCollect(_T("User_Name"), _variant_t(objUser.User_Name));  
35.     }  
36.     catch (_com_error &e)  
37.     {  
38.         m_pRecordSet->Close();  
39.         m_pRecordSet.Release();  
40.         e.ErrorMessage();  
41.         return FALSE;  
42.     }  
43.     m_pRecordSet->Update();  
44.     m_pRecordSet->Close();  
45.     m_pRecordSet.Release();  
46.     m_pRecordSet = NULL;  
47.     return TRUE;  
48. }  

BOOL CDataOperator::Insert_Into_User(OBJ_USER &objUser)
{
	if (NULL == m_pConnection)
		return FALSE;
	//Recordset object
	_RecordsetPtr m_pRecordSet;
	HRESULT hr = m_pRecordSet.CreateInstance(_T("ADODB.Recordset"));
	if (FAILED(hr))
	{
		return FALSE;
	}
	CString strSQL = _T("select User_ID, User_Name from user");
	hr = m_pRecordSet->Open(_bstr_t(strSQL), m_pConnection.GetInterfacePtr(),
		adOpenStatic, adLockOptimistic, adCmdText);
	if (FAILED(hr))
	{
		m_pRecordSet.Release();
		return FALSE;
	}
	
	try
	{
		//Add a row
		m_pRecordSet->AddNew();
	}
	catch (_com_error &e)
	{
		e.ErrorMessage();
		return FALSE;
	}
	try
	{
		m_pRecordSet->PutCollect(_T("User_ID"), _variant_t(objUser.User_ID));
		m_pRecordSet->PutCollect(_T("User_Name"), _variant_t(objUser.User_Name));
	}
	catch (_com_error &e)
	{
		m_pRecordSet->Close();
		m_pRecordSet.Release();
		e.ErrorMessage();
		return FALSE;
	}
	m_pRecordSet->Update();
	m_pRecordSet->Close();
	m_pRecordSet.Release();
	m_pRecordSet = NULL;
	return TRUE;
}

9. Update data in database

1. BOOL CDataOperator::Update_For_User(OBJ_USER &objUser)  
2. {  
3.     if (NULL == m_pConnection)  
4.         return FALSE;  
5.     //Recordset object  
6.     _RecordsetPtr m_pRecordSet;  
7.     HRESULT hr = m_pRecordSet.CreateInstance(_T("ADODB.Recordset"));  
8.     if (FAILED(hr))  
9.     {  
10.         return FALSE;  
11.     }  
12.     CString strSQL;  
13.     strSQL.Format(_T("select User_ID, User_Name from user where User_ID=%d"), objUser.User_ID);  
14.     hr = m_pRecordSet->Open(_bstr_t(strSQL), m_pConnection.GetInterfacePtr(),  
15.         adOpenStatic, adLockOptimistic, adCmdText);  
16.     if (FAILED(hr))  
17.     {  
18.         m_pRecordSet.Release();  
19.         return FALSE;  
20.     }  
21.     try  
22.     {  
23.         m_pRecordSet->PutCollect(_T("User_Name"), _variant_t(objUser.User_Name));  
24.     }  
25.     catch (_com_error &e)  
26.     {  
27.         m_pRecordSet->Close();  
28.         m_pRecordSet.Release();  
29.         e.ErrorMessage();  
30.         return FALSE;  
31.     }  
32.     m_pRecordSet->Update();  
33.     m_pRecordSet->Close();  
34.     m_pRecordSet.Release();  
35.     m_pRecordSet = NULL;  
36.     return TRUE;  
37. }  

BOOL CDataOperator::Update_For_User(OBJ_USER &objUser)
{
	if (NULL == m_pConnection)
		return FALSE;
	//Recordset object
	_RecordsetPtr m_pRecordSet;
	HRESULT hr = m_pRecordSet.CreateInstance(_T("ADODB.Recordset"));
	if (FAILED(hr))
	{
		return FALSE;
	}
	CString strSQL;
	strSQL.Format(_T("select User_ID, User_Name from user where User_ID=%d"), objUser.User_ID);
	hr = m_pRecordSet->Open(_bstr_t(strSQL), m_pConnection.GetInterfacePtr(),
		adOpenStatic, adLockOptimistic, adCmdText);
	if (FAILED(hr))
	{
		m_pRecordSet.Release();
		return FALSE;
	}
	try
	{
		m_pRecordSet->PutCollect(_T("User_Name"), _variant_t(objUser.User_Name));
	}
	catch (_com_error &e)
	{
		m_pRecordSet->Close();
		m_pRecordSet.Release();
		e.ErrorMessage();
		return FALSE;
	}
	m_pRecordSet->Update();
	m_pRecordSet->Close();
	m_pRecordSet.Release();
	m_pRecordSet = NULL;
	return TRUE;
}

So far, the steps of using ado to connect MySQL database are introduced. The most difficult part is to get the connection string and the use of database connection objects. The database class CDataOperator implements database connection.

Database operations: add, delete, change, check operations. In the future when the database is used in the development of vc, the use of CDataOperator can facilitate the development of programs.