Transplantation from RDO to ADO
Chen Yonghao
The latest data access technology of Microsoft ActiveX Data Objects (ADO) is very popular. ADO is an alternative for previous DAOs, especially RDO data access interfaces, providing additional features that are not available in both.
This article mainly discusses how to transplant the data visits that are using RDO into the ADO so that everyone uses ADO to convert their own applications.
First, more than ADO and RDO, DAO comparison
ADO is not automatically compatible with your existing data access application code. When the functionality of ADO packages DAO and RDO, many language elements must be converted to ADO syntax. In some cases, you can make some functions of existing code to a simple conversion. In other cases, it is best to override the application with ADO's new features.
DAO (Data Access Objects) Data Access Object is the first object-oriented interface, which reveals the Microsoft Jet database engine (used by Microsoft Access) and allows Visual Basic developers to connect directly to other databases through ODBC icons. Connect to the Access table. DAO is most suitable for single-system applications or small range local distribution.
RDO (Remote Data Objects) Remote data object is an ODBC, object-oriented data access interface, which is easy to use the Dao Style combination, providing an interface, forming all ODBC's underlying function and flexibility . Although RDO is restricted in a well accessible Jet or ISAM database, and it can only access the relational database through an existing ODBC driver. However, RDO has proven to be the best interface that many SQL Server, Oracle, and other large-scale relational database developers are often selected. RDO provides more and more complex objects, attributes, and methods for accessing stored procedures and complex result sets.
ADO is the subsequent product of DAO / RDO. ADO 2.0 is more similar to RDO, and in general, there is a similar mapping relationship between these two models. ADO "Extended" Object Model used by DAO and RDO, which means that it contains fewer objects, more properties, methods (and parameters), and events. For example, ADO does not have an object equal to RDOEngine and RDoEnvironment object, which can reveal the ODBC driver manager and HENV interface. Although your interface may be implemented through the ODBC OLE DB service provider, you are currently not creating an ODBC data source from the ADO.
Many of the features included in the DAO and RDO models are merged into a single object, which generates a simple object model. However, for this reason, at first, you may feel very difficult to find the right ADO object, collection, attribute, method, or event. Unlike DAO and RDO, although the ADO object is a hierarchical structure, it can be created outside of the hierarchical structure.
However, it should also be noted that ADO does not currently support all functions of DAO. ADO mainly includes the functionality of RDO style, in order to interact with OLE DB, and also include remote and DHTML technology.
Generally speaking, during the evolution of ADO, Most of the DAO applications may be placed immediately on the ADO may be too early because the current ADO does not support data definition (DDL), users, groups, and more. However, if you only use the DAO for the customer-server application, it does not rely on the Jet database engine or does not use DDL, so it may now be ported to ADO. Finally, Microsoft will provide an ADO DDL component to help DAO to ADO porting and provide general DDL support for OLE DB vendors. Second, how to quote ADO 2.0 in Visual Basic
To access the ADO 2.0 object in Visual Basic, you can set access to the appropriate ADO type library. There are two kinds of ADO type libraries. A called AdoDB is included in MSADO15.DLL. It appears in "References ..." in the Projects menu in Microsoft ActiveX Data Objects 2.0 Library. Another called ADOR is included in Msador15.dll. It appears in the "reference ..." dialog box as "Microsoft ActiveX Data Objects RecordSet 2.0 Library".
In both types of libraries, the first type library (ADODB) is larger, with more features; it contains the main ADO objects, and may be used in most cases. The second is a "lightweight" subset of the ADODB type library that only supports records. If you just want to operate the recordset, you can choose to use this type of library.
Third, ADO 2.0 object model
The ADO 2.0 object model consists of eight objects, and most of them are similar to the RDO object, but they have stronger functionality. It takes some time to browse the object model in Object Browser (F2) to facilitate the location of various different attributes, methods, events, collections, etc.
Note that all objects with an asterisk (*) are objects that are applied simultaneously to the ADO Recordset Type Library (Ador).
• The Command object contains information about a command, such as query strings, parameter definitions, and the like. The Command object is similar to the RDOQuery object of RDO.
• The Connection object contains information about a data provider. The Connection object is similar to the RDOConnection object of the RDO and contains information about the structure. It also contains features of certain RDoEnvironment objects, such as Transaction controls.
· Error object contains extended information when the data provider is wrong. Error object is similar to the RDoError object of RDO.
· Field object * contains information about a single column of the recorded data. Field objects are similar to RDocolumn objects that are functional and RDO.
• Parameter object contains information about a single parameter of the parameterized Command object. This Command object has a parameter collection that contains all of its Parameter objects. Parameter objects are similar to RDOParameter objects that are functional and RDO.
• Property object * The feature of the provider definition of an ADO object. There is no RDO equivalent to this object, but DAO has a similar object. ADO objects can have two properties:
· Built-in property: ADO's "local" attribute. That is, any new objects using the familiar MyObject.Property syntax can be used immediately in the ADO properties. Built-in attributes do not appear in the form of a Property object in an object's Properties collection, so although you can modify their values, you cannot modify their features or delete them. · Dynamic Attributes: ADO's non-local attribute, which is defined by the next level of data provider. They appear in the Properties collection of the appropriate ADO object.
For example, a data provider unique attribute may indicate that a Recordset object is supported or supported. These additional properties appear in the form of a Property object in the Recordset's Properties collection. Dynamic properties can only be referenced by a collection with myObject.properties (0) or myObject.properties ("Name") syntax. Different data providers may provide one or more special properties to handle the provider's unique operation.
· Recordset object * Recordset object contains records returned by a query, as well as cursors in those records. The Recordset object is similar to the RDoResultset object of RDO. You can open a recordset without explicitly open the Connection object, for example, executes a query). However, if you choose to create a Connection object, you can open multiple Recordset objects on the same connection.
Fourth, the transplantation of general data access
Two basic data access schemes are listed below, and each solution is discussed in the next topic. In each program, an RDO solution and an ADO solution are introduced.
· Establish a connection to a database
· Run a basic query
Note that some examples reference additional controls on a form, such as a MSHFLEXGRID control or TextBox control. To make your own works work normally, add these controls to the project.
1. Create a connection to a database
1.> RDO
To open a connection, you must provide a connection string with parameters. Note that when RDO wants to create an RDOQuery object, it is not necessary to connect, but when you originally create a RDORESultset object, it is desirable:
DIM CN AS New Rdoconnection
DIM CNB AS New Rdoconnection
Const connectionString = "uid = myname; pwd = mypw; driver = {sqlserver}; _ _
Server = myserver; database = pubs; dsn = '' "
The connection string accesses a specific SQL Server and allows ODBC to open a connection without DSN. This is a typical ODBC connection string with all standard parameters.
The next code, in the form's LOAD event, the type of the cursor driver is established and the login timeout. By default, RDO uses the RDUSEIFNEDED cursor type, which calls a cursor on the server side on SQL Server. RDUsenone is specified in the example below to make this default regulations are not compliant. The RDDrivernoprompt flag means that if the user ID and password do not match, the application will generate an error.
Private sub flow_load ()
WITH CN
Cn.connect = connectstring
Cn.logintimeout = 10
Cn.cursordriver = rdusenone
CN.ESTABLISHCONNECTION RDDRIVERNOPROMPT
End with
Second connection Execute any customer batch update:
WITH CNB
CNB.Connect = ConnectString
CNB.CURSORDRIVER = RDuseClientBatch
CNB.ESTABLISHCONNECTION
End with
End Sub
The last event occurs when the connection operation is completed, and it can process any errors that appear when the connection is open. You can use this event to test whether the connection is working properly. If you can work properly, any button that is dependent on the open connection is valid.
PRIVATE SUB CN_CONNECT (BYVAL Erroroccurred as Boolean)
IF errooroccurred then
Msgbox "Could Not Open Connection", Vbcritical
Else
Runokframe.enabled = TRUE
END IF
End Sub
2.> ADO
To create a database connection in the ADO, first create a set of ADO objects that can be referenced by the AdoDB object. These objects will be used in the future to set the specific properties of the open connection and the generate result set:
DIM CN AS New Adodb.Connection
DIM RS As New Adodb.Recordset
DIM CNB AS New Adodb.Connection
DIM qy as new adodb.command
The next line creates a connection string, just as you created in the previous RDO example. In both cases, you use ODBC's "non-DSN" connection policies to save time and improve performance:
Const connectionString = "uid = myname; pwd = mypw; driver = {sql server}; _ _
Server = myserver; database = pubs; dsn = ",, connection = adConnectasync"
The following statement is initialized to the variables used in this example. (Note that a variable array of saved result sets is created:
DIM SQL AS STRING
DIM RC AS INTEGER
DIM I as integer
DIM Changes as in
DIM BMS () as Variant
Next, open an ADO connection to a database in a Form_Load event. Note that this code is very similar to the RDO code, but the constant starts with "AD" instead of "RD". If you want to see all the constants you can use, check the AdoDB Type Library.
Note You do not need to specify a prompt behavior because the ADO's default setting is "No Tips". However, if you choose to change it, you can use the ADO Properties collection to handle your prompt behavior. In RDO, you can use the OpenConnection parameter to set behavior. In ADO, you must set the Properties attribute.
At the same time, if you don't want to use a cursor driver, you don't have to specify it (like Cursordriver = RDUsenone in RDO), because the default regulation of ADO is no visible driver.
PRIVATE SUB FORM_LOAD () with CN
'Establish a non-DSN connection
.Connectionstring = connectstring
.ConnectionTIMEOUT = 10
'.Properties ("prompt") = Adpromptnever
'This is the default prompting mode in ado.
.Open
End with
WITH CNB
.Connectionstring = connectstring
.CURSORLOCATION = aduseclient
.Open
End with
End Sub
2. Run a basic query
1.> RDO
The event process returns a result set based on the SQL statement. It performs a restricted query and passes the result set to a control, which inserts the result data into a MSHFLEXGRID control. Note that the establishment of the result set requires an open connection.
Private sub runbutton_click ()
DIM RS As RdoreSultset
SET RS = cn.openResultset ("SELECT * from Titles Where Title _
Like '% h' ")
RDOGRID1.SHOWDATA RS
Rs.close
End Sub
2.> ADO
Once the database connection is turned on, you can run a query. The following event procedure is very similar to the previous RDO code. However, in this case, you use the new ADO Open method for SQL queries and the ADO Connection object as a parameter instead of using the OpenResultset method using the RDoconnection object. You can also choose the Execute method of the ADO Connection object, just like you can do in RDO (as long as it does not return a row collection).
Compared to ADO2 and RDO2, a major difference is that ADO2 allows you to create a recordset and set it for its properties before opening this recordset.
Private sub runbutton_click ()
DIM RS As New Adodb.Recordset
rs.open "SELECT * from Titles Where Title Like '% H'", CN
Adogrid1.Showdata RS
Rs.close
End Sub
You can run the query and process its result set in ADO. That is, you specify the adfetchasynch option on rs.open, ADO results in the result set in the background of the cursor driver.
If you have a problem, please send Eamil: c-yh@263.net
