Database directly through the ADO operational Access database
Author: Xu Jing Zhou
Download sample source code (http://www.vckbase.com/code/viewcode.asp?id=1715)
Last time
The ADO provides a large group of objects that encapsulate generally universal data access details. Since the ODBC data source also provides a general OLE DB Privider, ADO can not only apply its own OLE DB Privider, but also all ODBC drivers can be applied. With regard to other details of OLE DB and ADO, readers can check their own books or MSDNs themselves, which will not be explained here. Let us go directly into the theme, how to master the ADO database access technology. The ADO's operation method and the operation of the previous DAO are similar in many ways. Here, in order to more effectively explain its method, use VC6.0 to do an example program (Adorwaccess), this sample program You can use the ADO to operate the Access database, and the operational effect of the sample program is shown below:
In the sample program, we still use the original library structure, database name demo.mdb, library name Demotable, table field named Name (Name) and AGE (age) to construct the sample program operation required Access database, which is also compatible with library structures in the sample source code of the previous two articles.
Let's take a look at the basic steps and methods for using the ADO database access technology:
First, use a #import statement to reference the component type library (* .tlb) supporting ADO, where the type library can be used as part of the executable (DLL, EXE, etc.) in its own programs, such as : It is located in the subsidiary of Msado15.dll, only needs to be referenced directly to #import. Can be added directly to the STDAFX.H file to implement:
#import "c: / program files / compon files / system / ado / msado15.dll" /
NO_NAMESPACE /
Rename ("EOF", "Adoeof")
The path name can be set according to the path to the ADO support file installed by your system. When the compiler encounters the #import statement, it generates a packaging class for the interface in the reference component type library, and the # import statement is actually equivalent to the API Caver LoadTypeLib (). #import statement will generate two files in the project executable program output directory, which is * .tlh (Type library header file) and * .tli (type library implementation file), which generate a smart pointer for each interface, and Declaring a variety of interface methods, enumeration types, CLSID, etc., create a series of packaging methods. Statements NO_NAMESPACE Description ADO objects do not use namespaces, rename ("EOF", "AdoEOF") Description Change the end flag EOF in the ADO to AdoEOF to avoid conflicts with other libraries.
Second, initialization components need to be initialized during the initial process of the program, which can generally be implemented, this method is to close the initialization COM, which can be implemented in the following statement counted. Another method can also be used in the MFC to implement the initialization COM. This method only needs a statement to automatically implement the operation of our implementation COM and the end, the statement is as follows: Afxoleinit (); then, You can use ADO's operation directly. When we often use only the three-in-TLH, the generated packaging class. Trical, which is the smart pointer declared in TLH, which is _connectionptr, _recordsetptr, and _Commandptr. The following is introduced separately:
1. _ConnectionPtr Intelligent pointer, usually used to open, close a library connection or use its Execute method to perform a command statement that does not return a result (similar to _CumePtr is similar).
l Open a library connection. Create an instance pointer first, open a library connection with Open, which will return an IUNKNOWN automation interface pointer. The code is as follows:
_ConnectionPTR m_PConnection;
// Initialize COM, create an ADO connection, etc.
Afxoleinit ();
M_PCONNECTION.CREATEINSTANCE (__ uuidof (connection));
/ / In ADO operation, it is necessary to use try ... catch () to capture error messages.
// Because it sometimes has some unexpected errors. Jingzhou Xu
Try
{
/ / Open the local Access library demo.mdb
m_pConnection-> Open ("provider = microsoft.jet.Oledb.4.0; data source = demo.mdb", ",", ", admodeunknown);
}
Catch (_COM_ERROR E)
{
AFXMessageBox ("Database connection failed, confirming if the database demo.mdb is in the current path!");
Return False;
}
l Close a library connection. If the connection status is valid, use the Close method to close it and it is empty. The code is as follows:
IF (M_PConnection-> State)
m_pConnection-> close ();
M_PConnection = NULL;
2. _RecordSetPtr smart pointer, can be used to open the library data table and can perform various operations on the records, fields, etc. in the table.
l Turn on the data table. Open the table name of DEMOTABLE in the library, the code is as follows:
_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. Jingzhou Xu
Try
{
m_precordset-> Open ("Select * from demotable", // query all fields in the Demotable table
Theapp.m_pconnection.getInterfacePtr (), // Get the IDSPATCH pointer of the library
AdoPENDYNAMIC,
AdlockOptimistic,
AdcmdText);
}
Catch (_COM_ERROR * E)
{
AfxMessageBox (E-> ErrorMessage ());
}
l Reads data in the table. The table data is read all and displayed in the list box, M_AccessList is the member variable name of the list box. If you do not encounter a table end flag AdoEof, use the getCollect (Field Name) or M_PRecordset-> fields-> GetItem (Field Name) -> Value method, then get the field value referred to in the current record pointer, then use movenext () Method moves to the next recording location. The code is as follows:
_VARIANT_T VAR;
CString Strname, StRAGE;
Try
{
IF (! m_precordset-> bof)
M_PRecordset-> MoveFirst ();
Else
{
AFXMessageBox ("The data is empty");
Return;
}
/ / Read in the library and join the list box
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 ();
}
/ / The default list points to the first item, and move the record pointer and display
m_accessList.Setcursel (0);
}
Catch (_COM_ERROR * E)
{
AfxMessageBox (E-> ErrorMessage ());
}
l Insert the record. You can use the addNew () method to enter a value of each field with the PUTCOLLECT (Field Name, Value), and finally Update () is updated to the library data. The variables m_name and m_age are the name and age editing of the member variable name, respectively. Looking down below:
Try
{
// Write to each field value
M_PRecordset-> addNew ();
M_PRecordset-> Putcollect ("name", _variant_t (m_name));
M_PRecordset-> Putcollect ("age", atol (m_age));
m_Precordset-> Update ();
AfxMessageBox ("Insert Success!");
}
Catch (_COM_ERROR * E)
{
AfxMessageBox (E-> ErrorMessage ());
}
l Move the record pointer. The mobile record pointer can be moved to the first record via the movefirst () method, the movelast () method moves to the last record, the movPrevious () method moves to the previous record of the current record, the MOVENEXT () method moves to the next one of the current record recording. However, when we often need to move the record pointer to any record position, you can use the MOVE (record number) method to implement, Note: The MOVE () method is moving the pointer position relative to the current record, moves backward, negative The value moves forward, such as: Move (3), when the current record is 3, it will move from the record 3 to move 3 record positions. The code is as follows: TRY
{
INT CURSEL = M_AccessList.getcursel ();
// First move the pointer to the first record, then move the record pointer with respect to the first record
M_PRecordset-> MoveFirst ();
M_PRecordset-> Move (long (crasel));
}
Catch (_COM_ERROR * E)
{
AfxMessageBox (E-> ErrorMessage ());
}
l Modify the field value in the record. You can move the record pointer to the location where you want to modify the record, write the new value directly to the update () update database. You can move the record pointer with the above method, modify the field value code as follows:
Try
{
/ / Assume that the second record is modified
M_PRecordset-> MoveFirst ();
m_precordset-> move (1); // Starting from 0
M_PRecordset-> Putcollect ("name", _variant_t (m_name));
M_PRecordset-> Putcollect ("age", atol (m_age));
m_Precordset-> Update ();
}
Catch (_COM_ERROR * E)
{
AfxMessageBox (E-> ErrorMessage ());
}
l Delete record. Deleting a record and modifying the record above, first move the record pointer to the location where the record is changed, and use the delete () method to update the database in both databases with UPDATE (). The code is as follows:
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 ());
}
l Close the recordset. Use the Close method to close the recordset and empower its null value. The code is as follows:
m_precordset-> close ();
m_precordset = NULL;
3. Commandptr intelligent pointer, you can use _ConnectionPTR or _RecordSetPtr to perform tasks, define output parameters, perform stored procedures, or SQL statements.
l Execute the SQL statement. Create a _commandptr instance pointer, then make the library connection and SQL statements as parameters, and execute the execute () method. The code is as follows:
_Commandptr m_pcommand;
m_pcommand.createInstance (__ uuidof (command)); m_pcommand-> ActiveConnection = m_pconnection; // Connect to it
m_pcommand-> commandtext = "select * from demotable"; // SQL statement
m_precordset = m_pcommand-> execute (null, null, adcmdtext); // Execute SQL statement, return to record set
l Execute a stored procedure. The operation of performing a stored procedure is similar to the SQL statement above. Different points is only the SQL statement in the CommandText parameter, but the name of the stored procedure, such as Demo. Another difference is that the parameters in Execute () are modulated by adcmdtext, and the stored procedure is changed to AdcmdStoredProc. If there is an input and output parameter in the stored procedure, you need to use another smart pointer _ParameterPtr to set the parameter information to enter, output, and assign it _Commandptr to pass the information, interested readers can Look for books or MSDNs yourself. The code to perform the stored procedure is as follows:
_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);
Finally, if you want to know in detail details, you can download the source code after downloading the sample source code (detailed note).
Contact information:
Author email: jingzhou_xu@163.com
Future Studio (Future Studio)
