ADOConnection

1.TADOConnection

 

Unit

ADODB

Description

TADOConnection encapsulates the ADO connection object. Use TADOConnection for connecting to ADO data stores. The connection provided by a single TADOConnection component can be shared by multiple ADO command and dataset components through their Connection properties.

TADOConnection allows you to control the attributes and conditions of a connection to a data store. Use the properties of TADOConnection to control such attributes as record locking scheme (optimistic versus pessimistic), cursor type, cursor location, isolation level, and connection time-out. Methods are also provided for implementing transactions and retrieving metadata about the database to which this component connects.

 

 

To connect your application with the data store , use an ADO connection component (TADOConnection ). Configure the ADO connection component  to use one of the available ADO providers. Although TADOConnection is not strictly required, because ADO command and dataset components can establish connections directly using their ConnectionString property, you can use TADOConnection to share a single connection among several ADO components. This can reduce resource consumption, and allows you to create transactions that span multiple datasets.

Like other database connection components , TADOConnection provides support for

1.Controlling connections

  1.1 Connecting to a database server

       There are two ways to connect to a database server using a connection component:

          Call the Open method.
          Set the Connected property to True.

       Calling the Open method sets Connected to True.

  Note

   When a connection component is not connected to a server and an application attempts to open one of its associated datasets, the dataset automatically calls the connection component's Open method.

When you set Connected to True, the connection component first generates a BeforeConnect event, where you can perform any initialization. For example, you can use this event to alter connection parameters.

After the BeforeConnect event, the connection component may display a default login dialog, depending on how you choose to control server login. It then passes the user name and password to the driver, opening a connection.

Once the connection is open, the connection component generates an AfterConnect event, where you can perform any tasks that require an open connection.

   Note

   Some connection components generate additional events as well when establishing a connection.

Once a connection is established, it is maintained as long as there is at least one active dataset using it. When there are no more active datasets, the connection component drops the connection. Some connection components surface a KeepConnection property that allows the connection to remain open even if all the datasets that use it are closed. If KeepConnection is True, the connection is maintained. For connections to remote database servers, or for applications that frequently open and close datasets, setting KeepConnection to True reduces network traffic and speeds up the application. If KeepConnection is False, the connection is dropped when there are no active datasets using the database. If a dataset that uses the database is later opened, the connection must be reestablished and initialized.

  

     1.2 Disconnecting from a database server

     There are two ways to disconnect a server using a connection component:

         Set the Connected property to False.
        Call the Close method.

      Calling Close sets Connected to False.

      When Connected is set to False, the connection component generates a BeforeDisconnect event, where you can perform any cleanup before the connection closes. For example, you can use this event to cache information about all open datasets before they are closed.

After the BeforeConnect event, the connection component closes all open datasets and disconnects from the server.

Finally, the connection component generates an AfterDisconnect event, where you can respond to the change in connection status, such as enabling a Connect button in your user interface.

   Note

Calling Close or setting Connected to False disconnects from a database server even if the connection component has a KeepConnection property that is True.

 

2. Controlling server login
 

3. Managing transactions

 3.1 Starting a transaction

When you start a transaction, all subsequent statements that read from or write to the database occur in the context of that transaction, until the transaction is explicitly terminated or (in the case of overlapping transactions) until another transaction is started. Each statement is considered part of a group. Changes must be successfully committed to the database, or every change made in the group must be undone.

While the transaction is in process, your view of the data in database tables is determined by your transaction isolation level.

For TADOConnection, start a transaction by calling the BeginTrans method:

Level := ADOConnection1.BeginTrans;

 

 3.2 Ending a transaction

Ideally, a transaction should only last as long as necessary. The longer a transaction is active, the more simultaneous users that access the database, and the more concurrent, simultaneous transactions that start and end during the lifetime of your transaction, the greater the likelihood that your transaction will conflict with another when you attempt to commit any changes.

When the actions that make up the transaction have all succeeded, you can make the database changes permanent by committing the transaction. For TDatabase, you commit a transaction using the Commit  method:

MyOracleConnection.Commit;

For TSQLConnection, you also use the Commit  method, but you must specify which transaction you are committing by supplying the transaction descriptor you gave to the StartTransaction method:

MyOracleConnection.Commit(TD);

For TIBDatabase, you commit a transaction object using its Commit method:

IBDatabase1.DefaultTransaction.Commit;

For TADOConnection, you commit a transaction using the CommitTrans method:

ADOConnection1.CommitTrans;

 

4. Working with associated datasets

   Closing all datasets without disconnecting from the server

5. Sending commands to the server

   All database connection components except TIBDatabase let you execute SQL statements on the associated server by calling the Execute method. Although Execute can return a cursor when the statement is a SELECT statement, this use is not recommended. The preferred method for executing statements that return data is to use a dataset.

    The Execute method is very convenient for executing simple SQL statements that do not return any records. Such statements include Data Definition Language (DDL) statements, which operate on or create a database's metadata, such as CREATE INDEX, ALTER TABLE, and DROP DOMAIN. Some Data Manipulation Language (DML) SQL statements also do not return a result set. The DML statements that perform an action on data but do not return a result set are: INSERT, DELETE, and UPDATE.
 

6 Obtaining metadata

 

In addition to these features that are common to all database connection components, TADOConnection provides its own support for

A wide range of options you can use to fine-tune the connection .
 The ability to list the command objects that use the connection .
 Additional events when performing common tasks.