Programmers familiar with the BDE, dbExpress, or IBExpress should recognize the set of components that make up dbGo (Table 15.2).
The four dataset components (ADODataSet, ADOTable, ADOQuery, and ADOStoredProc) are implemented almost entirely by their immediate ancestor class, TCustomADODataSet. This component provides the majority of dataset functionality, and its descendants are mostly thin wrappers that expose different features of the same component. As such, the components have a lot in common. In general, however, ADOTable, ADOQuery, and ADOStoredProc are viewed as "compatibility" components and are used to aid the transition of knowledge and code from their BDE counterparts. Be warned, though: These compatibility components are similar to their counterparts but not identical. You will find differences in any application except the most trivial. ADODataSet is the component of choice partly because of its versatility but also because it is closer in appearance to the ADO Recordset interface upon which it is based. Throughout this chapter, I'll use all the dataset components to give you the experience of using each.
Enough theory: Let's see some action. Drop an ADOTable onto a form. To indicate the database to connect to, ADO uses connection strings. You can type in a connection string by hand if you know what you are doing. In general, you'll use the connection string editor (the property editor of the ConnectionString property), shown in Figure 15.1.
This editor adds little value to the process of entering a connection string, so you can click Build to go straight to Microsoft's connection string editor, shown in Figure 15.2.
This is a tool you need to understand. The first tab shows the OLE DB providers and service providers installed on your computer. The list will vary according to the version of MDAC and other software you have installed. In this example, select the Jet 4.0 OLE DB provider. Double-click Jet 4.0 OLE DB Provider, and you will be presented with the Connection tab. This page varies according to the provider you select; for Jet, it asks you for the name of the database and your login details. You can choose an Access MDB file installed by Borland with Delphi 7: the dbdemos.mdb file available in the shared data folder (by default, C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb). Click the Test Connection button to test the validity of your selections.
The Advanced tab handles access control to the database; here you specify exclusive or read-only access to the database. The All tab lists all the parameters in the connection string. The list is specific to the OLE DB provider you selected on the first page. (You should make a mental note of this page, because it contains many parameters that are the answers to many problems.) After closing the Microsoft connection string editor you'll see in the Borland ConnectionString property editor the value that will be returned to the ConnectionString property (here split on multiple lines for readability):
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb; Persist Security Info=False
Connection strings are just strings with many parameters delimited by semicolons. To add, edit, or delete any of these parameter values programmatically, you must write your own routines to find the parameter in the list and amend it appropriately. A simpler approach is to copy the string into a Delphi string list and use its name/value pairs capability: This technique will be demonstrated in the JetText example covered in the section "Text Files through Jet."
Now that you have set the connection string, you can select a table. Drop down the list of tables using the TableName property in the Object Inspector. Select the Customer table. Add a DataSource component and a DBGrid control and connect them all together; you are now using ADO in an actual—though trivial—program (available in the source code as FirstAdoExample). To see the data, set the Active property of the dataset to True or open the dataset in the FormCreate event (as in the example) to avoid design-time errors if the database is not available.
When you use an ADOTable component this way, it creates its own connection component behind the scenes. You do not have to accept the default connection it creates. In general, you should create your own connection using the ADOConnection component, which has the same purpose as the dbExpress SQLConnection component and the BDE Database component. It allows you to customize the login procedure, control transactions, execute action commands directly, and reduce the number of connections in an application.
Using an ADOConnection is easy. Place one on a form and set its ConnectionString property the same way you would for the ADOTable. Alternatively, you can double-click an ADOConnection component (or use a specific item of its Component Editor, in its shortcut menu) to invoke the connection string editor directly. With the ConnectionString set to the proper database, you can disable the login dialog box by setting LoginPrompt to False. To use the new connection in the previous example, set the Connection property of ADOTable1 to ADOConnection1. You will see ADOTable1's ConnectionString property reset because the Connection and ConnectionString properties are mutually exclusive. One of the benefits of using an ADOConnection is that the connection string is centralized instead of scattered throughout many components. Another, more important, benefit is that all the components that share the ADOConnection share a single connection to the database server. Without your own ADOConnection, each ADO dataset has a separate connection.
So, an ADOConnection allows you to centralize the definition of a connection string within a form or data module. However, even though this is a worthwhile step forward from scattering the same connection string throughout all ADO datasets, it still suffers from a fundamental flaw: If you use a database engine that defines the database in terms of a filename, then the path to the database file(s) is hard-coded in the EXE. This makes for a fragile application. To overcome this problem, ADO uses Data Link files.
A Data Link file is a connection string in an INI file. For example, Delphi's installation adds to the system the dbdemos.udl file, with the following text:
[oledb] ; Everything after this line is an OLE DB initstring Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb
Although you can give a Data Link file any extension, the recommended extension is .UDL. You can create a Data Link using any text editor, or you can right-click in Windows Explorer, select New, select Text Document, rename the file with a .UDL extension (assuming extensions are displayed in your configuration of Explorer), and then double-click the file to invoke the Microsoft connection string editor.
When you select a file in the Connection editor, the ConnectionString property will be set to 'FILE NAME =' followed by the actual filename, as demonstrated by the DataLinkFile example. You can place your Data Link files anywhere on the hard disk, but if you are looking for a common, shared location, then you can use the DataLinkDir function in the ADODB Delphi unit. If you haven't altered MDAC's defaults, DataLinkDir will return the following:
Imagine that you are responsible for designing a new database middleware architecture. You have to reconcile two opposing goals of a single API for all databases and access to database-specific features. You could take the approach of designing an interface that is the sum of all the features of every database ever created. Each class would have every property and method imaginable, but it would only use the properties and methods it had support for. It doesn't take much discussion to realize that this isn't a good solution. ADO has to solve these apparently mutually exclusive goals, and it does so using dynamic properties.
Almost all ADO interfaces and their corresponding dbGo components have a property called Properties that is a collection of database-specific properties. These properties can be accessed by their ordinal position, like this:
But they are more usually accessed by name:
Dynamic properties depend on the type of object and also on the OLE DB providers. To give you an idea of their importance, a typical ADO Connection or Recordset has approximately 100 dynamic properties. As you will see throughout this chapter, the answers to many ADO questions lie in dynamic properties.
In ADO, you can retrieve schema information using the ADOConnection component's OpenSchema method. This method accepts four parameters:
To use OpenSchema, you need an open ADOConnection. The following code (part of the OpenSchema example) retrieves a list of primary keys for every table into an ADODataSet:
Each field in a primary key has a single row in the result set. So, a table with a composite key of two fields has two rows. The two EmptyParam values indicate that these parameters are given empty values and are ignored. The result of this code is shown in Figure 15.3, after resizing the grid with some custom code.
When EmptyParam is passed as the second parameter, the result set includes all information of the requested type for the entire database. For many kinds of information, you will want to filter the result set. You can, of course, apply a traditional Delphi filter to the result set using the Filter and Filtered properties or the OnFilterRecord event. However, doing so applies the filter on the client side in this example. Using the second parameter, you can apply a more efficient filter at the source of the schema information. The filter is specified as an array of values. Each element of the array has a specific meaning relevant to the kind of data being returned. For example, the filter array for primary keys has three elements: the catalog (catalog is ANSI-speak for the database), the schema, and the table name. This example returns a list of primary keys for the Customer table:
var Filter: OLEVariant; begin Filter := VarArrayCreate([0, 2], varVariant); Filter := 'CUSTOMER'; ADOConnection1.OpenSchema( siPrimaryKeys, Filter, EmptyParam, ADODataSet1); end;
|Copyright © 2004-2021 "Delphi Sources" by BrokenByte Software. Delphi Programming Guide||