First, ADO Overview
ADO is Microsoft designed for the latest and most powerful data access example OLE DB, which is an application layer interface that is easy to use. ADO allows you to write applications to access and operate data in the database server through the OLE.DB provider. The most important advantages of ADO are easy to use, fast, and less memory spending and small disk remains. ADO uses minimal network traffic in a critical application, and uses the least number of layers between the front and data sources, all of which are to provide lightweight, high-performance interfaces. The reason is called ADO, which has used a familiar metaphor, OLE automation interface.
OLE DB is a set of "Component Object Model" (COM) interfaces, a new database low-level interface, which encapsulates the function of ODBC and access data stored in different information in a unified manner. OLE DB is the technical foundation of the Microsoft UDA policy. OLE DB provides high performance access for any data source, including relationships and non-relational databases, email, and file systems, text, and graphics, custom business objects, and more. That is, OLE DB is not limited to ISAM, JET, even relational data sources, which can handle any type of data regardless of their format and storage methods. In practical applications, this diversity means accessing data residing in an Excel electronic data sheet, a text file, an email / directory service, and even a mail server, such as Microsoft Exchange. However, the purpose of the OLE DB application programming interface is to provide the best features for a variety of applications, which do not meet simplified requirements. The API you need should be a bridge that connects to applications and OLE DB, which is ActiveX Data Objects (ADO).
Second, use ADO in VC
1. Introduce the ADO library file
Before using ADO, you must introduce the STDAFX.H file in the project to introduce the ADO library file to enable the compiler to compile correctly. The code is as follows:
Code 1: Introducing an ADO library file with #import
#import "c: / program files / compon files / system / ado / msado15.dll"
NO_NAMESPACES RENAME ("EOF" AdoEof ")
This list of statements declares that use ADO in the project, but does not use the namespace of ADO, and rename constant EOFs to AdoEOF in order to avoid constant conflicts. Now you don't need to add additional header files, you can use the ADO interface.
2, initialize the OLE / COM library environment
It must be noted that the ADO library is a set of COM dynamic libraries, which means that the application must initialize the OLE / COM library environment before calling ADO. In the MFC application, a better way is to initialize the OLE / COM library environment in the initInstance member function of the application primary class.
Code 2: Initializing the OLE / COM library environment
Bool CadoApp :: InitInstance ()
{
IF (! Afxoleinit ())
{
AFXMessageBox ("OLE initialization error!");
Return False;
}
......
}
Function Afxolein initializes the OLE / COM library environment at each application starts.
Like DAO and CDATABASE, ADO consists of several interfaces:
_ConnectionPtr, _Commandptr and _RecordSetPtr.
Unlike DAO and CDATABASE, ADO is based on CoM interface, so if you have not touched COM, you should find a book to learn about COM before using ADO.
3. Introduction to the ADO interface
The ADO library contains three basic interfaces: _ConnectionPTR interface, _commandptr interface and _RecordSetPtr interface. _ConnectionPTR interface returns a recordset or an empty pointer. It is usually used to create a data connection or perform a SQL statement that does not return any result, such as a stored procedure. Returning a recordset using the _ConnectionPTR interface is not a good method. Usually, like CDATABASE, use it to create a data connection, then use other objects to perform data input output operations.
_CommandPTR interface returns a recordset. It provides a simple way to perform the stored procedures and SQL statements that returns the record set. When using the _CommandPTR interface, you can use the global _connectionptr interface, or you can use the connection string directly in the _commandptr interface. If you only perform one or more data access, the latter is a better choice. But if you want to access the database frequently and return a lot of records, you should use the global _connectionptr interface to create a data connection, then use the _CommandPTR interface to perform the stored procedure and SQL statements.
_RecordSetPtr is a recordset object. Compared with the above two objects, it provides more control functions to records, such as record lock, cursor control, and the like. Like the _CommandPTR interface, it does not have to use a data connection that has been created, you can use a connection string instead of the Connection member variable assigned to _RecordSetptr, let it create data connections yourself. If you want to use multiple record sets, the best way is to use the Command object to use the global _connectionptr interface that has created a data connection, and then use the _recordsetPTR to perform the stored procedure and SQL statements.
4, use the _connectionPTR interface
_ConnectionPtr is a connection interface that is similar to CDatabase and CDAODATABASE. Their working principle is similar. First create a _ConnectionPTR interface instance, then point to and open an ODBC data source or OLE DB data provider. The following code and CDAODATABASE create a DSN and non-DSN-based data connection.
Code 3: Using CDAODATABASE (based on DSN)
Cdaodatabase mydb = new cdaodatabase ();
Mydb.open (NULL, FALSE, FALSE, "ODBC; DSN = SAMP; UID = admin; pwd = admin");
Code 4: Using CDAODATABASE (based on non-DSN)
Cdaodatabase mydb = new cdaodatabase ();
Mydb.open (NULL, FALSE, FALSE, "ODBC; Driver = {SQL Server}; Server = Server;
Database = SAMP; UID = admin; pwd = admin ");
Code 5: Using _ConnectionPTR (based on DSN)
_ConnectionPtr MyDB;
Mydb.createInstance (__ uuidof (connection));
MYDB-> Open ("DSN = SAMP; UID = admin; pwd = admin", "", ", - 1);
Code 6: Using _ConnectionPTR (based on non-DSN)
_ConnectionPtr MyDB;
Mydb.createInstance (__ uuidof (connection));
MYDB-> Open ("provider = SQLOLEDB; Server = Server; Database = Samp; UID = admin; pwd = admin", "", ", - 1);
5, use _recordsetptr
_RecordSetPtr interface method and CDAODATABASE, by comparison with the following code, you will find that the usage _RecordSetPtr interface is very simple (the following code uses the data that has been created above):
Code 7: Execute SQL statements using CDAODATABASE
CDAORECORDSET MySet = New CDAORECORDSET (MYDB);
MySet-> Open (AFX_DAO_USE_DEFAULT_TYPE, "SELECT * from T_SAMP");
Now Using ADO:
Code 8: Using _RecordSetPtr to execute SQL statements
_RecordSetPtr myset;
MySet.createInstance (__ uuidof (recordset));
MySet-> Open ("SELECT * from home_table",
Mydb.getInterfacePtr (), AdoPENDYNAMIC, AdLockOptimistic, AdcmdText);
Now we already have a data connection and a recordset, and you can use data. As can be seen from the following code, using the ADO _RecordSetPtr interface, it is not necessary to use DAO to frequently use the large and complex data structure VARIANT, and forced to convert various data types, this is also one of the advantages of ADO. Assume that the program has a listbox control name M_List. The following code we use the _recordsetPTR interface to get record set data and populate this ListBox control:
Code 9: Access data using DAO
Variant * vfieldValue;
Colevariant CovfieldValue;
CString Holder;
While (! myset-> iesof ())
{
MySet-> GetfieldValue ("Field_1", COVFIELDVALUE);
VfieldValue = (LPVARIANT) COVFIELDVALUE;
IF (vfieldvalue-> vt! -vt_null)
{
Holder.Format ("% s", vfieldvalue-> pbval);
M_List.Addstring (Holder);
}
MySet.Movenext ();
}
Code 10: Access data using ADO
_Variant_t Holder
Try {
While (! myset-> adoEof)
{
Holder = MySet-> getCollect ("Field_1");
IF (Holder.Vt! = VT_NULL)
M_List.Addstring (CHAR *) _BSTR_T (HOLDER));
MySet-> MoveNext ();
}
}
Catch (_COM_ERROR * E)
{
Error = E-> ErrorMessage ();
AfxMessageBox (E-> ErrorMessage ());
}
Catch (...)
{
MessageBox ("ADO error!");
}
You must always use TRY and CATCH to capture an ADO error in your code, otherwise the ADO error will make your application crash. When the ADO is running error (such as the database does not exist), the OLE DB data provider will automatically create a _COM_ERROR object, and populate the error message to the member variable of this object. 6, use the _commandptr interface
_CommandPTR interface returns a RecordSet object, and provides more recordset control functions, the following code samples use the _CommandPTR interface:
Code 11: Get data using _commandptr
_Commandptr pCommand;
_RecordSetPtr myset;
PCOMMAND.CREATEINSTANCE (__ uuidof (command));
PCOMMAND-> ActiveConnection = MYDB;
PCOMMAND-> CommandText = "SELECT * from home_table";
PCOMMAND-> CommandType = AdcmdText;
PCOMMAND-> Parameters-> refresh ();
MySet = pCommand-> Execute (NULL, NULL, AdcmDunknown);
_VARIANT_T thevalue = myset-> getCollect ("Field_1");
CString Svalue = (char *) _BSTR_T (Thevalue);
7. About data type conversion
Since the COM object is a cross-platform, it uses a generic method to handle various types of data, so the CSTRING class and COM object are incompatible, we need a set of APIs to convert COM objects and C types of data. _vatiant_t and _bstr_t are the other objects. They provide a general method to convert COM objects and C types of data.
8, small knot
The trend of data access is OLE DB. The easiest way to use OLE DB is Ado.ado's object hierarchy model encapsulated database access details, providing a very good data access policy for C programmers.
