Microsoft's newly launched Universal Data Access, UDA technology provides a consistent access interface for relational or non-relational data access, providing a data interface standard for enterprise intranet application multi-layer software structure. Consistent data access includes two-layer software interface, which is ADO (Active Data Object) and OLE DB, respectively correspond to application development of different levels, ADO provides high-level software interface, which can be in various scripting languages (script) or some macro language. Direct use; OLE DB provides a low-level software interface that can be used directly in a C / C language. ADO is based on OLE DB, which is encapsulated in OLE DB. Consistent Data Access Technology is based on Microsoft's COM (Component Object Model), which includes a set of COM component programs, components and components, or communication between components and client programs, by standard COM interfaces. Since ADO is based on automation, ADO's application is very broad, not only in the high-level language development environment such as Visual Basic, but also in some scripting languages, this for development web applications, in ASP (Active Server Page) script code Access database provides shortcuts. This article will first introduce the structural model of ADO and OLE DB, so that the reader understands the relationship between ADO and OLE DB, then introduces ADO's object model and some features of ADO, and we will also show the reader to readers through some examples. . I. Consistent Data Access Introduction With the continuous development of network technology and database technology, the current application system is increasingly demanding data integration, which may be distributed in different places, and use different formats, such as relationships. Database, electronic form, email, multimedia data, directory service information, etc. in the operating system. Traditional solutions are to use large database systems to move all of these data to the database system, and then access these data according to the way of managing the database, so that the data can be made in a unified manner, but This indirect access has brought a lot of problems, such as data update, no timely, space resource redundancy, low access efficiency. The consistent data access technology introduced by Microsoft has better solved these problems, which allows applications to access a variety of data through a consistent interface, regardless of where the data is resident, and does not need to perform data transfer or Copy, conversion, also brings high efficiency while achieving distribution. And the UDA technology is in the unified data access interface, its multi-layer structure makes the data user have more choices, and its powerful extension capabilities have left more expansions for the data provider, this The open software structure makes it a strong vitality, so this technology has been widely welcomed from a launch. It can be said that UDA 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 that defines a set of COM interfaces. This set of interfaces encapsulates access to various data systems. This set of interfaces are data user and The data provider establishes a standard. OLE DB provides a set of standard service components for providing query, cache, data update, transaction processing, etc., so data providers only need to implement some simple data operations, in use All data control capabilities can be obtained. ADO is the application layer programming interface, which access data through the COM interface provided by OLE DB, which is suitable for various client / server applications and web-based applications, especially in some scripting languages, which is the main advantage of ADO.
ADO is an object hierarchy established with automation technology, which is more convenient than other object models such as DAO (Data Access Object), RDO (Remote Data Object), and more convenient. And the access data is higher. Figure 1 shows a software hierarchy model for unified data access. Figure 1 UDA hierarchical diagram we can see from the figure, the application can access data directly through the OLE DB via the ADO access data, while ADO accesss the underlying data by OLE DB. Moreover, OLE DB is divided into two parts, part of the data provider, including some basic functions, such as acquiring data, modifying data, adding data items, etc., another part is provided, including some advanced services, such as cursor functions, distributed queries and many more. Such hierarchies provide a variety of options for data users, but also simplify service functions for the data provider. It only needs to write a COM component program by pressing the OLE DB specification, making third parties Publishing data is easier, and in the application side can get a comprehensive functional service, this fully reflects the advantages of the OLE DB two-layer structure. Since OLE DB and ADO are implemented in the form of COM components, the various features of COM components have also made building data applications more flexible, not just a general client / server or web application model, which is suitable for data Central application, also suitable for distributed software systems for multi-layer structures. Through the configuration of COM components, we can build a variety of complex application systems. With the transparent technology from COM to DCOM, we can easily establish a distributed application system; use the MTS (Microsoft Transaction Server) to run the environment, we can also increase security control in data access, and use MTS object management Higher data access efficiency. All of these features require data providers to write code implementations, just perform regular configurations in the DCOM or MTS environment. The core of which can be said consistent data access technology is OLE DB, OLE DB establishes a standard interface for data access, which transfers all data sources through the concept of a row set (ROWSET). The OLE DB model mainly includes some of the following COM objects: (1) Data Source object. The data source object corresponds to a data provider, which is responsible for managing user rights, establishing initial operations such as connection with the data source. (2) Session (session) object. The session object is established on the basis of a data source connection, and the session object provides a transaction control mechanism. (3) Command (Command) object. Data users use command objects to perform various data operations, such as query commands, modify commands, and more. (4) Rowset object. The abstract representation of the data is provided. It can be the result of the command execution, or it can be generated directly by the session object, which is the main operation object of the application. OLE DB's object model is very simple. This simplicity also brings flexibility, which can also be seen from several COM objects. Below we will start from the application layer, by establishing an ADO object model structure based on OLE DB, to help readers further understand the consistent data access technology. Second, the ADO object model ADO object model defines a set of programmable automation objects, which can be used for Visual Basic, Visual C , Java, and other scripting languages that support automation feature.
The ADO is used to access the database in Microsoft Internet Information Server. Compared to the general database interface, ADO can be better used for network environments, and to reduce network traffic as much as possible by optimizing technology; another feature of ADO It is simple to use, not only because it is a database interface for advanced users, but because it uses a set of simplified interfaces to handle various data sources. These two characteristics make ADO will replace RDO and DAO to become the final application layer data interface standard. From Figure 1, we also see that the ADO is actually an application layer interface of OLE DB. This structure also provides good scalability for the consistent data access interface, and is no longer limited to a specific data source, so ADO can Handle data sources supported by various OLE DBs. Let's give an object model of ADO first: Figure 2 ADO object model In the ADO model, the main object is only three: connection, command and recordset, other four collection object errors, proties, parameters and fields respectively Error, Property , Parameter and Field objects, the entire ADO object model consists of these objects. A typical ADO application uses the Connection object to establish a connection with the data source, and then give a command to the database operation with a Command object, such as query or update data, and the RECORDSET is used to maintain or browse the result set data. The command language used by the command command is related to the OLE DB data source corresponding to the underlying, and different data sources can use different command languages, for relational databases, usually use SQL as a command language. In the three objects of Connection, Command, and Recordset, the Command object is an optional object that is effective dependent on whether the OLE DB data provider implements the ICommand interface. Since the OLE DB can provide a relational data source, you can also provide a non-relational data source, so use a traditional SQL command query data on non-relational data sources, and even the Command command object cannot be used. From the structure, the ADO model is very simple, but it is very flexible in use. Let's discuss from the perspective of individual objects: (1) Connection object. The Connection object represents a connection between the data source, and the ADO's Connection object encapsulates the data source object and session object of the OLE DB. According to the different performance of the OLE DB provider, the characteristics of the Connection object are different, so the method and attributes of the Connection object are not necessarily available. With the Connection object, we can do the following basic settings: a. Set the connection string, timeout information, access mode via Connectionstring, ConnectionTimeout and Mode properties. b. You can also set the CURSORLocation property to specify the client cursor to use the batch modification method in the client 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, implement transaction control through beginTrans, CommitTrans, and RollbackTrans methods. 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, we can use the Execute method or use a Command object. Use the Execute method to be relatively easy, but with the Command object to save the command information so that you can query multiple times. (2) Command object. Command object represents a command, which can perform the information for the data source, such as query, modification, etc. through its method. The usage of the Command object is as follows: a. Set the command string through the CommandText property. b. Define parameters for parametric queries or stored procedures through Parameters collection properties and Parameter objects. 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 indicate a compiled version to the current command through the pre previous property, and it will be greatly accelerated when executed. f. The timeout value (in seconds) performed by the CommandTimeOut property setting command is set. g. You can set the ActiveConnection property, specify the connection string for the command, and the Command object will create a Connection object internally. h. You can set the Name property so that you can execute the method name specified by the Name property on the corresponding Connection object. When the Command object is executed, you can specify the connected Connection object by the ActiveConnection property, or you can specify the connection string independently of the Connection object, even if the connection string is the same as the connection string of the Connection object, the Command object still uses its internal data source connection. (3) RECORDSET object. Recordset object represents a result of a table's recordset or command execution, in the recordset, there is always a current record. The record set is the basic object of the ADO management data. All RecordSet objects are managed in a row-like manner, each line corresponds to a record, and each column corresponds to a field. The Recordset object also access records through the cursor, in the ADO, the cursor is divided into four: a. Static cursor. Provides a static copy of the dataset, allowing a variety of mobile operations, including forward move, post-transfer, etc., but other users do not reflect. b. Dynamic cursor. Allow various mobile operations, including forward movement, post-transfer, etc., and other users do can also be reflected directly. C. Before the cursor. Allow various forward moving operations, not moving back, and other users do can also be directly reflected. d. Keyset cursor. Similar to a dynamic cursor, you can also see the data modifications made by other users, but you cannot see other users' new records, and you cannot access records from other users. The Usage of the Recordset object is as follows: a. Use the Cursortype property to set the cursor type. b. Open the recordset data through the open method, you can assign a value to the ActiveConnection property before the Open, specifying the Recordset object, can also specify the connection string parameter directly in the open method, then ADO will create an internal connection, even As with external connection objects, it also uses new connection objects. c. When the RecordSet object is just open, the current record is positioned in the first record, and the BOF and EOF flag attribute are false. If the current recordset is a empty recordset, the BOF and EOF flag properties are TRUE. d. Move the cursor of the recordset by movingfirst, MoveLast, MoveNext and MovePRevious methods.
If the OLE DB provider supports the relevant features, you can reposition the current record using the AbsolutePosition, AbsolutePage, and Filter properties. e. ADO provides two record modifications: Immediate modification and approval modifications. In immediately modify the mode, once the Update method is called, all changes to the data is immediately written to the base source. In the batch modification mode, multiple records can be modified, then call the UpdateBatch method to submit all modifications to the underlying data source. After submit, you can check the data conflict with the Status property. The RecordSet object is the core of the ADO data operation. It can be used as a result of a specific method as a Connection object or a Command object, or can be used independently of these two objects, thereby seeing the flexibility of the ADO object in use. . The above three objects contain the properties of a Property object collection, allowing the ADO dynamic to dynamically exposing the performance of the underlying OLE DB provider via the Property object. Since all underlying providers have the same performance, ADO allows users to dynamically access the underlying provider's capabilities. This makes ADO flexible and provides good scalability. Other collection objects of ADOs and their element objects are used in a specific context environment. For example, the Parameter object must truly work with a COMMAND object. Other three objects Field, ERROR and Property objects can only be included in their parent objects, and these objects cannot be created separately. Third, in a variety of languages describe the ADO object model, now we discuss how to use ADO objects in different locations. Because ADO is implemented as an automated component program, we can use ADO in any locale supporting COM and automation features, such as Visual Basic, Visual C , ASP (Active Script Page), Java, etc., below. (1) Using the ADO Visual Basic application in Visual Basic Applications (referred to as VBA) can create and use automation objects in design mode and run mode, in design mode, object library like ADO can be used as internal objects We only need to select the ADO object library "Microsoft ActiveX Data Objects Library" in the "References" command pop-up under the "Project" command, so we can declare or create an ADO object directly in the program. For example: DIM CN As New Adodb.Connection Dim CMD As New AdoDb.Command Dim Rs As New Adodb.Recordset It can be seen that using ADO objects at design time is very convenient, and the object browser (Object Browser) feature provided in the Visual Basic design environment allows Users can easily view the properties and methods of the ADO object.
We can also create an automated object at runtime. Use the Visual Basic's CreateObject function to create any automation objects. Since only Connection objects in the ADO, Command objects and RecordSet objects can be created independently, so we can only create these three objects. Examples are as follows: 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 ADO objects are very simple, and they can be called directly. (2) Using the ADO ADO object in the VBScript of Active Server Page can also be used for the VBScript script code of HTML and Active Server Page. VBScript script code is similar to the code of Visual Basic, which is embedded in HTML or ASP files. Internally. However, the VBScript engine is functionally weak than Visual Basic's design environment or run library. First, in the VBScript code, there is no similar use with the design environment, the VBScript engine cannot be loaded into the ADO type library, so the new operator cannot be used. Create an ADO object, but you can use the CreateObject function to create an object; secondly, the constant used in the ADO object library can only be introduced by including files, and the Adovbs.inc files provided together with the ADO include all ADO constants, we can code on script code This file is included directly. Therefore, in order to use ADO in the VBScript code, first include an adovbs.inc file, then create an ADO object using the CreateObject function, and then call the properties or methods of these objects. The following example shows the name and title of all the authors in the data table in the ASP file, the code is as follows: <% @ language = VBScript%> Using Ado in a Visual Basic Script Web Page
USING ADO IN A Visual Basic Script Web Page
<% Set myConnection = CreateObject ( "ADODB.Connection") myConnection.Open "DSN = MySamples; UID = sa" SQLQuery = "select AuthorName, Title from AuthorDB" set rs = myConnection.Execute (SQLQuery)%>
Title ID Title <% do while not rs.eof%>
<% = rs ("authorname")%> <% = rs ("Title")%> <% rs.movenext%> <% loop%>
(3) Use ADO in Visual C to use ADO in Visual C , there are many ways, the first method, we use the COCREATEINSTANCE function to create an ADO object, and get the object's IDispatch interface pointer, then call its Invoke function, with this The method requires our own handles parameters and return values. The ADO provides adoid.h and adoint.h header files defined the CLSID and interface ID of the ADO object, respectively; the second method, using the #import compilation indicator (in Visual C 5.0 and You can use in later versions), you can easily use the ADOSPATCH interface encapsulation class COLEDISPATCHDRIVER with the Idispatch interface package ColedispatchDriver provided by the Microsoft Foundation Class library. The following code shows the process of creating a data source connection in Visual C : guid connectionclsid; hResult hResult = :: CLSIDFROMPROGID (L "AdoDb.Connection", & connectionclsid; if (failed) {... return;} iDispatch * pDispatch = NULL; hResult = CoCreateInstance (connectionCLSID, NULL, CLSCTX_SERVER, IID_IDispatch, (void **) & pDispatch); if (FAILED (hResult)) {...... return;} COleDispatchDriver 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 to introduce the AdoDB class in the Java program, then declare the ADO variable, or use the New operator to create an ADO variable. The following code shows how to open a connection to the data source in Java: 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", ""); proties = null;} catch (exception e) {system.out.println ("/ NUNABLE to make a connection / n ");}} 4, ADO advanced features In order to more fully introduce unanimous data access technology, this part will focus on some advanced features of ADO.
When writing a data access application, usually simply simply increase, delete, changed the data, but sometimes the situation is complex, we may need to consider performance, or you may need to support big data volume, or Support for long data types, etc., in which case we can consider some advanced features of ADO. These advanced features are described below. (1) Processing data definition language data definition language refers to some SQL statements that support database object maintenance, such as CREATE TABLE, DROP TABLE, or ALTER TABLE. Because the execution of these statements do not generate record sets, we don't have to use the Recordset object, just use the Command object. To distinguish between a Store Procedure or a data definition statement for data queries, the CommandType property of the Command object must be specified as adcmdtext. The following code shows this usage: DIM CN AS New Adodb.Connection Dim Cmd As New AdoDb.command 'if The adotEstTable Does Not Exist on Error Goto AdoError CN.Open "MySamples", "SA" set cmd.activeConnection = CN cmd.CommandText = "drop table MyTestTable" cmd.CommandType = adCmdText cmd.Execute cmd.CommandText = "set nocount on" cmd.Execute cmd.CommandText = "create table MyTestTable (id int, Name char (100))" Cmd. Execute cmd.commandtext = "INSERT INTOMITESTTABLE VALUES (1, 'Pan Aimin')" cmd.execute cn.close Exit Sub ADOERR: 'Handle Error ... (2) Using "Compiled" Command Objects The second part introduces the command command object, we have said that the provider that can indicate the underlying provider can prepare a compiled version for the current query command. When you execute this command, the speed will be greatly accelerated. If we set the previous property of the Command object to True before executing the command, the command is executed for the first time, and the performance will be significantly improved when the query command is called later, because the query string has been optimized. And "compiled" over. And the prepared property is also valid for parameterized query strings. Of course, not all OLE DB providers support this feature, SQL Server OLE DB supports this feature, when setting the prepared property is executed, the underlying provider actually creates a temporary stored procedure When this command is executed next time, the provider launches the stored procedure. This temporary stored procedure is also deleted when the command object or the connection object is deleted. If the Command object is only executed once, there is no meaning of using the Prepared property, and even reducing efficiency. The usage of the prepared property is relatively simple, and the code is no longer given here. (3) The stored procedure and parameter processing stored procedures are a bit similar to the "compiled" command object described above, and the stored procedure runs on the server, which can encapsulate complex application logic on one end of the server, and only need in the client program. Call a simple SQL statement.
If we want to execute a stored procedure in the ADO's Command object, you can specify the CommandType property as AdcmdStoredProc, of course, in the Command object of the stored procedure type, do not specify the prepared property to True. The usage of the stored procedure is very simple. Here is an example: DIM cmd as new adod.command Dim Rs as new adod.com.Recordset cmd.activeConnection = "DSN = mysamples; uid = sa" cmd.commandtext = "MyProc" cmd.commandType = AdcmdStoredProc Set RS = cmd.execute () Debug.print RS (0) rs.close stored procedure allows the input and output parameters and return values, corresponding to the Command object, these parameters are parameter objects, and the processing of input parameters is relatively simple, output The processing of the parameters and the return value is different, and only when the records of the RECIDSET object are all traversed or the RECORDSET object is turned off, the output parameters and the return value are really valid. For the following stored procedure: create procedure myproc @iopArm Int Output as select name from mytesttable where id <2 Select @iopArm = 1 Return 100 The following code performs this stored procedure: DIM CMD AS New AdoDB.Command Dim RS AS NEW AdoDB. Recordset Dim param As Parameter cmd.ActiveConnection = "DSN = MySamples; UID = sa" cmd.CommandText = "MyProc" cmd.CommandType = adCmdStoredProc 'Set up parameters Set param = Cmd.CreateParameter ( "Return", adInteger, adParamReturnValue,. , 0) cmd.Parameters.Append param set param = cmd.createParameter ("Output", Adinteger, Adparamoutput,, 0) cmd.Parameters.Append param set = cmd.execute if not = cmd.execute if NOT = cmd.execute if not = cmd.execute if NOT = cmd.execute if not = cmd.execute if not = cmd.execute if not = cmd.execute if not = cmd.execute if not = cmd.execute if not = cmd.execute if not = cmd.execute if NOT = Debug.Print Rs (0) rs.close end if debug.print cmd (0) 'The return code debug.print cmd (1)' The Output Parameter (4) Batch modification process We know the Update method of the Recordset object is used to modify The current record, and the UpdateBatch method is used to submit all, delete, and modified operations for the current recordset. Setting the LockType property of the Recordset object to AdlockBatchOptimistic, the UpdateBatch method is valid, of course, different OLE DB providers may have different requirements, for example, SQL Server providers also require cursor types to set cursors or static cursors.
The UpdateBatch method can transfer all the modifications of the client to the database once, or call the CancelBatch method to cancel all modifications. After the UpdateBatch method is called, the error handling is different from usually different. During the error handling process, you can use the filter attribute of the Recordset object to be AdfilterConflictingRecords, and then processes on the conflict record. Below we give an example of a batch process modification: Dim rs As New ADODB.Recordset rs.CursorLocation = adUseClient rs.CursorType = adOpenKeyset rs.LockType = adLockBatchOptimistic rs.Open "select * from MyTestTable", "DSN = MySamples; uid = SA "'Change The Type for a Specified Title. While (Not Rs.eof) IF TRIM (RS (" Title ") =" Instructor "THEN RS (" Title ") =" Engineer "End if rs.Movenext Wend RS .Updatebatch rs.close (5) Multi-record set processing Some SQL statements can generate multiple record sets, and stored procedures can also generate multiple records. In these cases, we can obtain a recordset one by one, using the NEXTRecordset method of the Recordset object, can sequentially obtain each recordset, if all record collections have been obtained, then the nextrecordset method returns Nothing. The following code illustrates this usage: Dim cmd As New ADODB.Command Dim rs As ADODB.Recordset Cmd.ActiveConnection = "DSN = MySamples; UID = sa" Cmd.CommandText = "MyNextProc" Cmd.CommandType = adCmdStoredProc Set rs = Cmd.execute () While Not Rs Is Nothing IF (Not Rs.eof) THEN Debug.print RS (0) End If Set Rs = rs.nexTRecordset () Wend (6) Client Code and Server-Roucher Corbine Service is Data Important content, when the second part introduces the RecordSet object, we explain the four types of the cursor, and there is a property of the Recordset object to specify the location of the cursor, we can specify a cursor using the client, or you can specify Server-side cursor. The default value of the CURSorLocation property is AduseServer. The advantage of using the server-side game is that the program can be reflected to the server immediately, and other users can reflect the operation of the database, but use the server-side game. High network traffic, each data access needs to exchange data over network. ADO provides client data cache processing, so before opening the Recordset object, you can set CursorLocation to aduseClient, specify the client cursor.
With the client game, ADO uses local data cache to reduce network traffic, although there is a loss in data access flexibility, but greatly improves the performance of the database through the network. (7) Long data type Processing The current application is increasingly inseparable from the long data type of the database, long data types include text or binary information, which can be used to record images, sounds, and other multimedia or non-multimedia information. Sometimes long data is not very long, you can read or assign a value directly through the value of the field object; sometimes long data is very long, it is difficult to put down in memory, then you can access the block operation by the Field object. The GetChunk method with the field object can read some or all of the data. When the memory uses a restriction, you can read the partial data with GetChunk, and continuously call Getchun to read all data. Correspondingly, we can continuously call the AppAndChunk method of the Field object to assign a value for a long data type domain of the current record. Note that getChunk and AppandChunk are only valid for the Attributes property to be ADFLDLONG's field object. Sometimes, in the Parameter object of the Parameters collection of the Command object, you also need to use long data types, so getChunk and AppandChunk methods are equally applicable to Parameter objects. Here's an example of using GetChunk and AppandChunk: Dim Cn As New ADODB.Connection Dim rsRead As New ADODB.Recordset Dim rsWrite As New ADODB.Recordset Dim strChunk As String Dim Offset As Long Dim Totalsize As Long Dim ChunkSize As Long Cn. Open "MySamples", "sa" rsRead.CursorType = adOpenStatic rsRead.Open "select My_LongData from MyTestTable", Cn rsWrite.CursorType = adOpenKeyset rsWrite.LockType = adLockBatchOptimistic rsWrite.Open "select * from MyBLOB", Cn ChunkSize = 1000 totalsize = rsRead ( "My_LongData"). ActualSize Do while Offset Even if the machine is turned off, when the machine is turned on later, execute the Open ("FileName",,, AdcmdFile, you can continue to perform the data operation. (9) Asynchronous operation and event response mechanism ADO version 2.0 supports asynchronous operations, when connecting data sources or performing recordset access, we can choose asynchronous mode. When using an asynchronous manner, when the OPEN is called, the function will return immediately. We can continue to perform other operations before waiting for the connection to complete. When the asynchronous operation is completed, we can receive a notification completed. The Recordset object also supports asynchronous operation when obtaining data in batches (requests to use the client game), which only returns the first record of the query results, and will continue to obtain the rear record in the background. In response to the asynchronous operation, the event mechanism, the Connection and RecordSet objects provide a lot of notification events, including the completion, commands, all stages of the transaction, and the record sets are available, and the ADO is available. Event notification. Through the event control function, we can write more standardized client programs to provide a more flexible front and back. 5. Using the ADO Access Data We have already introduced the structure and characteristics of ADO, and briefly introduce the method of calling ADO objects in different languages. Now we will further tell us how to use ADO to perform data access from the perspective of development applications. (1) How to establish a data source connection to establish a connection to use a connection string. The connection string contains all information used to establish a data source connection, which uses a series of formats to "parameter = value" statements, and connect them with ";". ADO establishes a connection according to the connection string, first find the "Provider" parameter information to call the appropriate OLE DB provider. ADO can understand the following four parameters: provider = file name = remote provider = remote server = ADO Transmits other parameter information to the provider, and the provider establishes a data source connection based on these parameter information. The Connection object can be used to establish a connection using the Connectionstring property, or may specify a connection string in the parameter of the Open method, or even combine it, if the same parameter information is simultaneously in the opensen parameter and the Connectionstring property, the OPEN parameter Information is accurate. We can also specify OLE DB providers through the Provider property of the Connection object. Here is some "provider" information currently available: MSDASQL - for OLE DB providers for ODBC; SQLOLDB - for OLE DB providers for SQL Server; AdsdsoObject - OLE DB provider for SQL Server; msidxs - OLE DB provider for Microsoft Index Server; Microsoft.jet.OleDb.3.51 - OLE DB provider for Microsoft Jet; Msdaora - OLE for Qracle DB provider. If the OLE DB provider is specified in the Open method parameter, the Connectionstring or the Provider property, ADO uses the default OLE DB provider "MSDasql" for ODBC. Hereinafter, three typical connecting string examples: Method 1: DIM CN AS New AdoDb.connection CN.Open "provider = SQLOLEDB; user ID = sa; password =;" & _ "initial catalog = myDatabase; data source = myserver "Method 2: DIM CN AS New Adodb.connection CN.connectionstring =" Provider = SQLOLDB; User ID = SA; Password =; "& _" Initial Catalog = MyDatabase; Data Source = MyServer "CN.Open method 3: DIM CN As new adodb.connection cn.provider = "sqloledb" cn.properties ("user ID"). Value = "sa" cn.properties ("initial catalog"). Value = "mydatabase" cn.properties ("Data Source" ) .Value = "Myserver" cn open (2) How to handle the event before, use ADO 2.0, we can process the Connection object or the incident of the Connection object or the RECORDSET object, so how to implement it in the program? The WitHevents keyword provided by Visual Basic language can easily establish the connection relationship of event source objects and event control functions. This is a brief description of this. First, in order to deal with the event caused by the event source, declare the object class variable with the WitHevents keyword, for example: Option Explicit Dim WitHevents CN AS Connection and give the event control function, according to the Visual Basic language, the function name of the event control function is "" Event source name _ event name ", for example, we have to respond to the ConnectComplete event of the Connection object CN, the event control function is called CN_CONNECTCOMPLETE, the code can be written: private sub cn_connectComplete (ByVal Perror As Adodb.Error, _ Adstatus as adoDB. EventStatusenum, Byval PConnection As Adodb.connection ... After the connection CN is complete, the ADO will activate the ConnectionComplete event, which automatically calls the CN_CONNECTCOMPLETE function. (3) Methods to perform queries Many ways to perform queries using ADO, the most basic method is to use the Command object, or directly call the EXECUTE method of the Connection object. The following describes how to use a Command object to perform a query. First create a data source to connect the Connection object, then create a Command object, DIM cmd as new adod.command cmd.commandtext = "select * from authortable" cmd.name = "mycommand" cmd.activeConnection = CN then calls the Command object Execute method SET RS = cmd.execute or method for the Connection object: SET RS = cn.mycommand We can also use the recordset object without the Command object, exemplify the use of the Recordset object, as follows: DIM RS AS New AdoDb.Recordset rs.cursortype = AdopenKeyset Rs.lockType = AdlockOptimistic Rs.Open "Authus", CN,,, AdcmdTableSet, even the Connection object can be used directly, directly by the Recordset, just give the necessary connection information in the Open statement. (4) Operation of record set data Various movements or modifications to record set data is the most important task of application access data. Like other data access interfaces, these operations are concentrated in recorded set objects and domain objects. And method calls, in the ADO object model, the usage of some methods of the Recordset and the Field object. Add recording operation: addNew method, call Update method, such as: rs.addnew r! Author_id = strid a! Authorname = strname ket! Title = strtitle rs.Update Delete record operation: Call the delete method to delete the current record or satisfaction A set of records. In immediately modify mode, delete it immediately; in batch modification mode, the DELETE operation is only tagged only in the record in the current buffer, and only after the UpdateBatch is called, the record is really deleted. A simple example is as follows: rs.filter = "title = 'engineer'" rs.delete adAffectGroup 'delete the record. Rs.Updatebatch modified record operation: After re-assaped the current recorded domain information, call Update methods, if To cancel the modification, call the CancelupDate method. For example: rs! Author_id = strid rs! Authorname = strname rs! Title = start rs.Update Mobile and Traverse Operation: Recordset provides mobile operations in various directions, using these operations to traverse the record set. Various mobile operations are shown in the following table: Operation Type Description MoveFirst method Move to record set MOVELAST method Move to record interaction MOVENEXT method Move to the next Record MovePrevious method Move to the previous record MOVE method forward or backward Record the Bookmark property Returns the current record bookmark or set to the specified bookmark position BOF attribute If the current location is before the first record, return TRUE Otherwise, if the current location is after the current location, return TRUE Otherwise, FALSE utilization These attributes and methods can be flexibly access record sets, which will be given: rs.movefirst do while True 'Display Information About Current Record and get User INPUT.SRMESSAGE = "Author:" & RS! Authorname & _ VBCR & " Record "& RS.AbsolutePosition & _" Of "& rs.Recordcount &") "& VBCR & VBCR & _" please Enter Command: "& VBCR & _" [1 - Next / 2 - Previous / "& VBCR & _ "3 - set bookmark / 4 - go to bookmark]" intCommand = Val (InputBox (strMessage)) Select Case intCommand 'Move forward or backward, trapping for BOF or EOF Case 1 rs.MoveNext If rs.EOF Then MsgBox "Moving. Past The Last Record. "rs.movelast endiff." RSTPublishers. Movefirst Endiff 'Store The Bookmark of The Current Record. Case 3 varBookmark = rs.Bookmark 'Go to the record indicated by the stored bookmark. Case 4 If IsEmpty (varBookmark) Then MsgBox "No Bookmark set!" Else rs.Bookmark = varBookmark End If Case Else Exit Do End Select Loop Six examples To this end, we have fully introduced Microsoft consistent data access technology, focusing on the ADO object model and its usage, and finally we will explain the basic usage of the ADO access to the database through a simple example. The routine is completed in the Visual Basic environment, first create a standard EXE project to browse the database using the default form of the project. We change the engineering name to ADOSample and change the form name to ADOSampleForm. The final operation of the program is shown in Figure 3. Figure 3 Routing Interface In order to use ADO in the project, first we use the "Reference" command under the "Project" menu, select "Microsoft ActiveX Data Objects Library," later we can use the ADO object directly in the program. Next, we use the "Components" command under the Project menu to select the "Microsoft FlexGrid Control" control, then add FlexGrid control in the form, we use the default control name MSFlexGrid1. To simplify the program, we use the Access database file NWIND.MDB with Visual Studio 6.0, add a connection string definition in the program, all global variables are defined as follows: Dim WitHevents CN As Adodb.Connection Dim Cmd As Adodb.command Dim RS AS when ADODB.Recordset Dim ConnectString as String when the program starts, the establishment of the connection object, as follows: Private Sub Form_Load () Set cn = New ADODB.Connection On Error GoTo EH Const ConnectString = "Provider = Microsoft.Jet.OLEDB.3.51;" & _ "Data Source = c: / Program Files / Microsoft Visual Studio / vb98 / Nwind.mdb" cn.ConnectionTimeout = 5 cn.CursorLocation = adUseClient cn.Open ConnectString, Options: = adAsyncConnect Exit Sub EH: Select Case Err Case 40002 : MsgBox "Could Not Connect ..." & Error Case Else Debug.print Err, Error End Select End Sub When the connection is complete, we execute a basic query command and call the configFlexGrid function to configure the display format of FlexGrid control, call showdatainflexGrid record function shows a result set, the following event is connected to the control function object: Private Sub Cn_ConnectComplete (ByVal pError As ADODB.Error, _ adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection) set cmd = New ADODB.Command cmd.CommandText = "Customers" cmd.commandtype = adcmdtable cmd.activeconnecti on = cn Set rs = cmd.Execute MSFlexGrid1.Visible = False ConfigFlexGrid ShowDataInFlexGrid MSFlexGrid1.Visible = True End Sub The following is the code ConfigFlexGrid and ShowDataInFlexGrid function: Private Sub ConfigFlexGrid () MSFlexGrid1.Left = 0 MSFlexGrid1.Top = 0 MSFlexGrid1.Rows = 1 MSFlexGrid1.Cols = 8 MSFlexGrid1.Row = 0 MSFlexGrid1.Col = 1 MSFlexGrid1.ColWidth (0) = 300 MSFlexGrid1.ColWidth (1) = 1500 MSFlexGrid1.ColWidth (2) = 2500 MSFlexGrid1.ColWidth (3) = 1500 MSFlexGrid1 .COLWIDTH (4) = 1500 msflexgrid1.colwidth (5) =
