ch11lev1sec4
Accessing Datasets
After connected to the database, you will want to access individual or joined datasets. This can be done with several of the ADO components .
Accessing a Dataset with TADOTable
To access a dataset with TADOTable , you need to
-
Set a connection so that the database can be accessed.
-
Set a table name so the component knows what table in the database you want.
-
Open the table.
After that, you will often need to connect the TADOTable through a TDataSource to some data-aware components for display or editing.
Setting the Connection for TADOTable
As mentioned, you have two options for establishing a database connection for any ADO object. The object can share a TADOConnection object, which it references by design time or runtime assignment to the Connection property; or it can have one created when it is opened, by providing a connection string. Again, this can be done at design time or runtime. Note, however, that any TADOCustomDataSet descendant must be inactive if you intend to change its Connection or ConnectionString . The same is true for the TADOConnection when you are going to change its ConnectionString .
Setting the Table Name for TADOTable
The next thing to do is establish which table will be opened. This is done using the TableName property. Once again, this can be done at either design time or runtime but, in either case, the TADOTable must be closed when a change is made.
Opening TADOTable
As with BDE tables, you can either set the Active property or call Open() to activate the table.
Using a Data-Source and Data-Aware Controls with TADOTable
TADOTable objects, like all TDataSet descendants, can be used with TDataSource objects and can be referenced from components that have a TDataSet type property. Note that this suggests that your components that may reference an ADO or BDE data set should not be of type TADOTable * or TTable * , but TDataSet * , if you want maximum compatibility.
Iterating Through the TADOTable
Like any TDataSet descendant, the TADOTable can use First() , Eof() , Next() , Prior() , and Last() for navigation through the table.
Adding or Editing Records Through TADOTable
Use Append() or Insert() to create a new record and Edit() to prepare to change field values. FieldByName , Fields->Field[Index] , and other properties and methods with the same names as their BDE component counterparts all work as they do with BDE components. See the online help for the specifics.
Locating Specific Records in TADOTable
The same methods that work for BDE tables work for TADOTable and the other TCustomADODataSet descendants. For instance, Locate and Lookup can be used to shift the database cursor to a record and to return values from a record without moving the cursor, respectively.
Using Filters with TADOTable
In general, filters can be used with the ADO components because they are used with the BDE components. Filters are client-side, so records from the underlying table must travel to the client for the filter to decide whether to pass or hide them. If you can't afford this, you will need to use a TADOQuery to include the filter condition in the selection on the server-side ( assuming you are using the ADO components to access a client/server database, that is). Of course, this is not an issue for desktop databases, such as dBase, Paradox, or Access.
There are some special considerations when using filters. First, although BDE component filters are tolerant of contact between operators, field names and literal values, the ADO components are not. They require a space on each side of an operator. Thus, SomeField = 'String' is fine, but SomeField='String' is not.
Secondly, the BDE component filters allow for partial comparison of string fields using * as a wildcard in the literal side of the expression. This also works with the equals sign, as in SomeField='String*' , which matches any value that starts with 'String' . The ADO components allow this same form, except that you must use LIKE in place of the equals sign. Note that you will receive no error (and no records) if you use the BDE style of a * with the equals sign.
Accessing a Dataset with TADOQuery
A TADOQuery is the same as a TADOTable (or, for that matter, is largely the same as a TQuery ) in the properties and methods it offers. But there are a few differences explained in the following list.
-
Of course, you must supply explicit SQL. Keep in mind that, unlike the BDE components, the ADO components do not offer a Local SQL to insulate you from differences between provider's SQL dialects, so you must make sure to comply with any special requirements of the driver or DBMS. It is wise to carefully conceal such differences in a central location so that you can easily shift between DBMSs in the future. Also, you cannot use SQL that references other databases or DBMS ”it is the BDE Local SQL that supports heterogeneous joins.
-
The SQL you supply can, as with TQuery , contain parameters. However, there is a small and annoying difference. Specifically, the parameters for a TADOQuery are supplied through a property called Parameters (not Params ), and the type of the elements supplied to Parameters is TParameter , not TParam . Finally, you cannot assign to AsString or AsFloat properties of a TParameter because they do not exist. There is only one accessor property for the value of a TParameter , and that is Value , which takes a Variant .
As with any query, insertions or changes will not be reflected in the query data set until you refresh the query by closing and opening it. There are some special properties you can set to allow for better performance during a refresh ”they will be discussed in the "Performance Optimizations" section, later in this chapter.
Running a Stored Procedure with TADOStoredProc
Stored procedures are a staple of client/server database programming. Though not supported by all DBMS (particularly desktop databases such as Access or dBase), these SQL scripts can be useful for performing repeatable functions on the server-side of a client/server connection, with the performance advantages that implies.
Setting Up TADOStoredProc
As with the other ADO components, you need to set up a connection, either through the Connection or ConnectionString property.
You need to set the stored procedure name through the ProcedureName property. Because procedure name restrictions vary, consult your database documentation. Note that the names of procedures in a database can be obtained through a TADOConnection member function call.
Finally, establish any necessary parameters using the TParameter objects in the TParameters class collection represented by the Parameters property. Note that parameters have a direction (input, output, and so on) as well as a type, but that values can only be set with Value (as with TADOQuery ).
Executing TADOStoredProc
Use ExecProc if the stored procedure is a command or set of commands, such as UPDATE or DELETE , that do not return results. If the stored procedure returns a result set, such as the results of a query, use Open or set the stored procedure to Active .
Getting Results from TADOStoredProc
A stored procedure can either return a result through the parameters or through a special parameter with direction pdReturnValue . The latter allows the return of a result set ”that is, the results of an SQL select . However, the pdReturnValue parameter is not examined; it is automatically linked to any TDataSource that references the TADOStoredProcedure through its DataSet property. This enables you to display the result set in a grid or to use any other data-aware controls.
Executing an Update with TADOCommand
The TADOCommand class is of limited additional utility and is provided primarily for compatibility with ADO. It can be used for any SQL that does not return a result, but because the TADOQuery can be used for such commands and for regular SQL, little advantage comes from using TADOCommand instead.
Setting Up TADOCommand
TADOCommand offers the same properties as the other ADO components, and they are set in the same fashion with the same types of values.
Executing TADOCommand
The Execute() method is used to perform the command. If the command is executed with ExecuteOptions of one of the eoAsync types, the command in progress can be interrupted with Cancel , as long as that call occurs before CommandTimeout passes . Note that the capability to cancel a long-running command is the only advantage offered by TADOCommand over TADOQuery . Though you can have an asynchronous TADOQuery and can receive periodic events while it executes, you cannot interrupt it.
Using TADOCommand for Dataset Access
A TADOCommand can be used to execute SQL that returns a result set. However, to do that, it must be associated with a TADODataSet . Executing the command, and then assigning the resulting Recordset property to a TADODataSet object's Recordset property, will accomplish that association. For example
ADODataSet1->Recordset = ADOCommand->Execute();
The resulting TADOCommand can be used with a TDataSource and any data-aware controls compatible with it.
Using TADODataset for Dataset Access
A TADODataSet is for use with SQL that produces a result set. Other than the capability to work with the asynchronous and abortable TADOCommand , it offers no compelling features. However, it is the component that must be used to work with the Microsoft RDS (Remote Data Space) multitier data access capability. This is accomplished by assigning a TRDSConnection object to the RDSConnection property. Of course, such a connection is mutually exclusive of the Connection property.
 |
| |
| Top |
