Note: This article is not originally original, from
Www.vckbase.com
For reference, if there is any offense, please contact me, I will delete. Thank you Author: North China University of Computer 2000 4 classes
querw
ready:
(1) introduce ADO classes
Add to STDAFX.H in this header file. #import "c: / program files / compon files / system / ado / msado15.dll" /
NO_NAMESPACE /
Rename ("EOF", "AdoeOf") (2), initialization COM
Afxoleinit () can be used in the MFC;
If it is a dialog-based MFC program, add AFXoleinit () in the OnInitDialg () function. If it is a document / view class, you add AFXoleinit () to the initInstance () function. Non-MFC environments: coinitialize (null);
Couninitialize ();
(3) #import, you can use 3 smart pointers: _ConnectionPtr, _RecordSetPtr and _Commandptr, basically able to meet the database's operations through these three smart pointers. 1. Connect and close the database (1) Connection example: Connect Access Database
M_PConnection.createInstance (__ uuidof (connection)); // Note: __ uuidof is used to get the GUID value of the rear expression.
Try
{
/ / Open the local Access library demo.mdb
m_pConnection-> open ("provider = microsoft.jet.oledb.4.0; datasource = demo.mdb",
"", ", admodeunknown;
}
Catch (_COM_ERROR E)
{
AFXMessageBox ("Database connection failed, confirming if the database demo.mdb is in the current path!");
Return False;
} (2), close
// If the database connection is valid
IF (M_PConnection-> State)
m_pConnection-> close ();
M_PConnection = NULL;
(3), set up connection time // Set connection time ---------------------------------
PConnection-> PUT_CONNECTIONTIMEOUT (long (5)); 2. Open a result set
(1) Open, first create a _recordsetptr instance, then call the Open () to get the execution result of a SQL statement
_RecordSetPtr m_precordset;
M_PRecordSet.createInstance (__ uuidof (recordset);
/ / In ADO operation, it is necessary to use try ... catch () to capture error messages.
// Because it sometimes has some unexpected errors. Try
{
m_precordset-> Open ("Select * from demotable", // query all fields in the Demotable table
m_pconnection.getInterfacePtr (), // Get the IDSPATCH pointer to the library
AdoPENDYNAMIC,
AdlockOptimistic,
AdcmdText);
}
Catch (_COM_ERROR * E)
{
AfxMessageBox (E-> ErrorMessage ());
}
(2) Close result set
m_precordset-> close ();
3. Operate a result set
(1), traverse (read) a), use PrecordSet-> AdoEOF to determine if the database pointer has moved to the end of the result set; m_PRecordSet-> BOF determines if the first record in front:
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 get the value of a field.
One
/ / Indicates the value of the value of the 0th field
M_PRecordset-> getCollect ("name");
or
M_PRecordset-> getCollect (_VARIANT_T (long (0));
two is
PrecordSet-> Get_collect ("Column_name");
or
PrecordSet-> Get_collect (long (index));
(2) Add
a), call m_precordset-> addnew (); b), call m_precordset-> Putcollect (); assign each field assignment C), call m_precordset-> Update (); confirm
(3), modify
(4), delete
a) Move the record pointer to the record you want to delete, then call Delete (AdAffectCurrent)
Try
{
/ / Assume that the second record is deleted
M_PRecordset-> MoveFirst ();
M_PRecordset-> Move (1);
// starting from 0
M_PRecordset-> delete (AdaffectCurrent);
// Parameter adAffectCurrent To delete the current record
m_Precordset-> Update ();
}
Catch (_COM_ERROR * E)
{
AfxMessageBox (E-> ErrorMessage ());
}
4. Execute the SQL statement directly, in addition to the fact that most of the functions of the result set can be implemented directly with SQL language
(1), with _commandptr and _recordsetptr
_Commandptr m_pcommand;
M_PCommand.createInstance (__ uuidof (command));
// Connect the library connection to it
M_PCOMMAND-> ActiveConnection = m_pConnection;
// SQL statement
m_pcommand-> commandtext = "select * from demotable";
/ / Execute SQL statement, return to record set
m_precordset = m_pcommand-> execute (null, null, adcmdtext); (2), directly using _ConnectionPTR to execute SQL statements
_RecordSetPtr Connection15 :: EXECUTE (_BSTR_T CommandText,
Variant * Recordsaffected,
Long Options)
Where the CommandText is a command string, usually the SQL command.
Parameter recordsaffected is the number of rows that are affected after the operation is completed, and the parameter Options represents the type of content in CommandText, and Options can take one of the following:
AdcmdText: Indicates that CommandText is a text command
AdcmdTable: Indicates that CommandText is a table name
AdcmdProc: Indicates that CommandText is a stored procedure
Adcmdunknown: Unknown
example:
_VARIANT_T RECORDSAFFECTED;
M_PConnection-> Execute ("Update Users Set Old = OLD 1", & Recordsaffected, AdcmdText); 5. Call the stored procedure
(1), using _commandptr
_Commandptr m_pcommand;
M_PCommand.createInstance (__ uuidof (command));
m_pcommand-> ActiveConnection = m_pconnection; // Connect the library connection
m_pcommand-> commandtext = "demo";
m_pcommand-> execute (null, null, adcmdstoredProc); (2), directly call directly _ConnectionPtr (see 4. (2))
6. Traverse all the table names in the database
_CONNECTIONPTR M_PCONNECT;
_RecordSetPtr pset;
HRESULT HR;
Try
{
HR = m_pconnect.createInstance ("AdoDb.Connection");
En (ac))
{
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))
{
// Get the form
_BSTR_T TABLE_NAME = Pset-> Fields-> GetItem ("Table_Name") -> Value;
// Get the table type
_BSTR_T TABLE_TYPE = Pset-> Fields-> GetItem ("Table_TYPE") -> Value;
// Filter, only output the table name, other omissions
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) /// Capture Exception
{
CString ErrorMessage;
ErrorMessage.Format ("Connection Database Failed! RN Error Information:% S", E.ErrorMessage ());
AfXMessageBox; ErrorMessage;
Return -1;
} 7. Traversing all fields in a table
Field * Field = NULL;
HRESULT HR; FIELDS * FIELDS = NULL;
hr = m_precordset-> get_fields (& FIELDS); // Get the field set of records and
En (ac))
Fields-> Get_count (& colcount);
/ / Get the total number of fields in the field collection of records
For (i = 0; i
Item [i] -> get_name (& bstrcolname); // Get field name in record set //
Strcolname = BSTRCOLNAME;
Namefield = strcolname;
m_fieldslist.addstring (namefield);
}
En (ac))
Fields-> release (); // Release pointer
Attachment:
1, _variant_t
(1), the value of the three pointers is generally not the data type directly supported by the MFC, but use _variant_t to convert it.
_Variant_t (xx) can convert most types of variables into suitable types:
(2), _ variant_t var;
_VARIANT_T -> Long: (long) Var;
_variant_t -> cstring: cstring strval = (lpcstr) _BSTR_T (var);
CSTRING -> _VARIANT_T: _VARIANT_T (STRSQL);
2, BSTR wide string and CString mutual conversion
BSTR BSTR;
CString strsql;
CSTRING -> BSTR: BSTR = strsql.allocsystring ();
BSTR -> cstring: strsql = (lpcstr) BSTR;
3, _BSTR_T and CSTRING mutual conversion
_BSTR_T BSTR;
CString strsql;
CSTRING -> _BSTR_T: BSTR = (_BSTR_T) strsql;
_BSTR_T -> CString: strsql = (lpcstr) BSTR;
4. About time access: indicates the character string # 2004-4-5 # SQL: indicates the string of time '2004-4-5''datefield (Time field) Select * from my_table where datefield> # 2004-4 -10 #
