c + + ADO way to connect MySQL database

For the software development is actually in the fact that it is constantly working with the data, so the operation of the database is essential, the next introduction of VC development using ADO to establish an ODBC data source to access the MySQL database.

From my contact with the database programming, I think the VC development connection database is more difficult, is also very prone to error. In Android, the system comes with the SQLite database, only need to use the Sqliteopenhelper abstract class to complete the operation with the database. In Java, use JDBC to connect to the MySQL database, download the appropriate jar to invoke the appropriate interface, and pass in the database type and user name password for database operation. But the ADO connection database is rather complex, so let's look at how to use ADO to connect to the database.

One. Install MySQL

First you need to make sure that the MySQL database is installed on your computer and that you are using MySQL successfully using your username and password. How to install the configuration MySQL, here do not do a detailed introduction.

If you do not know how to install the configuration MySQL can refer to this address: Click to open the link

Two. Connect to MySQL by ADO mode

1. Import the ADO database and add the following statement to your program to import the ADO database

2. Initializing the COM environment

Initialize COM environment afxoleinit ();

3. Create a data table structure to hold the content retrieved from the database
Suppose a 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 an Operational database class operator class

Class Cdataoperator{public:cdataoperator (); ~cdataoperator ();p ublic://Open the specified database bool OpenDatabase (CString strDbName, CString strUserName, CString struserpwd);p ublic://Execute SQL statements, add data, delete data bool ExecuteSQL (CString SQL);//query data bool Select_ From_user (vector<obj_user> &vecobjuser);//Insert data, can insert picture, binary data (Big data) BOOL Insert_into_user (Obj_user & objuser)///update data, can update big data bool Update_for_user (Obj_user &objuser);p ublic://Connection object _connectionptr m_pconnection;};

5. Definition of the operator class

Cdataoperator::cdataoperator () {try{//Creates a Connection object 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 ();   & nbsp;    }        catch (_com_error &e)          {            e.errormessage ();         }        //Release connection objects         m_ Pconnection.release ();        m_pconnection = null;    }}// Opening the database operation 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)) &NBsp;           {                 m_pconnection = null;            }         }        catch (_com_error &e)         {            e.errorinfo ();            return false;        }    }     return true;}

How to get the connection string:

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 user name and password of the database, and, here and Java, similar to the JDBC Connection database, gets the connection string:

"Database=%s;dsn=myodbc;option=0; pwd=%s; port=0; Server=localhost; uid=%s; "In the following manner:

(1) First need to install Mysql-connector-odbc-3.51.26-win32.ini, in order to connect MySQL database
(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 ', select ' Use connection string ',
Click ' Compile ', select ' machine Data source ', click ' New ', click ' Next ', select MySQL (if you installed the app in the first step will have MySQL driver) Click ' Next ',
Then fill in the corresponding content, need to connect the database, server, user name, password, etc., click the Test button, test whether the connection is successful, the connection is successful, save.
(4) The connection string is obtained by opening the file in Notepad.

6. Interface for Cdataoperator class to execute SQL statement operations

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 the 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;} Empty Vecobjuser.clear () before obtaining data; 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;} Gets the position of the current cursor Variant_bool bRet = m_precordset->getadoeof ();//If the cursor returns failure at the end//Traverse 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 moves down m_precordset->movenext (); bRet = M_precordset->getadoeof ();} M_precordset->close (); M_precordset.release (); m_precordset = NULl;return true;} 

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

By using the Cdataoperator::executesql method, we can insert data into the data through incoming SQL statements, but inserting a picture or binary big data is not a good way to do it. So the insert and update operations are implemented separately, enabling big data operations

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. Updating data in the database

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;}

at this point, the ADO way to connect to the MySQL database, the steps to complete the most difficult place I think is to get the connection string and database connection object use. Database class Cdataoperator, the database connection is realized ,

Database operation: Increase, delete, change, check operation. Later in VC development when using the database, the use of cdataoperator can be very convenient to develop programs.

c + + ADO way to connect MySQL database