The Connection Object - Properties of the Connection Object

(Page 7 of 15 )

 

The Attributes Property

This property indicates the transactional facilities of a Connection object.

Long = Connection.Attributes
Connection.Attributes = Long

Its value can be one or more of the adXactAttributeEnum constants:

• adXactCommitRetaining, to ensure that a new transaction is started automatically after a CommitTrans

• adXactAbortRetaining, to ensure that a new transaction is started automatically after a RollbackTrans

• A combination of both, to indicate that a new transaction is started automatically after an existing transaction is finished

Note that not all providers support this property.

In Visual Basic you can combine two or more values by ORing them together:

objConn.Attributes = adXactCommitRetaining _
                 OR adXactAbortRetaining

Beware of automatic transaction enlistment when using nested transactions, because this can lead to problems when committing the higher-level transactions. For more details on this see the Nested Transactions subsection of the discussion of the BeginTrans method, earlier in this chapter.

The CommandTimeout Property

This property indicates how long, in seconds, to wait while executing a command before terminating the command and generating an error. The default is 30.

Long = Connection.CommandTimeout
Connection.CommandTimeout = Long

If the timeout period is reached before the command completes execution, an error is generated and the command cancelled. Setting this property to zero will force the provider to wait indefinitely.

For example, the following code will ensure that an error is generated if the command doesn’t complete within 10 seconds:

objConn.CommandTimeout = 10

Note that the Command object’s CommandTimeout property does not inherit the value set here. So the CommandTimeout property of a Connection object applies only to statements executed through the use of the Execute method.

The ConnectionString Property

This property contains the details used to create a connection to a data source.

String = Connection.ConnectionString
Connection.ConnectionString = String

ADO supports only the following five arguments in the connection string (all other arguments are ignored by ADO and passed directly to the provider):

• Provider= identifies the name of the provider

• File Name= identifies the name of the provider-specific file containing connection information (for example, a UDL file)

• Remote Provider= is the name of a provider that should be used when opening a client-side connection (this applies only to RDS)
  
• Remote Server= is the path name of the server that should be used when opening a client-side connection (this applies only to RDS)

• URL= is the absolute URL identifying a resource, such as a file or directory

The provider can change the connection string while the connection is being established, because it fills in some of its own details.

You cannot pass both the Provider and File Name arguments. Specifying a File Name will cause ADO to load the specified Data Link (.udl) file, which contains all of the necessary connection information. For more information about Data Link files, see Chapter 2.

Under version 2.8, reading of the ConnectionString property has the following actions:

1. If the connection is open, the connection string is obtained from the underlying OLE DB provider.

2. The dynamic property "Persist Security Info" (OLE DB property DBPROP_AUTH_PERSIST_ SENSITIVE_AUTHINFO) is checked. If true, then the password information is returned along with the connection string; otherwise, the password information is omitted.

Some examples of various connection strings are shown here. For the ODBC provider connecting to Microsoft Access:

Driver={Microsoft Access Driver (*.mdb)}; DBQ= database_name

For the ODBC provider connecting to Microsoft SQL Server:

Driver={SQL Server}; Server=server_name; _ 
          Database=database_name;
          UID=user_name; PWD=password

You can also use an existing DSN. To use a DSN called pubs, you would specify this connection string:

DSN=pubs; UID=sa; PWD=

The pros and cons of using a DSN versus a full ODBC connect string are discussed in Chapter 14.

When using the OLE DB provider for ODBC, notice that you can omit the Provider option (because it is the default). For the OLE DB provider connecting to Microsoft Access:

Provider=Microsoft.Jet.OLEDB.4.0; Data Source=database_name

For the OLE DB provider connecting to Microsoft SQL Server:

Provider=SQLOLEDB; Data Source=server_name;
     Initial Catalog=database_name; User Id=user_name;
     Password=user_password

For example, to connect to a SQL Server using the OLE DB Provider, you would do something like this:

objConn.ConnectionString = "Provider=SQLOLEDB; " & _ 
                        "Data Source=TIGGER;" & _ 
                        "Initial Catalog=pubs; " & _
                        "User Id=davids; Password=letmein"
objConn.Open

If, when you open a connection, you pass the connection details into the ConnectionString argument, then the ConnectionString property will be filled in with these details.

For more information on connection strings, refer to Chapter 2. 

This is from ADO Programmer's Reference, by Dave Sussman (Apress, ISBN 1590593421). Check it out at your favorite bookstore today. Buy this book now.

Next: The ConnectionTimeout Property and More >>
 

More ASP.NET Articles
More By Apress Publishing

Comments On: The Connection Object

>>> Be the FIRST to comment on this article!