Consistent Data Access Technology ADO / OLE DB
Pan Aimin
Microsoft has recently launched UDA (Universal Data Access, unanimous data)
Question technology) provides an agreementary or non-relational data access providing a consistent access interface for companies
The Intranet application multi-layer software structure provides a data interface standard. Consistent data access package
Including the two-layer software interface, which is ADO (Active Data Object) and OLED B, respectively
Different levels of application development, ADO provides high-level software interfaces, available in various scripting languages (SC
Ript) or some macro language directly; OLE DB provides the underlying software interface, can be in C / C
Direct use in languages. The ADO is based on OL E DB, which is packaged in OLE DB.
Consistent Data Access Technology is based on Microsoft's COM (Component Object Model), it
Including a set of COM component programs, components and components, or between components and client programs
Standard COM interface for communication.
Since ADO is based on automation (Automation), ADO's application occasion
Very extensive, not only in the high-level language development environment like Visual Basic,
You can use in some scripting languages, which is developed for web applications, in ASP (Active Serv)
The shortcut of the operating application is provided in the script code access database. This article will be the first
First introduce the structural model of ADO and OLE DB, so that readers understand the relationship between ADO and OLE DB.
Department, then introduce ADO's object model and some features of ADO, and we will also pass some
Example code shows the reader to the usage of ADO.
First, unanimous data access introduction
With the continuous development of network technology and database technology, the current application system
Integration requirements are getting higher and higher, which may be distributed in different places, and
The same format, such as files, spreadsheets, electrons in relational databases and operating systems
Mail, multimedia data, and directory service information, etc. Traditional solutions are large
Type database system, move all of these data to the database system, then follow the operation
The database can be accessed, so that although it can be paired in a unified way
Data make a variety of operations, but this indirect access has brought a lot of problems, such as data more
New is not timely, the redundancy and access efficiency of space resources are low.
Consistent data accesses in Microsoft companies solve these questions
Troune, it makes it access to a variety of data through a consistent interface, regardless of the data station
Where to stay, you don't need to perform data transfer or copy, conversion, while implementing distributed
It also brings high efficiency. And the UDA technology is connected to the interface in unified data, its multi-storey
The structure has more choice opportunities for data users, and its powerful extension capacity is also
According to the provider, more expansions have been left, this open-type software structure makes it extreme
Strong vitality, so this technology has been widely welcomed from a launch, it can be said that UD
The A technology is a leap in another data access technology after ODBC.
UDA technology includes OLE DB and ADO two-layer standard interface, OLE DB is a system-level programming
Interface, it defines a set of COM interfaces, this set of interfaces encapsulates access to various data systems.
For this set of interfaces, this set of interfaces have established standards for the data user and the data provider. OLE DB is also available.
A set of standard service components for providing queries, cache, data updates, transaction processing, etc.
Operation, therefore, the data provider only needs some simple data operation, and it can be
To achieve all of the data control capabilities.
ADO is the programming interface of the application layer, which accesses data via the COM interface provided by OLE DB.
It is suitable for a variety of client / server applications and web-based applications, especially in some
Accessing database operations in scripting languages is the main advantage of ADO. ADO is a set of automated techniques
Object hierarchy is built, it is more than some of the other object models such as DAO (Data AC
Cess Object), RDO (Remote Data Object), etc. Have better flexibility, use
It is more convenient, and the access data is more efficient.
Figure 1 shows a software hierarchy model for unified data access.
Figure 1 Hierarchical diagram of UDA
As we can see from the figure, the application can be directly straight through the ADO access data.
Access the data through the OLE DB, and the ADO accesses the underlying data through the OLE DB. Moreover, OLE
DB is divided into two parts, part of the data provider, including some basic functions, such as getting
Data, modify data, add data items, etc .; another part is provided by the system, including some high
Level services such as cursor functions, distributed queries, etc. Such hierarchies are both data
User, the application provides a variety of options, and simplifies service power for the data provider.
Can achieve a means of implementation, it only needs to write a COM component program by pressing the OLED B specification, making
Third-party publishing data is easier, and the application can get a comprehensive functional service,
This fully reflects the advantages of two layers of OLE DB.
Since OLE DB and ADO are implemented in the form of COM components, various specials of COM components
Sex also makes it more flexible to build data applications, not just a general client / service.
Merry or web application model, which is suitable for data-centric applications, but also suitable for multi-layer knots
Distributed software system. Through the configuration of COM components, we can build a variety of complexities.
Application system. With the transparent technology from COM to DCOM, we can be very convenient
Distributed application system; running the ring using MTS (Microsoft Transaction Server)
We can also increase security control in data access, and use MTS object tube
Ellose data access efficiency is higher. All these features require data providers to write code
Now, you only need to conduct a regular configuration in the DCOM or MTS environment.
The core of which can be said consistent data access technology is OLE DB, OLE DB established data
Access standard interface, which puts all data sources throughout the abstract formation row set (ROWSET)
Read. The OLE DB model mainly includes some of the following COM objects:
(1) Data Source Object Data Source objects correspond to a data
It is responsible for managing user privileges, establishing initial operations such as connection with data sources.
(2) Session (session) object establishes a session object on the basis of a data source connection.
The object provides a transaction control mechanism.
(3) Command (Command) Object Data User Using Command Objects to perform various data
Operation, such as query command, modify command, etc.
(4) Rowset object provides an abstract representation of data, it can be commanded
The results of the line can also be generated directly by the session object, it is the main operation object of the application.
.
OLE DB object model is very simple, this simplicity also brings flexibility, from above
Several COM objects can also be seen. Below we will start from the angle of application layer.
The analysis and use of ADO object model structure based on OLE DB is used to help readers
Further understand consistent data access technology.
Second, ADO object model
The ADO object model defines a set of programmable automation objects available for Visual Bas.
IC, Visual C , Java, and other all kinds of scripting languages that support automation features. AD
O First access data in Microsoft Internet In Formation Server
The interface of the library, ADO can be better used in the network environment than the general database interface.
Optimization technology, it reduces network traffic as much as possible; another feature of ADO is simple, not
Just because it is a database interface for advanced users, it is more because it uses a set of simple
The interface is used to handle various data sources. These two characteristics make ADO will replace RDO and DA
O, becoming the final application layer data interface standard.
From Figure 1, we also see that the ADO is actually the application layer interface of OLE DB, this structure
Also provide good scalability for the consistent data access interface, not limited to specific
Data source, therefore, ADO can process data sources supported by various OLE DBs.
Figure 2 is an object model diagram of ADO.
Figure 2 ADO object model
In the ADO model, there are only 3 subject objects: Connection, Command and Records
ET, other 4 collection object errors, proties, parameters and fields
Corresponding to Error, Property, Parameter and Field objects, the entire ADO object model
These objects are composed.
A typical ADO application uses the Connection object to establish a connection with the data source,
After a Command object gives a command to operate, such as query or update
According to the like, the RECORDSET is used to maintain or browse the result set data. COMM
The command language used by the AND command is related to the OLE DB data source corresponding to the underlying.
Data sources can use different command languages, for relational databases, usually use SQL
For the command language.
In the three objects of Connection, Command, and Recordset, the Command object is an optional object. Is it effective dependent on whether the OLE DB data provider implements ICOMMAN
D interface. Since the OLE DB can provide relational data sources, non-relational data sources can also be provided.
So use the traditional SQL command query data in non-relational data sources.
You cannot use the Command command object.
From the structure, the ADO model is very simple, but it is very flexible in use.
Discuss from the perspective of a single object:
(1) Connection object Connection object represents one between the data source
Connection, the CONNEC TION object of ADO encapsulates the OLE DB data source object and session object
. According to the different performance of the OLE DB provider, the characteristics of the Conne Ction object are also different.
Therefore, the method and attribute of the Connection object are not necessarily available. Using Connect
ION object, we can do some of the following basic settings.
a. Set by Connectionstring, ConnectionTimeout and Mode Properties
Connection string, timeout information, access mode.
b. You can also set the CURSORLocation property to specify the client's game.
Use a batch modification method in the customer program.
c. Set the default database properties of the connection DEFAULTDATABASE.
d. Set the properties provike of the OLE DB provider.
e. Configure the connection of the Connection object with the physical data source via the Open and Close.
f. Execute the command via the Execute method.
g. Provide transaction mechanisms through Begintrans, CommitTrans and RollbackTran
S method realizes transaction control.
h. Check the error message of the data source through the ERRORS collection attribute.
i. Get table information of the database through the OpenSchema method.
The Connection object is one of the basic objects of ADO, which is independent of all other objects
. If we want to query the database, you can use the Execute method or
Use the Command object. It is easy to use the Execute method, but with the Command object
In order to save the command, you can query multiple times.
(2) Command object Command object represents a command, can be implemented
Line for data sources, such as queries, modifications, etc. The usage of the Command object is like
under:
a. Set the command string through the CommandText property.
b. Define parameterized queries by parameters collection attributes and parameter objects or
The parameter of the stored procedure.
c. Run the command, possible words through the Execute method, return to the Recordset object.
d. Before executing the command, you can set the CommandType property to optimize performance.
e. You can instruct the underlying provider to prepare one for the current command through the Prepared property.
The compiled version, the speed will be greatly accelerated when executed.
f. Set the timeout value (in seconds) of the command execution through the CommandTimeout property setting command
.
g. You can set the ActiveConnection property to specify the connection string for the command, Command
Objects will create C Onnection objects internally.
h. You can set the Name property so that you can follow the corresponding Connection object.
Perform the name specified by the Name property.
When the Command object is executed, it can be connected to the ActiveConnection property.
The Connection object can also be independent of the Connection object, directly specify the connection string,
Even if the connection string is the same as the connection string of the Connection object, the Command object is still using it.
The internal data source is connected.
(3) Recordset object RecordSet object represents a list of records or commands
The result of the execution is in the record set, there is always a current record. Record set is ADO management
Basic objects of data, all RecordSet objects are in accordance with the historical manner
Row management, each row corresponds to a record, and each column corresponds to a domain (Field).
The Recordset object also access the record through the cursor. In ADO, the cursor is divided into four types.
:
Static cursors provide a static copy of the data set, allowing various mobile operations, including
Forward movement, post-transfer, etc., but other users do not reflect.
Dynamic cursors allow various mobile operations, including forward movement, post-transfer, etc., and other users do can also be directly reflected.
The forward cursor allows various forward moving operations, not moving backwards, and other users
Operation can also be reflected directly.
Keyset cursor is similar to a dynamic cursor, and it can also see other users.
Data modification, but you can't see other users added records, and you cannot access other users to delete
record of.
The Usage of the Recordset object is as follows:
a. Set the cursor type with the Cursortype property.
b. Open the recordset data through the Open method, you can both in Open to ACTIVECON
The NECTION attribute assigns, specifying the Recordset object to use the connection object, or directly in O
The connection string parameter is specified in the PEN method, and the ADO will create an internal connection, even if the connection string is outside
The connection object is the same, and it also uses a new connection object.
The C.Recordset object is just turned on, the current record is positioned in the first record, and Bof
And the EOF flag attribute is F alse, if the current recordset is a empty record set, Bof and EOF standard
Zhi property is TRUE.
d. Through MoveFirst, MoveLast, MoveNext and MovePrevious methods
Move with the cursor of the record set. If the OLE DB provider supports related functions
If you can use AbsolutePosition, Absol Utepage, and Filter properties to current
Record repositioning.
E.ado offers two record modifications: Immediate modifications and batch modifications. Modify immediately
In the mode, once the U PDATE method is called, all the modifications to the data are immediately written to the bottom layer.
Data source. In the batch modification method, you can modify multiple records, then call UPDA
The tebatch method submits all modifications to the underlying data source. After submit, you can use Stat
US Property Check data conflicts.
The Recordset object is the core of the ADO data operation, which can be used as a Connection
Object or Command object performs a result of a specific method, or can be independent of these two
The object is used, thereby it can be seen that the flexibility of the ADO object is used.
The above three objects contain the properties of a Property object collection, through Property
Objects allow ADO to expose the performance of the underlying OLE DB provider. Because not all
The underlying provider has the same performance, so ADO allows users to dynamically access the underlying
The ability of people. This makes ADO flexible and provides good scalability.
Other collection objects of ADO and their element objects are used in a specific context environment.
For example, the Parameter object must be linked to a Command object.
effect. And the other three objects, ERROR, and Property objects can only be attached to their father.
Objects cannot be created separately.
Third, use ADO in multiple languages
The above introduced ADO object model, now let's discuss how to speak in different languages
Use ADO objects in the environment.
Because ADO is implemented as an automation component program, we can support any support.
Use ADO in the language environment of COM and automation features, such as Visual Basic, Visual
C , ASP, and Java, etc., the following is introduced.
1. Using ADO in Visual Basic
Visual Basic Applications can be created and used in design patterns and operation mode
Through the object, in the design mode, the object library like ADO can be used as an internal object
Use, we only need to pop up the dialog box that "Ref Erences" command under the "Project" menu
The ADO object library "Microsoft ActiveX Data Objects Libra RY",
We can declare or create an ADO object directly in the program, as follows:
DIM CN AS New Adodb.Connection
DIM CMD As New AdoDb.command
DIM RS As New Adodb.Recordset
It can be seen that it is very convenient to use ADO objects when designing, and Visual Basic
The object browser feature provided in the environment allows users to be conveniently
Check the properties and methods of the ADO object.
We can also create an automated object at runtime, using the CreateObject function of Visual Basic, can create any automation object, because only Connection is available in ADO
Objects, Command objects and RecordSet objects can be created independently, so we only
You can create these three objects, for example:
DIM CN
SET RS = CREATEOBJECT ("AdoDb.Connection")
DIM CMD
SET RS = CREATEOBJECT ("AdoDb.command")
DIM RS
SET RS = CreateObject ("AdoDb.Recordset")
Whether it is design mode or operation mode, the properties and methods of calling the ADO object are very
Simple, call directly.
2. Use ADO in ASP's VBScript
ADO objects can also be used for VBScript scripts for HTML and Active Server Page
Code, VBScript script code is similar to the code of Visual Basic, which is embedded in HT
The ML or ASP file is within the interior. But VB Script Engine is longer than Visual Basic
The design environment or run library is still weaker, first, in the VBSCRI PT code,
There is no similar use with the design environment, the VBScript engine cannot be loaded into the ADO type library, so
You can't create an ADO object with the New operator, but you can use the CreateObject function to create
Object; Secondly, the constant used in the ADO object library can only be introduced by including files, with ADO
The supplied Adovbs.inc file contains all the definitions of all ADO constants, we can code on scripting
This file is included directly.
Therefore, in order to use ADO in the VBScript code, you must first contain adovbs.inc.
Part, then create an ADO object using the Create Object function, you can call these pairs in the future.
The properties or methods of icons. The following example shows listing data sheets in ADO in ASP files.
All the names and titles of all authors, the code is as follows:
USING ADO IN A Visual Basic Script Web Page
USING ADO IN A Visual Basic Script
Web Page
MyConnection.open "DSN = mysamples; uid = sa"
SQLQuery = "SELECT Authorname, Title from Authordb"
SET RS = MyConnection.execute (SQLQuery)%
Width = 200
Title ID
SIZE = 1
Title
"arial narrow" size = 1
"arial narrow" size = 1
3. Using ADO in Visual C
There are many ways to use ADO in Visual C , the first method is to use CoCr
The EateInstance function creates an ADO object and gets the IDispatch interface pointer of the object.
After calling its Invoke function, use this method to handle parameters and return values, AD
O Provide adoid.h and adoint.h header files defined the CLSID and interfaces of the ADO object, respectively.
ID; the second method is to use the #import compilation indicator (after Visual C 5.0 and later
You can use the version of the version, you can easily use the ADO object; the third method is to utilize MFC (
Idispatch interface package Coled provided by Microsoft Foundation C Lass
ISPATCHDRIVER Create and call ADO objects.
The following code shows the process of creating a data source connection in Visual C :
Guid ConnectionClsid;
HRESULT HRESULT = :: CLSIDFROMPROGID (l "adoDb.connection",
& connectClsid; if (failed (HRESULT))
{
......
}
Idispatch * pdispatch = null;
HRESULT = CocreateInstance (ConnectionClsid, NULL,
CLSCTX_SERVER, IID_IDISPATCH, (VOID **) & PDISPATCH;
Failed (HRESULT))
{
......
}
ColedispatchDriver DRIVER DRIVER;
Driver.attachdispatch (pdispatch, false);
Try
{
BYTE PARMS = VTS_BSTR;
Driver.invokehelper (0xA, Dispatch_method,
Vt_empty, & hResult, Parms, L "provider = sqloledb;
User ID = sa; password =; "l" initial catalog = leaves;
Data Source = NettestServer ");
}
END_TRY
Driver.detachdispatch ();
......
4. Using ADO in Java
In the Java program, you can introduce the AdoDB class, then declare the ADO variable, you can also use NE.
The W operator creates an ADO variable. The following code illustrates how to open and data sources in Java
connection:
Import msado10. *;
_Connection m_conn = null;
_Recordset M_RS = NULL;
_Command m_cmd = NULL;
Void OpenConnection ()
{
String S;
Properties Properties;
Try
{
Properties = m_conn.getproperties ();
M_Conn.open ("DSN = mysamples", "sa", "");
Properties = NULL;
}
Catch (Exception E)
{System.out.Println ("/ NUNABLE to make a connection / n")
;
}
}
(Author Address: Research Institute, Peking University, 100871, Receipt Date: 1
999.02)