ActiveX Data Objects consists of a generic-style data-access structure that allows you to access any data source, regardless of its structure, with the same programming interface. The individual objects within the ADO object model are used to provide all of the datastorage, manipulation, and retrieval commands needed when writing a data-based application. ADO includes the following objects and collections:
- The Connection object
- The Command object
- The Parameters collection and the Parameter object
- The Recordset object
- The Fields collection and the Field object
- The Record and Stream objects
- The Properties collection and the Property object
- The Errors collection and the Error object
- In the next sections, I take a closer look at these objects and collections.
The Connection object is the gateway for all data communications through ActiveX Data Objects. Figure 2-1 illustrates the Connection object's object model.
Figure 2-1. The Connection object's object model
In order to access data from any source, a connection for that source must first be established. ADO uses the Connection object to accomplish this. The Connection object uses information that you provide to establish a unique connection to a particular OLE DB data source. The standard information that a Connection object accepts includes filenames, data-provider names, usernames, and passwords. If your particular data provider needs additional information, this information can be passed from the Connection object directly to your data provider. By allowing this form of pass-through of connection specifications, ADO does not make any assumptions or restrict itself to one type of data source. All of the functionality of the chosen data provider is made available through the use of the Connection object.
A Connection object is used to accomplish the following tasks:
- Select a data source and data provider
- Open and close a connection on a selected data source
- Manage transactions on a data source
- Execute queries on a data source
Connection objects can be created explicitly and used later with the Command and Recordset objects, or the Connection object can be created by the Command and Recordset objects implicitly, behind the scenes.
In addition, the Connection object reports errors through an Errors collection and provides ADO version information.
The Command Object:-
The Command object is used to execute instructions -- whether for storing, manipulating, or gathering information -- on a specific data source. Figure 2-2 shows the Command object's object model.
Figure 2-2. The Command object's object model
Once you're connected to a data source, you naturally want to perform some operation on it. One of your options is to use the Command object, which executes commands against the associated data source. There are five types of commands that a Command object can execute:
Probably the most popular type of command, a SQL statement can gather information, manipulate information, or manipulate the structure of the underlying database.
A parameterised query uses variables that set or return values that are part of a particular query or SQL statement.
A stored procedure is a query that resides within the connected data source. By identifying the name of a stored procedure, you can execute, through the data provider, a query that is defined outside of ADO. Stored procedures can also use parameters.
An open table-type statement does not query data, but instead returns all of the fields in all of the records belonging to the specified table. This is comparable to the DAO OpenTable method.
A string command enables the data provider to perform a specific operation that is defined by the provider itself and outside of ADO. Such a command is commonly used, for example, when a particular data provider offers its own version of the SQL language. In such a situation, ADO has no idea how to process a proprietary SQL string for this language, so you tell ADO to forward it directly to the data provider. The data provider, in turn, can take this string and process a result that can be sent back through ADO to your application. The OLE DB provider for Internet Publishing, for instance, allows the passing of a URL statement to identify a data source, within the Command object.
If the Command object is used to retrieve data, then a Recordset object containing the requested records is created and passed back to the application.
The Command object can be associated with a currently open connection, or it can be created independently of any existing Connection objects, in which case the Command object creates its own Connection object but does not share it with you.
The Parameters collection and the Parameter object:-
The Parameters collection belongs to the Command object. This collection stores Parameter objects that are used to make parameterized queries or to invoke stored procedures. Every Command object has a Parameters collection created by ADO. You can populate the Parameters collection, or it can be refreshed to retrieve the already defined parameters -- for the Command -- from the data source.
The Parameters collection and the Parameter object's object model is displayed in Figure 2-3. This collection and object combination defines the characteristics of parameters when referring to a parameterized query or defines the input and output arguments when referring to a stored procedure.
Figure 2-3. The Parameters collection and the Parameter object's object model
With the Parameter object, you can set or read the name, value, and characteristics of a given parameter. If you know this information beforehand for any stored procedure or parameterized query, you can potentially save valuable time by creating Parameter objects yourself that ADO would otherwise spend trying to learn this information.
The Recordset Object:-
A Recordset object is used to access data on a record level. Figure 2-4 illustrates the Recordset object model.
Figure 2-4. The Recordset object model
A Recordset object can be created by the developer to return data itself, or it can be returned from executing a command with a Connection or Command object. This information can be obtained from a table in the underlying data source or from a previous SQL statement, query, or stored procedure executed through the Command object.
The Recordset object consists of a Fields collection of individual Field objects, each with its own properties, characteristics, and values. (The Recordset object may be familiar to you if you have worked with DAO before.)
The Recordset object works well with all types of data because it relies on the ability of all data to be broken into structured records composed of one or more fields. It is easy to see this structure in a database, but what about a data source such as a directory? In this case, each file in the directory may be a record. Each field of this record might be a different attribute of that file, including its name, its size, its creation date, its last modification date, its contents, etc. It is important to realize that all stored data can have a structure that represents records with fields that are located within tables, just as in a more obviously structured database.
With the Recordset object, we can move a virtual record pointer around a list of records, searching for records, placing bookmarks, and editing specific values of designated fields. We can also add and remove records from the recordset. We can view and edit the properties of the fields that make up these records.
Recordset objects, like Command objects, can be created using an existing Connection, or Recordset objects can implicitly create their own Connection object, which is not automatically passed back to your application, unless you request it. Recordsets show you one record at a time. With this view, you can manipulate data any way that you would like through a Fields collection or Field object, which are discussed next. Multiple Recordset objects can access the same data, and, as a matter of fact.
There are four types of cursors available in ADO. A cursor is a way of working within a result set or records. Each provides a different view of the same data, and each has its pros and cons. Not all providers support every type of cursor. The four types of cursors are:
Forward-only cursor:-
The forward-only cursor is exactly the same as the static cursor except that you can only move forward through the records in your recordset. Unless you specify otherwise, this is the default view of a recordset, and it offers the best performance of all four recordset types.
Dynamic cursor:-
This view of your data source allows you to see dynamically any additions, changes, or deletions made by other users of the data source. The dynamic cursor is the most resource-intensive type of recordset.
Keyset cursor:-
This view of your data source only allows you to see modifications made to the data in your recordset by other users. It does not show you records that have been added by other users, and it denies you access to records that have been deleted. The keyset cursor offers slightly better performance than the dynamic cursor.
Static cursor:-
The static cursor offers you a snapshot of your data through the Recordset object. The static cursor does not show you any additions, modifications, or deletions of the records in your recordset, regardless of what other users are doing to it. It is generally used for data gathering and reporting in a multi-user environment. The static cursor offers abundant speed advantages over both the keyset and dynamic cursor.
The Recordset object offers two types of data updating: immediate update mode and batch update mode. In the immediate update mode, changes are made one record at a time. Once you have indicated that you have finished updating a record, the information is immediately transferred to the underlying data source and written. On the other hand, the batch update mode allows the data provider to cache several records in memory to be sent to the data source in a single call, where it is then written as a batch.
The Fields Collection And The Field Object:-
The Fields collection belongs to the Recordset object and the Record object. The Fields collection is a group of Field objects that represent individual columns in a recordset. Figure 2-5 shows the Fields collection and Field object's object model. Every Recordset object and every Record object has a Fields collection that is created by ADO.
Figure 2-5. The Fields collection and the Field object's object model
The Field object offers the developer complete access to the underlying data of a chosen recordset. The Field object makes available its field's name, value, data size, and attributes. With this information, we can read, alter, and verify field information within the current record of our recordset.
The Record Object:-
The Record object is one of the newest additions to the ADO object model added with Version 2.5. It can represent either a single record within a Recordset object, or it can represent a resource within a hierarchical data source. A Record object can be obtained from a Recorset object (representing a single record of the complete recordset), or it can be created as a standalone object to represent a resource such as a file or a directory. Figure 2-6 shows the Record object's object model.
Figure 2-6. The Record object's object model
One of the unique features of the Record object is that it can be used to navigate hierarchical data sources such as a file directory. By using the OLE DB provider for Internet Publishing, the Record object allows the developer to access resources within a web server (files and directories).
The Record object allows for file and directory manipulation, such as copying, moving, and deleting resources. In addition, the Record object can be used to access the actual data belonging to one of these resources through the exposure of a default Stream object.
The Stream Object:-
The Stream object was added at the same time as when the Record object was added to ADO with Version 2.5. The Stream object is used to view and manipulate text and binary data belonging to a resource such as a file or a buffer in memory. A Stream object can be obtained from a Record object or it can be created as a standalone object. Figure 2-7 shows the Stream object's object model.
Figure 2-7. The Stream object's object model
An additional feature of the Stream object is its ability to be created independently of a specified data source. In other words, the Stream object can be created in memory and need not be tied to any predefined data. In this way, the Stream object can be used as a utility object such as a buffer. Added functionality allows the Stream's buffer to be persisted (saved to the datasource) to local files in any directory.
The Properties Collection And The Property Object:-
The Connection, Command, and Recordset objects each have their own Properties collection. The Properties collection consists of individual Property objects that hold specific information about their associated objects. These collections are supplied automatically by ADO. Figure 2-8 illustrates the Properties collection and Property object's object model.
Figure 2-8. The Properties collection and the Property object's object model
In order to fine-tune all of these objects -- the Connection, Command, Recordset, and Field objects -- ADO offers the Properties collection. This collection contains individual Property objects that allow dynamic characteristics of the data source belonging to the current data provider to be accessed within each object. The Property objects may inform you of special features that are unique to the data source and are not standard ADO functionality. The Property objects may also tell you what standard ADO functions are supported by the current data provider so that you can avoid problems when attempting particular commands. With this ability, we can determine at runtime the capabilities of the data source that we are trying to access. This allows our software to realize the full potential of data-source drivers.
One of the more flexible features of ADO is that it can offer the developer data providerdefined functions that are not part of the standard ADO specification. For instance, the Microsoft Cursor Service for OLE DB offers dynamic properties that are used to specify how often calculated and aggregate columns are calculated within a data-shaping query. Instead of working with only the lowest common denominator in data-access techniques, ADO allows your application to check for and take advantage of functions that are specific to a particular data provider. Each data provider uses the Property objects of the Properties collection differently, but they all use it to expose their special functionality. Consult the documentation of the data provider you are using in your application for more information on how to utilize the Properties collection and the Property object.
The Errors Collection And The Error Object:-
The Errors collection belongs to the Connection object but services all of ADO. The Errors collection is populated with Error objects whenever an error occurs within a single ADO data-access operation. Figure 2-9 shows the Errors collection and the Error object's object model.
Figure 2-9. The Errors collection and the Error object's object model
The Errors collection contains errors and warnings that are generated both by ADO and by the individual data provider being used. These messages allow us to scan and trap errors that arise when we access data sources. If ADO detects the error, then ADO will throw the error. But if the error is provider-specific, the data provider passes it back to ADO, which will report the error to you. What is nice about ADO's error capabilities is that they can tell you where an error was generated and which object produced the error.
An Error object is added to the Errors collection whenever an error occurs within ADO. The Errors collection is cleared right before a method that can generate an error is called. An Error object provides a description of the error, an error number, the name of the object that generated the error, the capability to access Windows help files based on the particular error, and error information from SQL data sources. An error object can also contain a warning that does not halt the execution of your application.
No comments:
Post a Comment