Use ADO
6.1 Overview
ADO is an ActiveX Data Object, which is an object-oriented new interface for Microsoft Develop Database Applications. The ADO Access Database is performed by accessing the OLE DB data provider, providing a simple high-level access interface for the OLE DB data provider.
ADO technology simplifies OLE DB operation, and a large number of COM interfaces are used in OLE DB programs, while ADO encapsulates these interfaces. Therefore, ADO is a high-level access technology.
ADO technology is based on General Object Model (COM), which provides access technology for multiple languages, and because ADO provides access to automation interfaces, ADO can use the description scripting language to access VBScript, VCScript, etc.
6.2 Using ADO in VC
You can use the ActiveX control for VC6 to develop applications, and you can develop applications with ADO objects. Developing applications using ADO objects allows program developers to control access to databases, resulting in database access programs that meet user needs.
Developing applications using ADO objects is similar to other technologies, you need to generate connections to data sources, create records, but different from other access technologies, the levels and sequential relationship requirements between ADO technologies are not too strict. During the program development, you don't have to select a connection before you can generate a record object. You can use the record object directly in the use of records, and the program automatically establishes a connection with the data source while creating a recording object. This model has strong simplified programming and enhances the flexibility of the program. The following describes how to use ADO objects.
6.21 Introducing an 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:
#define initguid
#import "c: / program files / commit files / system / ado / msado15.dll" no_namespace rename ("EOF", "endoffile")
#include "icrsint.h"
This list of statements declare use ADO in the project, but does not use ADO's namespace, and rename EOF to endoffile in order to avoid conflicts.
6.22 Initializing the ADO environment
The COM environment must be initialized before using the ADO object. Initialization COM environment can be done with the following code:
:: Coinitialize (NULL);
After initializing the COM environment, you can use the ADO object. If this code is not added in front of the program, a COM error will be generated.
After using the ADO object, you need to use the following code to release the initialized object:
:: Couninitialize ();
This function clears the COM environment prepared for the ADO object.
6.23 Introduction
The ADO library contains three basic interfaces:
__ConnectionPTR interface,
__Commandptr interface,
__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 with the __connectionptr interface is not a good way of use. Usually, like CDATABASE, use it to create a data connection, then use other objects to perform data input output operations.
__Commandptr The 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 only one or more data access is performed, the latter is a better choice. But if you want to access the database frequently and return a lot of record sets, you should use the global __connectionptr interface to create a data connection, then use the __commandptr interface to perform the stored procedure and SQL statement. __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 created data connection, 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 Global-ConnectionPTR interface that has created a data connection as the Command object, and then use the __recordse7tptr to perform the stored procedure and SQL statement.
6,24 Using ADO Access Database
__ConnectionPtr is a connection interface, first create a __connectionPTR interface instance, then point to and open an ODBC data source or OLE DB data provider (Provider). The following code creates a DSN and non-DSN-based data connection.
// Use __connectionptr (based on DSN)
__ConnectionPtr MyDB;
Mydb.createInstance (__ uuidof (connection));
MYDB-> Open ("DSN = SAMP; UID = admin; pwd = admin", "", ", - 1);
// Use -ConnectionPTR (based on non-DSN)
__ConnectionPtr MyDB;
Mydb.createInstance (__ uuidof (connection));
Mydb.open ("provider = sqloledb; server = server; database = SAMP; UID = admin; pwd = admin", ",", ", - 1);
// Use __recordsetptr to execute SQL statements
__Recordsetptr myset;
MySet.createInstance (__ uuidof (recordset));
MySet-> Open ("SELECT * from Some__Table", Mydb.GetInterfacePtr (), AdoPENDYNAMIC, ADLOCKTIMISTICISTICISTIC, 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 __recordsetptr interface, it is not necessary to frequently use the large and complex data structure Variant like Dao, and forced to convert various data types, this is also one of the advantages of ADO. Assume that the program has a listbox control named m__list. The following code we use the __recordsetPtr interface to get recordset data and populate this ListBox control:
__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)
{Cstring 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 runs an error (such as the database does not exist), the OLE DB data provider will automatically create a __com_rm_rror object and populate the error message to the member variable of this object.
6.25 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 two objects. They provide a general method to convert COM objects and C types of data.
6.3 Using ADO in VB
ADO provides ways to do the following:
1. Connect to the data source. At the same time, it is determined whether all changes to the data source have been successful or not.
2. Specify the command to access the data source and can be used with variable parameters, or optimize it.
3, execute the command.
3. If this command returns the data in the form of rows in the table, these rows are stored in a cache that is easy to check, operation, or changes.
4. Under appropriate cases, the data source can be updated using the change of the cache line.
5. Provide conventional methods to detect errors (usually caused by establishing a connection or execution command).
In a typical case, all of these steps are required in the programming model. However, since ADO has strong flexibility, you can do some useful work at the end only need to perform some modules.
The following elements are key parts in the ADO programming model:
6.31 connection
With "connection", you can access the data source from the application, the connection is the environment necessary to exchange data. The object model uses the Connection object to make the connection concept avatar.
"Transaction" is used to define the beginning and end of a series of data access operations that occur during the connection. ADO can clarify changes or successful changes in the operation in the transaction, or have not occurred at all. If the transaction is canceled or its operation fails, the final result will seem that the operation in the transaction has not occurred, and the data source will remain the state before the transaction begins. The object model cannot clearly reflect the concept of transactions, but in a group of Connection object methods. ADO Accesses data and services from the OLE DB provider. The Connection object is used to specify a special provider and any parameters.
6.32 command
The "command" emitted by the established connection can operate the data source in some way. In general, the command can add, delete, or update data in the data source, or retrieve data in the form of rows. Object models reflect command concept with Command objects with Command objects. Use the Command object to enable the execution of the ADO optimization command.
1, parameter
Typically, the variable part required by the command is "parameter" can be changed before the command is released. For example, you can repeat the same data retrieval command, but each time you can change the specified search information.
The parameter is very useful for the executable commands as the function activity, so you know what the command is doing, but you don't have to know how it works. For example, a bank transfer command can be issued, from one party to the other party. You can set the amount of payee to the parameter. Object models use parameter objects to reflect parameters concept.
6.33 record set
If the command is a query that returns data in the table (row return query), these rows will be stored locally.
The object model refers to the storage as a Recordset object. However, there is no object that represents only a single RECORDSET line only.
The recordset is the most important method for checking and modifying data in the row.
6.34 field
A record collection line contains one or more "fields". If the record set is regarded as a two-dimensional grid, the field will be arranged to "column". Each field (column) contains the properties of the name, data type, and value, which is in this value that contains real data from the data source.
The object model reflects fields in the field object.
To modify the data in the data source, you can modify the value of the Field object in the record collection line, and the change to the record set is ultimately transmitted to the data source. As an option, the transaction management method of the Connection object can reliably guarantee that the changes are all successful, or all failed.
6.35 error
Error can occur in the application at any time, usually due to the operation of the connection, execution command, or some state (for example, trying to use a recordset without initialization).
The object model reflects an error in Error object.
Any given error generates one or more Error objects, and the resulting error will give up the previous ERROR object group.
6.36 attribute
Each ADO object has a set of unique "properties" to describe or control the behavior of the object.
The attribute has two types of built-in and dynamics. The built-in attribute is part of the ADO object and is available at any time. Dynamic properties are added by the special data provider to the properties of the ADO object, only when the provider is used.
The object model reflects attributes in a Property object.
6.37 collection
ADO provides a "collection", which is convenient to include object types of other special type objects. Use the collection method to retrieve the objects in the collection according to the name (text string) or serial number (integer).
ADO provides four types of sets:
The Connection object has an Erroors collection that includes all Error objects created for a single error associated with the data source.
The Command object has a parameters collection that contains all Parameter objects that are applied to the Command object.
The Recordset object has a Fields collection that contains all Field objects that define the Recordset object column.
In addition, Connection, Command, Recordset, and Field objects have a Properties collection. It contains all Property objects that belong to each containing objects.
The ADO object has an attribute that can set or retrieve values such as "Integer", "Character", or "Boolean" that can be used thereon. However, it is necessary to see some properties as the return value of the data type "Collection Object". Correspondingly, the collection object has a method of storing and retrieving other objects suitable for this collection.
6.38 incident
ADO 2.0 supports events, events are notifications for some operations or have already occurred.
There are two types of events: ConnectionEvent and RecordseTevent. The Connection object produces a ConnectionEvent event, and the Recordset object produces a RecordseTevent event.
The event is handled by an event handler routine, which is called before or after an operation begins. Some events are paired. The event name format called before starting operation is Willevent (Will Event), and the event name format called after the operation is EventComplete (Complete event). The rest of the incident only occurs after the end of the operation. (The name does not have any fixed mode.) The event handler is controlled by the status parameter. Additional information is provided by errors and object parameters.
You can request an event handler to do not accept any notifications after the first notification. For example, you can choose to receive only WILL events or Complete events.
The following code shows an example of using ADO.
First join Microsoft ActiveX Data Object 2.0 library reference.
DIM DB AS Connection
SET DB = New Connection
db.cursorlocation = aduseclient
DB.Open "provider = msdasql; dsn = testdatabase", "sa", "", -1
DIM I as long
DIM ID as long
DIM Value As Single
DIM RST AS New Recordset
Set RST = New Recordset
Rst.open "SELECT * FROM Mechanistic Change Historical Table", DB, AdoPENDYNAMIC, ADLOCKOPTIMISTIC
Rst.Movefirst
For i = 0 to Rst.Recordcount - 1
ID = RST.Fields ("ID")
Value = Rst.fields ("Value")
Rst.movenext
Next I
Rst.Close
SET RST = Nothing
Db.close
6.4 Summary
ADO technology is a new technique for accessing the database, which is easy to use, flexible, and widely used. The characteristics of accessing the data source with ADO can be summarized as follows:
1, easy to use
This is the most important feature of ADO technology. Since ADO is a high-level application, it has an object-oriented property relative to OLE DB or ODBC. At the same time, in the object structure of the ADO, the hierarchical relationship between its objects is not obvious. In terms of access techniques such as DAO, there is no need to care for the structure of the object and the construction level. For objects to be used, you don't have to select an object such as a connection, a session, etc., you can easily construct it, it is convenient for the application.
2, high-speed access data source
Since the ADO technology is based on OLE DB, it also inherits the high speed of the OLE DB access to the database.
3, you can access different data sources
ADO technology can access all file systems including relational databases and non-relational databases. This feature makes the application much more flexibility and versatility.
4, can be used for Microsoft ActiveX Pages
ADO technology can appear in the form of ActiveX controls, so that can be used for Microsoft ActiveX pages, this feature simplifies the programming of the web page.
5, the program occupies less memory
Since ADO is a component object model (COM) access technology, the application generated by ADO occupies less memory.
7, summary
It is not easy to judge which technology should be used when accessing the data. A common utility may require a utility to handle multiple database types; some data may appear on the local hard drive, partially on the network, and some on the host. Even products that customers install on the device will make this choice more difficult. For example, what you expect ODBC support may depend on the version installed by Microsoft Office because this product does not provide ODBC support. You will also find that the objects and methods provided by the ADO class are more than the ODBC class. Ado can provide some feature programs that must be absolutely programs. For example, you will find that OLE-DB and ADO support DFX_CURRENCY, but there is no corresponding function in ODBC, but you have to master them must pay a certain effort. When OLE-DB or ODBC, there are several general rules. Because ADO is actually only the package of OLE-DB, these rules apply to it. Some basic principles are provided below, which can be used to help you decide to choose OLE-DB or ODBC.
Non-OLE Environment If you want to access a database that supports ODBC, the database is not supported on the OLE server, then ODBC is the best choice.
Non-SQL Environment ODBC is very outstanding when processing SQL. When processed non-SQL databases, OLE-DB has a very significant advantage.
OLE Environment For support OLE's servers, select OLE-DB or ODBC may be a half. If there is an ODBC driver to be available, use ODBC is a good idea; otherwise, only OLE-DB is selected.
The required interoperability If you need interoperable database parts, only OLE-DB is selected.
8. Reference book:
1. Hope book creation, "ODBC3 Programmer Reference and SDK Guide", 1999
2, Zheng Zhang, Cheng Gang, Zhang Yong is waiting, "Visual C 6.0 Database Development Technology", 2000
3. Hope book creation, "Visual C 6.0 Technology Insider", 1999
4. Hope book creation, "Visual C and Database Management", 1999
5, hope book creation, "Visual Basic Professional Edition step by step", 1999
