VB memorandum (21) ADO Concept

ADO is an interface. Its main function is to let you forget complicated internal operations and use this interface to operate databases.

User <---> ADO <---------> oledb <----------> odbc <---------> various databases

As shown above, the user connects to oledb through ado, oledb connects with the driver ODBC, and finally automatically connects to various databases to complete communication with the database.

You only need to remember that ADO can deal with various databases.

When you operate ADO to obtain data from the database or access data, consider the following:

1. The database has a cursor (equivalent to a pointer pointing to a record). What type of cursor can this cursor be moved down? Move up or down? Is the cursor on the server or local?

2. How can I handle the same record when several users modify the database at the same time? Lock before modification? Can I modify it automatically regardless of other changes? Does the modification take effect at the permission level?

3. How to store and obtain images? How does one store and obtain binary data? How can I use a stream style )?

4. How do I operate a transaction? How does one take effect and roll back?

Typical database operations:

1. Connect to the database.

2. Obtain a dataset

3. process a dataset

4. Close the connection

Ado mainly has three objects: connection, command, and recordset. The model is as follows:

Connection object

The connection object indicates the connection that is opened and to the data source.
Note that when you need to make the command text persistent and re-execute, or use query parameters, you must use the command object.

1. Open the connection to the data source.
Connection. Open connectionstring, userid, password, options
Options: adconnectunspecified (default, synchronous), adasyncconnect (asynchronous)
If there is a user name and password, it will overwrite the user and password corresponding to the preceding parameters.
Close the connection with close, only release related resources, not delete objects (delete with set to nothing)

2. Execute the specified query, SQL statement, stored procedure, or text of a specific provider.
Connection. Execute commandtext, recordsaffected, options (no return value)
Set recordset = connection. Execute (commandtext, recordsaffected, options) (return row set)
With a response parameter recordsaffected (affected number of rows) Long
Options (optional) long integer value. Ado intelligently determines the type, but specifying the type will speed up.
Adshorttext indicates that the provider should calculate commandtext according to the command text definition.
Adcmdtable indicates that ADO should generate an SQL query so that all rows can be returned from the commandtext table.
Adcmdtabledirect instructs the provider to return all rows from the commandtext table.
Adcmdtable indicates that the provider should calculate commandtext Based on the table name.
Adcmdstoredproc indicates that the provider should calculate commandtext Based on the stored procedure.
Adcmdunknown indicates that the command type in commandtext is unknown.
Adasyncexecute indicates that the command should be executed asynchronously.
Adasyncfetch indicates the asynchronous extraction of the remaining rows after the initial quantity specified by the cachesize attribute.
Function restriction: The returned recordset object is always a read-only, forward-only cursor.

Command object

The command object defines the specified command that will be executed on the data source. Returns the record in the recordset object.

Use the commandtext attribute to define executable texts of commands (such as SQL statements.
You can use the parameter object and the parameters set to define parameters for parameterized queries or stored procedures.
Execute the command and return the recordset object when appropriate.
Use the commandtype attribute to specify the command type before execution to optimize performance.
Use the prepared attribute to determine whether the provider saves (or compiles) The command version before execution.
Use the commandtimeout attribute to set the number of seconds for the provider to wait for command execution.
Associate the opened connection with the command object by setting the activeconnection attribute.
Set the name attribute to identify the command as the method associated with the connection object.

If multiple command objects are associated with the same connection, you must explicitly create and open the connection object so that the connection object can be assigned to the object variable.
If the activeconnection attribute of the command object is not set to the object variable, even if the same connection string is used,
Ado will also create a new connection object for each command object.

If two or more command objects are executed on the same connection and a command object is stored with output parameters, an error occurs. To execute each command object, use an independent connection or disconnect all other command objects.

Run the query, SQL statement, or stored procedure specified in the commandtext attribute.
For the command returned by line:
Set recordset = command. Execute (recordsaffected, parameters, options)
For commands not returned by rows:
Command. Execute recordsaffected, parameters, options
Returns the recordset object reference or nothing.

Recordsaffected is optional. Long variable, the number of records affected by the operation returned by the provider. Therefore, this value cannot be returned for queries (the recordcount attribute is used for queries ).
Optional. The variant array of parameter values passed using SQL statements. (When this parameter is used, the output parameter cannot return the correct value .)
Options is optional. The long value indicates how the provider calculates the commandtext attribute of the command object. This value can be either commandtypeenum or

The bitmask value generated by the executeoptionenum value. For example, if you want ADO to calculate the commandtext attribute value as text,

If the command text is discarded and no generated text is returned, use adshorttext with adexecutenorecords.
Commandtypeenum specifies the method for interpreting command parameters. Functions of option with connection
Adcmdunspecified-1 does not specify the command type parameter.
Adcmdtext 1 calculates commandtext based on the definition of the text called by the command or stored procedure.
Adcmdtable 2 calculates commandtext by table name. All columns in this table are returned by an internal SQL query.
Adcmdstoredproc 4 calculates commandtext Based on the stored procedure name.
Default Value of adcmdunknown 8. Indicates that the command type in the commandtext attribute is unknown.
Ad1_file 256 calculates commandtext Based on the file name of the persistent storage recordset.
It is only used with recordset. Open or requery.
Adcmdtabledirect 512 calculates commandtext by table name. All columns in the table are returned. It is only used with recordset. Open or requery.
To use the seek method, you must use adcmdtabledirect to open recordset.
This value cannot be combined with the value adasyncexecute of executeoptionenum.

Executeoptionenum specifies how the provider executes commands.
Adasyncexecute 0x10 indicates that the command will be executed asynchronously. This value cannot be combined with the commandtypeenum value adcmdtabledirect.
Adasyncfetch 0x20 indicates that the remaining rows after the specified initial quantity in the cachesize attribute are retrieved asynchronously.
Adasyncfetchnonblocking 0x40 indicates that the main thread will never be blocked during retrieval. If the requested row is not retrieved, the current row will automatically move to the end of the file.
If you open recordset from a stream that contains persistent storage recordset, then adasyncfetchnonblocking
Will not work; this operation will be synchronized and blocked.
When the adcmdtabledirect option is used to open recordset, adasynchfetchnonblocking does not work.
 
Adexecutenorecords 0x80 indicates that the command text does not return line commands or stored procedures (for example, commands that only insert data ).
If any rows are retrieved, they are discarded and no response is returned.
Adexecutenorecords can only be passed to the command or connection execute method as an optional parameter.
 
Adexecutestream 0x400 indicates that the result of command execution should be returned as a stream.
Adexecutestream can only be passed to the execute method of command as an optional parameter.
Adexecuterecord indicates that commandtext is a command or stored procedure that returns a single row that should be returned as a record object.
Adoptionunspecified-1 indicates that no command is specified.

The parameters of the execute method of the command object always use the most recent values unless they are overwritten by the parameter values passed by the execute call.
The new values of some parameters can be omitted when the execute method is called to overwrite the parameter subset. The order of the specified parameters is the same as that of the parameters passed by this method. For example,
If there are four (or more) parameters, you only want to pass the new values of the first and fourth parameters, you can set array (var1, var4) it is passed as a parameter of parameters.

Recordset object

The recordset object represents the complete set of records from basic tables or command execution results.
At any time, the current record referred to by the recordset object is a single record in the set.

Four different cursor types are defined in ADO:
 
Dynamic Cursor-used to view the addition, modification, and deletion of other users, and to move various types of recordset that do not depend on bookmarks.
You can use bookmarks if the Provider supports them.
Key set cursor-its behavior is similar to dynamic cursor. The difference is that you cannot view records added by other users and access records deleted by other users,
Data changes made by other users are still visible. It always supports bookmarks, so various types of movement in recordset are allowed.
Static cursor-provides a static copy of the record set to search for data or generate reports. It always supports bookmarks, so various types of recordset are allowed
Type. The addition, modification, or deletion made by other users will not be visible. This is when you open the client (Ador) recordset object
Unique allowed cursor type.
Forward cursor only-in addition to allowing only forward scrolling in the record, the behavior is similar to static cursor. In this way, when you need to move one way in recordset
High performance can be improved.

Set the cursortype attribute to select the cursor type before opening the recordset, or use the open method to pass the cursortype parameter. If no cursor type is specified, ADO will open only forward cursor by default.
If the recordset is enabled after the cursorlocation attribute is set to aduseclient, The underlyingvalue attribute of the Field object is unavailable in the returned recordset object.
Like multiple commands using the same connection, multiple Rs are opened on the same connection. If the connection is not displayed, ADO still creates a connection object, but it does not assign this object to the object variable, ADO will also create a new connection object for each new recordset
When recordset is enabled, the current record is in the first record (if any), and the BOF and EOF attributes are set to false. If no record exists, the BOF and EOF attributes are set to true.
Assuming that the Provider supports related functions, you can use the movefirst, movelast, movenext, and moveprevious methods as well as the move method, and the absoluteposition, absolutepage, and filter attributes to re-determine the location of the current record.
Only the forward recordset object supports only the movenext method. When you use the move method to access each record (or enumerate recordset), you can use the BOF and EOF attributes to check whether the movement has exceeded the start or end of the recordset.
The recordset object supports two types of updates: immediate update and batch update. Use immediate update. Once the update method is called, all changes to the data will be immediately written to the basic data source. You can also use addnew and update Methods to pass the array of values as parameters and update several fields of the record.
If the provider supports batch update, the provider can store changes to multiple records in the cache, and then use the updatebatch method to send them to the database in a single call. This situation applies to changes made using the addnew, update, and delete methods. After calling the updatebatch method, you can use the status attribute to check and resolve any data conflicts.
Note that you should pass the query string to the open method of the recordset object to execute a query that does not use the command object. However, to keep the command text and execute it repeatedly or use query parameters, you still need the command object. Is the previous "persistent"

Open Method (ADO recordset) opens the cursor.
Recordset. Open source, activeconnection, cursortype, locktype, options
Source is optional. Variant: Calculate valid command objects, SQL statements, table names, stored procedure calls, URLs
Or a file name or stream object that permanently stores the recordset.
Activeconnection is optional. Variant: Calculate the variable name of a valid connection object or the string containing the connectionstring parameter.
Cursortype is optional. Cursortypeenum value to determine the type of cursor that the provider should use when recordset is enabled. The default value is adopenforwardonly.
Adopendynamic 2 uses a dynamic cursor. Other user's ADD, change, or delete operations can be seen, and the recordset
All movement types (except bookmarks if the provider does not support bookmarks ).
Adopenforwardonly 0 default value. Use forward-only cursor. Except that the record can only scroll forward, it is the same as the static cursor. If you only need
When performing a transfer in A recordset, it can improve the performance.
Adopenkeyset 1 uses the keyset cursor. Although your recordset cannot access records deleted by other users
It is similar to a dynamic cursor. Data changes made by other users are still visible.
Adopenstatic 3 uses a static cursor. Static copies of a group of records, which can be used to find data or generate reports. Add, change, or
The deletion is invisible.
Adopenunspecified-1 does not specify the cursor type.

Locktype is optional. Locktypeenum value to determine the lock (concurrency) type that the provider should use when the recordset is enabled. The default value is adlockreadonly.
Adlockbatchoptimistic 4 indicates an open batch update. Batch update mode is required.
Adlockoptimistic 3 indicates to record open locks one by one. The provider uses open locks to lock records only when the update method is called.
Adlockpessimistic 2 indicates logging locking one by one. The provider must ensure that the record is successfully edited. Generally, the record is locked immediately after the record is edited.
Adlockreadonly 1 indicates a read-only record. Data cannot be changed.
Adlockunspecified-1 does not specify the lock type. When creating a copy, the copy uses the same lock type as the source object.

Options is optional. Long value indicates how the provider calculates the source parameter (if this parameter represents some content other than the command object ),
Or instruct the recordset to be restored from the previously saved files. It can be one or more commandtypeenum or executeoptionenum values,
These values can be combined with the bitwise AND operator.

Description
The default cursor of ADO recordset is a forward-only read-only cursor located on the server.

Open the cursor on the recordset object. The cursor indicates the record, query result, or previously saved recordset in the basic table.

Use the optional Source parameter to specify the data source, which uses one of the following: command object variable, SQL statement, stored procedure, table name, URL, or complete file path name. If source is the file path name, it can be the complete path ("C: \ dir \ file. RST), relative path ("... \ file. RST) or URL ("http: // files/file. RST ").

The activeconnection parameter corresponds to the activeconnection attribute and specifies the connection to open the recordset object. If the connection definition of this parameter is passed, ADO will use the specified parameter to open a new connection. After you use the client cursor (cursorlocation = aduseclient) to open the recordset, you can change the value of this attribute to send updates to other providers. Alternatively, you can set this property to nothing (in Microsoft Visual Basic) or null to disconnect recordset from any provider. However,
Activeconnection will generate an error.

Before setting the activeconnection attribute, you can call open without operands to create A recordset instance. These recordsets are created by appending fields to the fields set of recordset.

Stream object indicates the stream of binary data or text.

In a tree-like hierarchical structure such as a file system or email system, a record may have a default binary stream associated with its bit, which contains the content of a file or email. Stream objects can be used to operate fields or records that contain these data streams. You can obtain stream objects in the following ways:

Instantiate a stream object. These stream objects can be used to store data for applications. Different from the default stream of the stream or record associated with the URL, the instantiated stream is not associated with the basic source by default.
You can use the methods and attributes of stream objects to perform the following operations:

Use the Open Method to open stream objects from record or URL.
Close stream.
Use the write and writetext methods to input bytes or text to stream.
Use the read and readtext methods to read bytes from the stream.
Use the flush method to write any stream data in the ADO buffer to the basic object.
Copy the stream content to another stream using the copyto method.
The skipline method and lineseparator attribute are used to control the way to read rows from the source file.
Determine the end of the stream position using the EOS attribute and seteos method.
Use the savetofile and loadfromfile methods to save and restore data in the file.
Use the charset attribute to specify the character set used to store stream.
Use the Cancel Method to terminate the asynchronous stream operation.
Use the size attribute to determine the number of bytes in the stream.
Use the position attribute to control the current position in the stream.
Use the type attribute to determine the Data Type in stream.
Use the state attribute to determine the current status of the stream (enabled, disabled, or running ).
Use the mode attribute to specify the stream access mode.
--------------------
Charset
Indicates the character set used to convert text stream content to store it in the internal buffer of the stream object.

Set and return values
Sets or returns a string value, which specifies the character set used to convert string content. The default value is Unicode ". The allowed value is a typical string that is passed to an interface as an Internet Character Set string (for example, "iso-8859-1", "Windows-1252", and so on. For a list of character set strings supported by the system, see hkey_classes_root \ mime \ database \ charset subkeys in the Windows registry.

For an open stream, its current position must be at the beginning of stream (0) to set charset.
Charset can only be used with a text stream object (type: adtypetext. If type is adtypebinary, this attribute is ignored.

----------------------
EOS attributes
Indicates whether the current position is at the end of the stream.

Return Value
Returns a Boolean value indicating whether the current position is at the end of the stream. If there are no other bytes in the stream, Eos returns true; if there are other bytes after the current position, false is returned.

To set the end position of a stream, use the seteos method. To determine the current position, use the position attribute.

------------------------------
The seteos method sets the end position of the stream.
Syntax stream. seteos
It indicates that seteos updates the value of the EOS attribute by making the current position the end of the stream. All bytes or characters after the current position will be truncated.

Since write, writetext, and copyto do not intercept excess values in the existing stream object, you can use seteos to set a new end position of the stream to intercept these bytes or characters.

Warning if you set Eos to the position before the actual end of the stream, all data after the new EOS position will be lost.

---------------------------
Lineseparator attributes
Indicates the binary character to be used as a branch character in a text stream object.

Set and return values
Sets or returns the lineseparatorsenum value, indicating the line break used in stream. The default value is adcrlf.

Description
Lineseparator is used to interpret the content of a text stream. You can use the skipline method to skip a row.

Lineseparator can only be used with a text stream object (type: adtypetext. If type is adtypebinary, this attribute is ignored.

---------------------
Savetofile Method
Saves the binary content of stream to a file.

Syntax
Stream. savetofile filename, saveoptions

-----------------------
Loadfromfile Method
Load the content of an existing file to stream.

Syntax
Stream. loadfromfile filename
Parameters
Filename
String value, including the name of the file to be loaded into stream. Filename can contain valid paths and names in any UNC format. If the specified file does not exist, a runtime error occurs.
Description
This method can be used to load the content of a local file to a stream object. It can also be used to upload the content of local files to the server.

Before calling loadfromfile, the stream object must be open. This method does not change the binding of stream objects; it will still be bound to the object specified by the original URL or record that opened the stream.

Loadfromfile overwrites the current content of the stream object with the data read from the file. Any existing byte in stream is overwritten by the content of the file. Any original and remaining bytes followed by the EOS created by loadfromfile will be truncated.

After loadfromfile is called, the current position is set at the beginning of stream (position is 0 ).

To facilitate encoding, two bytes may be added at the beginning of the stream, so the size of the stream may be different from the size of the file to which it is loaded.

---------------------
Open Method (ADO Stream)
Open the stream object to operate the stream of binary or text data.

Syntax
Stream. Open source, mode, openoptions, username, password
Parameters
Source
Optional. Variant value, which specifies the stream data source. Source may contain an absolute URL string pointing to an existing node in a well-known tree structure (such as email or file system. Use the URL keyword ("url = scheme: // server/folder") to specify the URL. In addition, source can contain references to opened record objects, which open the default stream associated with the record. If no source is specified, stream is instantiated and opened. By default, stream is not associated with the basic source. For more information about the URL mode and its associated providers, see absolute and relative
URL.
Mode
Optional. Connectmodeenum value to specify the stream access mode (for example, read/write or read-only ). The default value is admodeunknown. For more information about access mode, see mode attributes. If mode is not specified, it will be inherited by the source object. For example, if open-source record is opened in read-only mode, stream will also be opened in read-only mode by default.
Openoptions
Optional. Streamopenoptionsenum value. The default value is adopenstreamunspecified.
Username
Optional. String value, including the user ID that accesses the stream object (if needed.
Password
Optional. String Value, containing the password used to access the stream object (if needed.
Description
When the record object is passed as the source parameter, the userid and Password parameters are not used because the record object can be accessed. Similarly, the mode of the record object is also passed to the stream object. If source is not specified, the opened Stream does not contain data and its size is zero (0 ). When stream is disabled, to avoid losing any data written into the stream, use the copyto or savetofile method to save the stream or save it to another memory location.

The openoptions value of adopenstreamfromrecord identifies the content of the Source parameter, which is the record object to be opened. By default, source is treated as a URL that directly points to a node (such as a file) in the tree structure. The default stream associated with the node will be opened.

When stream is not enabled, all read-only attributes of stream may be read. If stream is enabled asynchronously, all subsequent operations (except checking State and Other Read-Only attributes) will be blocked until the open operation is complete.

In addition to the options discussed above, stream objects in the memory can be instantiated without specifying the source, instead of associating it with the basic source. You can use write or writetext to write binary or text data to a stream, or use loadfromfile to load data from a file to dynamically add data to a stream.

Dim cn as new ADODB. connectiondim RS as new ADODB. recordsetdim STM as new ADODB. streamprivate sub form_load () CN. open "provider = sqloledb.1; Password = 123; persist Security info = true; user id = sa; initial catalog = book; Data Source = Zheng" set mshflexgrid1.datasource = cn. execute ("select * from book salesman") CN. closeend subprivate sub mshflexgrid?gotfocus () mshflexgrid=selchangeend subprivate sub mshflexgrid#selchange () CN. open Rs. open "select * From where employee ID = '" & mshflexgrid1.textmatrix (mshflexgrid1.row, 1) & "'", CN, adopenkeyset if not Rs. EOF then if isnull (RS. fields (3) Then image1.picture = loadpicture ("") else STM. open STM. type = adtypebinary STM. write Rs. fields (3) 'is written into stream. Note that stream is the carrier STM. savetofile app. path & "\ ly.gif", adsavecreateoverwrite image1.picture = loadpicture (App. path & "\ ly.gif") STM. close end if RS. close CN. closeend sub