The connection object provides the connection between the data source and the application. The connection object is used to create an open connection to your data source.
The main way to open a connection is with the Connection.Open method.
If you want to access a database multiple times you should establish a connection using the Connection object and hold this connection in a variable. So it can be re-used.
You can create an invisible connection by passing a connection string via the Command or Recordset object although this is not encouraged and should really only be used when executing a single query
Connection pooling preserves and reuses your connections for each queary. Connections can be reused as long as the connection strings are identical.
An asynchronous call returns control to the calling program before the operation is completed and allows execution of the code to continue
Connection Properties and Methods
|Attributes||Property||Sets or returns the attributes of the connection|
|BeginTrans||Method||Begins a new transaction (fires a begintranscomplete event)|
|Cancel||Method||Cancels a command executing on a connection if the connection is busy This is used to cancel the last pending, asynchronous call involving either the "Execute " or the "Open" methods. Can only be used if the Options parameter of the Open was "adSyncConnect" or the Options parameter of the Execute was "adAsyncExecute" or "adExecuteFetch"|
|Close||Method||Closes the connection (fires a ?? event)|
|CommitTrans||Method||Saves any changes and ends the current transaction (fires a committrans event)|
|CommandTimeOut||Property||Sets or returns the number of seconds to wait while attempting to execute a command before raising an error. The default is 15 seconds|
|CursorLocation||Property||Sets or returns the location of the cursor service|
|DefaultDatabase||Property||Sets or returns the default database for the Microsoft ODBC Provider|
|Execute||Method||Executes a query statement, procedure or provider specific text|
|Isolation Level||Property||Sets or retruns the isolation level. Allows you to open a transaction that is conducted in isolation of other transactions|
|Mode||Property||Sets or returns the provider access permission|
|Open||Method||Opens a connection (fires a connectcomplete event after connection starts)|
|OpenScema||Method||Returns a recordset of the name and the type of each table in the database|
|Provider||Property||Sets ot returns the provider name|
|RollBackTrans||Method||Cancels any changes in the current transaction and ends the transaction (fires a rollbacktranscomplete event)|
|State||Property||Sets ot returns the state of the connection|
|Version||Property||Returns the ADO version number|
Creating a Connection
Connection.Open [ConnectionString] [, UserID] [, Password] [, OpenOptions]
You can open a connection by using Connection.Open method.
dbADOConnection = new ADODB.Connection
dbADOConnection.Open ConnectionString, UserID, Password, OpenOptions
ConnectionString - contains the information needed to establish a connection to a data source. The string is composed of various parameter values, sperated by semicolons, and is database specific. For examples, please refer to the Connection Strings page.
OpenOptions - [Optional] specifies how the connection object should be returned, synchronously (the default) or asynchonously.
Using an Implicit Connection
If you want to quickly return a single recordset of data you do not have to explicitly create a connection.
You can use either the Command.Execute or RecordSet.Open when you do not want to reuse the database connection.
RecordSet.Open Source, ActiveConnection, CursorType, LockType, Options
where the activeconnection could be a literal string or a connection object representing an open connection
Connection.Execute [CommandText] [, RecordsAffected ] [, Options]
CommandText - can contain any executable string typically a SQL statement
RecordsAffected - is a return value used to indicate how many reocrds have been affected
Options - this should always be used to tell the provider how to interpret the CommandText type, ie the CommandType. This is critical to optimising the execution efficiency of your command
ADO supports the creation of cursors on both the client and server side.
The Cursor location indicates which engine manages the records in the recordset, either serverside or clientside
A cursor is a collection of pointers to a resultset returned by the datasource. Cursors can support features such as scrolling forward and backwards, to update the recordset and to view data changed by other users. The more features you ask for the more resources that will be required.
Server side cursors will make multiple trips to the data source to work with rows from the resultset. The number of trips is affected by the CacheSize property of the recordset object, which indicates how many rows the data provider will keep in its cache with each call to the data source. The default cachesize is 1.
When when client side cursors are employed, the entire resultset is sent to the client using one SQL statement with the rows being held in memory
Server side cursors are more efficient when an application is likely to address a small percentage of rows in the resultset. Fewer round trips to the data source will be required. You should note, however that applications should be designed to ask for only rows required by the client.
Unfortunately, not all types of cursors or locks are recognised by all providers. No error will occur if an unsupported value is used for the CursorType or LockType. The provider will just change it to one that it does support.
The location of the cursor can be specified by setting the CursorLocation property of either the Connection or Recordset object before creating the record set. You should always explicitely specify your cursor location. Server side cursors are the default in ADO.NET all data source support both methods.
If the "CursorLocation" property is set to use "adClient" the only valid setting for the "CursorType property is adOpenStatic
Cursors define the functionality of a specific recordset
The ADO cursor engine sits between ADO and ??
Some cursors allow you to see data in real time (data changes as they are being made when you are working with the recordset)
Some allow only a snapshot of the data at a particular point in time.
Some allow you to move up and down and others only in a forward direction.
The LockType property indicates the type of lock in effect on a record for editing
To use BatchUpdating (adLockBatchOptimistic) you should select either a keyset or static cursor.
|CursorLocation||adUseServer - manipulate the rows directly on the datasource|
|adUseClient - brings the rows down to the client machine and manipulates them there|
|CursorType||adOpenDynamic - Lets you see changes to rows by others and any added rows|
|adOpenStatic - A read only snapshot, you can't see any changes made by others. This is the only type of cursor allowed when you open a client side cursor.|
|adOpenForwardOnly (default) - A read only snapshot, lets you move through the record only once. Use the .movenext method to access successive rows|
|adOpenKeyset - Lets you see changes to rows made by others, but you will not see new rows added by others|
|LockType||adLockPessimistic - Locks rows as oon as you use the Edit method (which places the row in editable state)|
|adLockReadOnly (default) - No locks, cursor is read only|
|adLockBatchOptimistic - When used with server-side cursors it disables immediate updates on the server as the rows are modified. Used with the UpdateBatch and CancelBatch methods of the recordset|
|IsolationLevel||adXactBrowse / adXactReadUncommitted - Allows the application to read rows that have been modified but are not committed|
|adXactCursorStability / adXactReadCommitted - Allows the application to only read data that has been committed, but releases locks when the data is no longer being accessed|
|adXactIsolated / adXactSerializable - Allows the application to only read dat that has been committed, but holds locks for the duration of the transaction|
|adXactRepeatableRead - Does everthing a adXactIsolated / adXactSerializable does plus it allows new rows to be added to the resultsets if it is refreshed using the Requery method|
|adXactUnspecified - Indicates that ADO cannot determine what isolation level is being used|
|adXactChaos - Indicates that the transaction cannot overwrite pending changes from a higher isolation transaction. In SQL Server this is the same as adXactReadUncommitted|
|Mode||adModeRead - Permissions are Read - only|
|adModeUnknown - Permissions not set or determined|
|asModeWrite - Permissions are Write - only|
|adModeReadWrite - Permissions are Read and Write|
|adModeShareDenyRead - Prevents others from opening record source with Read permissions|
|adModeShareDenyWrite - Prevents others from opening record source with Write Permissions|
|adModeShareExclusive - Prevents others from opening the connection|
|adModeShareDenyNone (default) - Shared Access|
|States||adStateOpen - Connection is currently open|
|adStateConnecting - means that the object is in the process of making a connection|
|adStateExecuting - means that the object is in executing a command|
|adStateClosed - Connection is currently closed|
|Execute||adExecuteNoRecords - Tells the provider not the construct a recordset object. Always use this value in the Options argument whenever you execute a non-row returning query|
|source is a command or stored procedure that does not return rows. You can combine this constant with the adCmdText or adCmdStoredProc constants to speed up processing|
|adAsynFetch - Rows retrieved asynchronously|
|adAsyncFetchNonBlocking - records are fetched asynchronously without blocking the return|
|adAsyncConnect - Executes the SQL asynchronously|
|adAsyncExecute - Executes the SQL asynchronously|
|OpenOptions||adAsyncConnect - opens an asynchronous connection which returns before the connection is completed|
|adConnectUnspecified (default) - opens an synchronous connection which returns after the connection is completed|