Use ADO to write database programs under VC ++

 

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.
_ Recordsetptr m_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
_ Commandptr m_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 and return the record set
M_precordset = m_pcommand-> execute (null, null, ad1_text );
(2) run the SQL statement directly 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
_ Commandptr m_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.
}
Top

Wangjia184 on the second floor (I am a legend ...... SB)The score is 0 at 08:53:28.

ODBC Link

Suitable for database type connection
Access "driver = {Microsoft Access Driver (*. mdb)}; DBQ = *. mdb; uid = admin; Pwd = pass ;"
DBASE "driver = {Microsoft DBASE Driver (*. DBF)}; driverid = 277; DBQ = ------------;"
Oracle "driver = {Microsoft ODBC for Oracle}; server = oraclesever. World; uid = admin; Pwd = pass ;"
MSSQL Server "driver = {SQL Server}; server = servername; database = dbname; uid = sa; Pwd = pass ;"
MS text "driver = {Microsoft text Driver (*. txt; *. CSV)}; DBQ = -----; extensions = ASC, CSV, tab, txt; persist securityinfo = false ;"
Visual FoxPro "driver = {Microsoft Visual FoxPro driver}; sourcetype = dBc; sourcedb = *. dBc; exclusive = no ;"
MySQL "driver = {MySQL}; database = yourdatabase; uid = username; Pwd = yourpassword; option = 16386 ;"


Oledb Link

Suitable Connection Methods for database types
Access "provider = Microsoft. Jet. oledb.4.0; Data Source = your_database_path; user id = admin; Password = pass ;"
Oracle "provider = oraoledb. Oracle; Data Source = dbname; user id = admin; Password = pass ;"
Ms SQL Server "provider = sqloledb; Data Source = machinename; initial catalog = dbname; userid = sa; Password = pass ;"
MS text "provider = microsof. Jet. oledb.4.0; Data Source = your_path; extended properties 'text; FMt = delimited '"