Connection

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

NameTypeDescription
AttributesPropertySets or returns the attributes of the connection
BeginTransMethodBegins a new transaction (fires a begintranscomplete event)
CancelMethodCancels 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"
CloseMethodCloses the connection (fires a ?? event)
CommitTransMethodSaves any changes and ends the current transaction (fires a committrans event)
CommandTimeOutPropertySets or returns the number of seconds to wait while attempting to execute a command before raising an error. The default is 15 seconds
CursorLocationPropertySets or returns the location of the cursor service
DefaultDatabasePropertySets or returns the default database for the Microsoft ODBC Provider
ExecuteMethodExecutes a query statement, procedure or provider specific text
Isolation LevelPropertySets or retruns the isolation level. Allows you to open a transaction that is conducted in isolation of other transactions
ModePropertySets or returns the provider access permission
OpenMethodOpens a connection (fires a connectcomplete event after connection starts)
OpenScemaMethodReturns a recordset of the name and the type of each table in the database
ProviderPropertySets ot returns the provider name
RollBackTransMethodCancels any changes in the current transaction and ends the transaction (fires a rollbacktranscomplete event)
StatePropertySets ot returns the state of the connection
VersionPropertyReturns 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.
UserID -
Password -
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.


Command.Execute


RecordSet.Open Source, ActiveConnection, CursorType, LockType, Options
where the activeconnection could be a literal string or a connection object representing an open connection
Source -
ActiveConnection -
CursorType -
LockType -
Options -


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


Cursor Location

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


Cursor Type

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.


Locking

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.


CursorLocationadUseServer - manipulate the rows directly on the datasource
 adUseClient - brings the rows down to the client machine and manipulates them there
CursorTypeadOpenDynamic - 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.
 adOpenUnspecified
 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
LockTypeadLockPessimistic - Locks rows as oon as you use the Edit method (which places the row in editable state)
 adLockOptimistic
 adLockReadOnly (default) - No locks, cursor is read only
 adLockUnspecified
 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
IsolationLeveladXactBrowse / 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
 adAsyncExecute
ModeadModeRead - 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
StatesadStateOpen - 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
ExecuteadExecuteNoRecords - 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
OpenOptionsadAsyncConnect - opens an asynchronous connection which returns before the connection is completed
 adConnectUnspecified (default) - opens an synchronous connection which returns after the connection is completed



© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext