ADO Connection and Command Objects Explained

Posted on May 18, 2025 in Electronic Engineering

Connection object (ADO)

A Connection object represents an open connection to a data source.

Comments

A Connection object represents a unique session with a data source. In the case of a client/server database system, this may be equivalent to a current network connection to the server. Depending on the provider’s functionality, some collections, methods, or properties of a Connection object may not be available. Using the collections, methods, and properties of a Connection object, you can do the following:

  • Configure the connection before opening using the ConnectionString, ConnectionTimeout, and Mode properties.
  • Set the CursorLocation property to invoke the Client Cursor Provider, which supports batch updates.
  • Set the default database using the DefaultDatabase property.
  • Set the isolation level for open transactions using the IsolationLevel property.
  • Specify an OLE DB Provider using the Provider property.
  • Establish and terminate the physical connection to the data source with the Open and Close methods.
  • Execute a command using the Execute method and set the execution timeout with the CommandTimeout property.
  • Manage transactions in the open connection, including nested transactions if supported by the provider, using the BeginTrans, CommitTrans, and RollbackTrans methods and the Attributes property.
  • Examine errors returned by the data source using the Errors collection.
  • Read the version of the ADO implementation in use using the Version property.
  • Obtain database schema information using the OpenSchema method.

To execute a command, you typically use a Command object. Set the Command object’s ActiveConnection property to the desired Connection object. You can also execute simple commands directly using the Connection object’s Execute method. If the command returns rows, a Recordset object is returned. You can set Recordset properties to customize the resulting Recordset.

Command object (ADO)

A Command object is the definition of a specific command that you plan to run against a data source.

Comments

Use a Command object to query a database and retrieve records in a Recordset object, to perform bulk data management operations, or to manipulate the structure of a database. Depending on the provider’s functionality, some collections, methods, or properties of a Command object may not be available or may fail when referenced. Using the collections, methods, and properties of a Command object, you can do the following:

  • Define the executable command text (e.g., SQL statement) using the CommandText property.
  • Define parameterized queries or stored procedures with arguments using Parameter objects and the Parameters collection.
  • Execute a command and retrieve a Recordset object if applicable using the Execute method.
  • Specify the command type using the CommandType property before execution to optimize performance.
  • Check if the provider supports a prepared (or compiled) version of the command before execution using the Prepared property.
  • Set the number of seconds the provider will wait for the command to execute using the CommandTimeout property.
  • Associate an open connection with a Command object by setting its ActiveConnection property.
  • Set the Name property to identify the Command object, allowing it to be executed as a method of the associated Connection object.
  • Pass a Command object to the Source property of a Recordset object to retrieve data.

To execute a Command object, ensure its ActiveConnection property is set to the desired Connection object. You can then invoke the command using its Name property on the Connection object, passing parameter values as method arguments if applicable.