Session pooling can provide high performance in an
environment that exhibits frequent connects and disconnects from a backend
database. This article describes how to get session pooling in a Visual Basic
program that uses ActiveX Data Objects (ADO) component to talk with the backend
database.
The following Visual Basic program exhibits how you can do
session pooling. The trick to doing this is to make sure that one reference to
a connection object is retained at global scope in the Visual Basic
application. This keeps a reference to the IDataInitialize interface, which is
the OLE DB Service Components where session pooling occurs.
Sample Code
Note You must change uid=<username> and pwd=<strong
password> to the appropriate username and password before you run this code.
Make sure that uid has the appropriate permissions to perform the required
operations on the specified database.
'Global connect handle declaration
Dim conn1 As New ADODB.Connection
Private Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim i As Integer
'Do not uncomment the following. Let time to be default, that is, never
'expire conn1.CommandTimeout = 60 is not enough to keep connection alive.
conn1.Open "dsn=pubs;uid=<username>;pwd=<strong password>;"
For i = 0 To 10
conn.Open "dsn=pubs;uid=<username>;pwd=<strong password>;"
rs.Open "select * from authors", conn
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
Next
conn1.Close
Set conn1 = Nothing
End Sub
The preceding ADO program uses the default "MSDASQL" (Microsoft OLEDB
Provider for ODBC drivers). To get the required session pooling with this
provider you have to change or add the following registry entry:
HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}
Look for the value "OLEDB_SERVICES." This is a hex value, and
should be set to 0xffffffff to enable ADO session pooling.
If you
want to use "SQLOLEDB", the native OLEDB Provider for SQL Server, you have to
change/add the following registry entry:
HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}
Look for the value "OLEDB_SERVICES." Again, this is a hex value
and should be set to 0xffffffff to enable ADO session pooling.
Specifying "OLE DB Services=-1;" on the connection string will also use session
pooling. If this is set on the connection string it overrides what is set in
the registry.
For additional information, visit the following Microsoft
Developer Network (MSDN) Web site: