ado| Beginners | access | data ADO (activexdataobjects) is a new type of data access interface that Microsoft provides and recommends for use, specifically implemented as automation. In this way, programmers can easily access ADO objects, such as VisualBasic, Visualc++, Visualj++ and Delphi, in various support automation development environments. ADO is implemented as a thin layer above OLE DB, which makes it faster to access, easier to use, and more resource-saving. It is noteworthy that for VisualStudio6.0 users, the ADO2.0 now provided is more complete than the ADO1.5 of the VisualStudio5.0 era. This means that programmers can use the ADO interface more widely and even use it in all Windows platform-based data access. For example, the support of RDS (formerly known as ADC) makes it easy to build efficient Web applications.
first, the main operation of the ADO object
The main operation of the ADO object is basically the same as that of DAO and RDO Library. Mainly includes 6 aspects:
1. Connect to the data source. This is an optional connection object that usually involves ADO.
2. Submit commands to the data source. The Command object for ADO is usually involved. Can be used in conjunction with parameter objects (Parameter) in queries.
3. Execute commands, such as a select script.
4. If the submitted command has a result return, the result can be manipulated through the ADO Recordset object and the data is stored in the cache.
5. If appropriate, the modified data in the cache can be updated to physical storage.
6. Provide error detection. The Error object of ADO is usually involved.
From a programmer's point of view, the object names of ADO, DAO, and RDO are not very similar. But using ADO objects is much simpler than DAO and RDO. The main point is that programmers do not have to create child objects in a step-by-step way from the top level of the object model, as they do with DAO and RDO. As a result, ADO provides a more flexible way of programming.
the function of the main object in ADO
Connection object that represents a session to the data source. With the members of the Connection object, you can use the corresponding properties to open the connection to the data source, set the cursor location, set the default current database, set the oledbprovider that will be used, submit the SQL script directly, and so on. It is worth noting that the query can be completed without creating a Command object when submitting the SQL Script task. In addition, the creation of the connection object is independent of other objects. Command object, which can be used to query the database and return the results to the Recordset object. You can also batch operation and manipulate the structure of the database, of course, this requires the use of Oledbprovider to provide appropriate support. In addition, an activated connection object can be bound to the ActiveConnection property of the Command object, which allows multiple command object instances to share a connection object.
A Recordset object that encapsulates the results of a query, which can be called a result set.
A Field object that is used to express the type and value of each paragraph in a row of results.
An Error object that detects and determines errors that occur in a database operation, such as a failure to connect. In ADO, many object names have an extra "s", such as Error->errors,field->fields and so on. Adding "s" means a collection (set) object for the corresponding object, such as Errors is the collection object of the Error object. Collection is a bit like an array, but the difference is that collection can have different types of data or objects as their own elements, and the elements in the array are usually the same type. So, when you see an object name and finally "s", it usually indicates that it is a collection object, such as the elements in errors are made up of instances of the Error object.
three, simple examples
Now, after we have passed the groundwork, we can finally get into the process of writing code. Here is a series of snippets of code written in VisualBasic, in which the manipulation of ADO is fully implemented in code. Before you begin, be aware of the activexautomation reader that we are using an ADO component that is implemented as activexautomation. 1. Open the Connect.ion object
Open a connection to the data source, that is, the VB code for the Connection object is as follows:
Dim cn as ADODB. Connection
' Declare adodb.connection object variable
Dim STRCN as String
' declares a string variable that holds the connection string
Set cn= New Connection
' Instantiate the Connection object
' Generate a connection string (ConnectionString)
Strcn= "provider=microsoft.jet.oledb.3.51;
Userid=admin; " &_
"Datasource=d:\microsoftvisual
Studio\vb98\nwind.mdb; "
cn. Open STRCN ' calls the Connection object's method open connection data source
The preceding code uses the pre-binding (Early-bind) to access the ADO object, which requires the reference menu item to be selected from VisualBasic's Project menu. and choose Microsoftactivexdataobjects2.0library. In the STRCN variable declared in the program, the connection string attribute provider identifies the Oledbprovider as Oledbproviderformicrosoftjet, because we are accessing the MicrosoftAccess data file, Use Microsoftjetengine to achieve better performance than ODBC. There are two places to note when testing the above code. First, to select the appropriate version based on the Oledbproviderformicrosoftjet service installed by the system, it may be 3.51 or 4.0. In this case, the version 3.51 is used. If you use version 4.0, you first need to change the provider property to "Microsoft.Jet.OLEDB.4.0", and secondly, the DataSource property identifies the path to the data file you want to access, making appropriate adjustments based on your installation. File Nwind.mdb are usually installed by default in Visual Studio, and are typically placed in the working directory of the workbook.
In addition to the OLE DB Provider for Microsoftjet, Visual Studio6.0 also provides the following oledbprovider, as shown below.
| oledb provider type |
data Source Type |
| databases |
| microsoft OLE DB Provider for Microsoft Index server |
microsoft (R) Index Server |
| microsoft OLE DB Provider for Microsoft Active Directory Service |
microsoft (R) Ac tive Directory Service |
| microsoft OLE DB Provider for SQL Server Mic Rosoft (r) SQL Server |
microsoft OLE DB Provider for Oracle |
TD style= "BORDER:0;PADDING:0;" >oracle
| databases |
This means that using the ADO interface makes it easy to access the various data sources above, and to do so is to select the appropriate OLE DB Provider. In addition to using the open method of the Connection object, you can quickly create a connection to the data source through the Recordset object's Open method. This fully embodies the flexibility of ADO.
2. Create Command object
Dim cmd as Adodb.command
Set cmd=new Command ' instantiates the command object
3. Execute Query
Take the command object for example.
Dim rs as New ADODB. Recordset
Set cmd. ACTIVECONNECTION=CN ' Bind-activated Connection object instance
cmd.commandtext= "Select*from Customers" generates SQL script
Set Rs=cmd. Execute ' Execute query
The above code is just a way of querying, in addition, ADO's Connection object's Execute method and Recordset object's Open method also provides the query ability. The returned results can be saved in a Recordset object instance for subsequent data processing and manipulation. For example:
Dim rs As NewADODB.Recordset
rs.Open cmd,cn,adOpenDymanic,adLockBatchOptimisticIn addition, in the way SQL scripts are generated, they can usually be done with the help of Parameters/parameter objects.
4. Display and manipulate data
The query results are encapsulated by the Recordset object. Manipulation of data can be done through the members (properties and methods) provided by the Recordset object.
Rs. MoveFirst
Do but not Rs. EOF ' Judge eof Tag attribute (endoffile)
Debug.Print rs! CustomerID & VbTab & rs! CompanyName & _
VbTab & rs! ContactName & VbTab & rs! ContactTitle
Rs. MoveNext ' Move the cursor pointer to the next record
Loop
The preceding code prints the record of each row in the recordset in Visual Basic's Immediate window. To see the results of the printing, you can add a stop statement after the above code to enter the debug state.
5. Update records
Use the Recordset object to complete the update operation.
Rs. Close ' Closes the result set previously established
' Open a new result set with Write permission
Rs. Open "Customers", cn,adopendynamic,adlockoptimistic,adcmdtable
Rs. MoveFirst ' though not necessary, but may be a good habit of
Rs! Companyname= "Microsoft" assigns a new value to the corresponding field
Rs. Update ' takes effect on physical storage
In addition to update, write operations include AddNew (adding a new record) and delete (deleting a new record). In addition, the recordset supports batching, but these features need to be supported by low-level oledbprovider. When an error occurs in a write operation, the reason can be judged by an error description (Access to ADO's error or VisualBasic's Err object). Another thing to note is that when you use a Recordset object to complete a write operation, you need to specify that the Recordset object instance is not read-only.
6. Finishing work
You should explicitly release the appropriate resources at this stage and, if not, usually VisualBasic automatically releases and reclaims resources. But for a programmer with good programming habits, you should take the initiative to do the finishing work, just like the following code.
Rs. Close
Set rs=nothing
Set cmd=nothing
cn. Close
Set cn=nothing
