Use ADO to write database programs under VC ++ [Post]

Preparation:
(1) introduce the ADO class

# Import "C: \ Program Files \ common files \ System \ ADO \ msado15.dll "\
No_namespace \
Rename ("EOF", "adoeof ")
(2) initialize com

Afxoleinit () can be used in MFC; used in non-MFC environments:
Coinitialize (null );
Couninitialize ();

(3) # Three smart pointers can be used after the import is included: _ connectionptr, _ recordsetptr, and _ commandptr.

1. Connect and close the database (1) Connection

Example: connect to the Access Database
Afxoleinit (); // Initialization
Hresult hr;
Try
{
HR = m_pconnection.createinstance ("ADODB. Connection"); // create a connection object
If (succeeded (HR ))
{
M_pconnection-> connectiontimeout = 0;
HR = m_pconnection-> open ("provider = Microsoft. Jet. oledb.4.0; Data Source = dB. mdb", "", "", admodeunknown );
// M_pconnection-> putdefaultdatabase (_ bstr_t) "DB"); // you can specify the default database.

M_pcommand.createinstance (_ uuidof (command ));
M_pcommand-> commandtimeout = 5;
M_pcommand-> activeconnection = m_pconnection;
}
}
Catch (_ com_error e) // catch an exception
{
Cstring errormessage;
Errormessage. Format ("failed to connect to the database! \ R \ n error message: % s ", E. errormessage ());
Afxmessagebox (errormessage); // displays the error message.
}

(2) Disable

// If the database connection is valid
If (m_pconnection-> state)
M_pconnection-> close ();
M_pconnection = NULL;

(3) set the connection time // set the connection time -----------------------------------
Pconnection-> put_connectiontimeout (long (5 ));
2. Open a result set

(1) Open. first create a _ recordsetptr instance, and then call open () to obtain the execution result of an SQL statement.
_ Recordsetptrm_precordset;
M_precordset.createinstance (_ uuidof (recordset ));

// In the ADO operation, we recommend that you use try... catch () to capture error information,
// Because it often produces unexpected errors. Jingzhou Xu
Try
{
M_precordset-> open ("select * From demotable", // query all fields in the demotable table
M_pconnection.getinterfaceptr (), // gets the idispatch pointer of the database connected to the database
Adopendynamic,
Adlockoptimistic,
Adcmdtext );
}
Catch (_ com_error * E)
{
Afxmessagebox (e-> errormessage ());
}

(2) Close the result set m_precordset-> close ();

3. operate a result set

(1) traverse (read)
A) Use precordset-> adoeof to determine whether the database pointer has been moved to the end of the result set; m_precordset-> BOF to determine whether it is before the first record: While (! M_precordset-> adoeof)
{
Var = m_precordset-> getcollect ("name ");
If (var. VT! = Vt_null)
Strname = (lpcstr) _ bstr_t (VAR );
Var = m_precordset-> getcollect ("Age ");
If (var. VT! = Vt_null)
Strage = (lpcstr) _ bstr_t (VAR );
M_accesslist.addstring (strname + "-->" + strage );
M_precordset-> movenext ();
}

B) There are two ways to obtain the value of a field.

First

// Get the value m_precordset-> getcollect ("name") of the 0th fields ");

Or m_precordset-> getcollect (_ variant_t (long (0 ));

Second
Precordset-> get_collect ("column_name ");

Or precordset-> get_collect (long (INDEX ));

(2) Add

A) Call m_precordset-> addnew ();
B) Call m_precordset-> putcollect (); assign values to each field
C) Call m_precordset-> Update (); Confirm

(3) modify
(4) Delete
A) Move the record pointer to the record to be deleted, and then call Delete (adaffectcurrent) Try
{
// Assume that the second record is deleted.
M_precordset-> movefirst ();
M_precordset-> move (1 );
// Starts from 0
M_precordset-> Delete (adaffectcurrent );
// The adaffectcurrent parameter is used to delete the current record.
M_precordset-> Update ();
}
Catch (_ com_error * E)
{
Afxmessagebox (e-> errormessage ());
}

4. Execute SQL statements directly. Most of the functions except the result set can be directly implemented in SQL.

(1). Use _ commandptr and _ recordsetptr in combination with
_ commandptrm_pcommand;
m_pcommand.createinstance (_ uuidof (command ));
// assign the database connection to it
m_pcommand-> activeconnection = m_pconnection;
// SQL statement
m_pcommand-> commandtext = "select * From demotable";
// execute the SQL statement, returned record set
m_precordset = m_pcommand-> execute (null, null, ad1_text);
(2) run the SQL statement with _ connectionptr
_ recordsetptr connection15: Execute (_ bstr_t commandtext,
variant * recordsaffected,
Long Options)

Commandtext is a command string, usually an SQL command.
The recordsaffected parameter indicates the number of rows affected by the operation,
The options parameter indicates the content type in commandtext. options can be one of the following values:
Adshorttext: indicates that commandtext is a text command.
Adcmdtable: indicates that commandtext is a table name.
Ad1_proc: indicates that commandtext is a stored procedure.
Adcmdunknown: Unknown

Example:
_ Variant_t recordsaffected;
M_pconnection-> execute ("Update users set old = old + 1", & recordsaffected, ad1_text );
5. Call the Stored Procedure
(1) Use _ commandptr
_ Commandptrm_pcommand;
M_pcommand.createinstance (_ uuidof (command ));
M_pcommand-> activeconnection = m_pconnection; // assign the database connection to it
M_pcommand-> commandtext = "Demo ";
M_pcommand-> execute (null, null, ad1_storedproc );
(2) directly call with _ connectionptr (see 4. (2 ))

6. traverse all tables in the database named _ connectionptr m_pconnect;
_ Recordsetptr pset;
Hresult hr;
Try
{
HR = m_pconnect.createinstance ("ADODB. Connection ");
If (succeeded (HR ))
{
Cstring dd;
Dd. Format ("provider = Microsoft. Jet. oledb.4.0; Data Source = % s", file );
HR = m_pconnect-> open (_ bstr_t) dd, "", "", admodeunknown );
Pset = m_pconnect-> openschema (adschematables );
While (! (Pset-> adoeof ))
{
// Obtain the table
_ Bstr_t table_name = pset-> fields-> getitem ("table_name")-> value;

// Obtain the table Type
_ Bstr_t table_type = pset-> fields-> getitem ("table_type")-> value;

// Filter the table name. Only the table name is output.
If (strcmp (lpcstr) table_type), "table") = 0 ){
Cstring tt;
TT. Format ("% s", (lpcstr) table_name );
Afxmessagebox (TT );
}
Pset-> movenext ();
}
Pset-> close ();
}
M_pconnect-> close ();
} Catch (_ com_error e) // catch an exception
{
Cstring errormessage;
Errormessage. Format ("failed to connect to the database! Rn error message: % s ", E. errormessage ());

Afxmessagebox (errormessage );
Return-1;
}
7. traverse all fields in a table
Field * field = NULL;
Hresult hr;
Fields * fields = NULL;
HR = m_precordset-> get_fields (& fields); // obtain the field set of the record set
 
If (succeeded (HR ))
Fields-> get_count (& colcount );

// Obtain the total number of fields in the field set of the record set
For (I = 0; iItem [I]-> get_name (& bstrcolname); // obtain the field name in the record set //
Strcolname = bstrcolname;
Namefield = strcolname;
M_fieldslist.addstring (namefield );
}
If (succeeded (HR ))
Fields-> release (); // release the pointer

Appendix:
1. _ variant_t
(1) generally, the values passed to the three pointers are not the data types directly supported by MFC, but must be converted using _ variant_t.
_ Variant_t (XX) can convert most types of variables into suitable types for input:
(2), _ variant_t var; _ variant_t-> long: (long) var;
_ Variant_t-> cstring: cstring strvalue = (lpcstr) _ bstr_t (VAR );
Cstring-> _ variant_t: _ variant_t (strsql );
2. Conversion between BSTR and cstring

BSTR;
Cstring strsql;
Cstring-> BSTR: BSTR = strsql. allocsysstring ();
BSTR-> cstring: strsql = (lpcstr) BSTR;
3. _ bstr_t and cstring convert each other

_ Bstr_t BSTR;
Cstring strsql;
Cstring-> _ bstr_t: BSTR = (_ bstr_t) strsql;
_ Bstr_t-> cstring: strsql = (lpcstr) BSTR;
4. Time
Access: Time string #
SQL: The time string '''2017-4-5''
Datefield (time field) Select * From my_table where datefield> #2004-4-10 #

 

Try
{
M_pcommand-> commandtext = "insert into ttest (AGE) values ('23f2 ')";
M_precordset = m_pcommand-> execute (null, null, ad1_text );
}
Catch (_ com_error e) // catch an exception
{
Cstring errormessage;
Errormessage. Format ("failed to connect to the database! \ R \ n error message: % s ", E. errormessage ());
Afxmessagebox (errormessage); // displays the error message.
}