How To Write a COM+ Pooled Object That Pools an ADO Connection (266690)

SUMMARY

One of the more important uses of COM+ object pooling is the pooling of database connections. This is useful for several reasons. You can pool database connections to limit the number of connections that can be made to a database by setting a maximum object pool size. Pooling data connections can also provide a performance improvement over Microsoft Data Access Components (MDAC) connection pooling. Because ActiveX Data Objects (ADO) is the most commonly used data access method, pooling ADO connections is especially useful. This article describes some of the issues that are associated with pooling ADO connections.

The ObjectPoolingSample.exe sample demonstrates how to pool an ADO Connection object through a COM+ pooled object. The sample consists of two Microsoft Visual C++ Active Template Library (ATL) projects: one project contains a pooled component, the other project contains a client object for the pooled component. The Readme.txt file that comes with the sample provides more details about how the sample is structured.

The following file is available for download from the Microsoft Download Center:

ObjectPoolingSample.exe

Release Date: Aug-16-2001

For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:

119591 How to Obtain Microsoft Support Files from Online Services

Microsoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.

MORE INFORMATION

In general, if you encapsulate a database connection in a pooled object, you should perform the following actions in the object:

Open a database connection once, usually when the object is initialized.
Enlist the database connection in the current transaction, if one exists. Automatic transaction enlistment does not apply to connections that are made in pooled objects (as it does for connections that are made in regular components), so you must do this programmatically.
Use the database connection to do database work.
Unenlist the connection from the transaction when the object is deactivated. You must also do this programmatically.

Of course, if the database that you are using does not support COM+ transactions, or if you do not wish to have your pooled object participate in the current transaction, you will not perform transaction enlistment.

There are many options for implementing the above requirements. The following are some recommendations and issues that you should be aware of when you pool database connections through pooled components. The ObjectPoolingSample.exe sample demonstrates all of these items.

On the Transaction tab of the pooled COM+ component's Properties dialog box, make sure that the Transaction support setting is set to Supported so that the object can access the current transaction (if it exists).
Your pooled object should implement the IObjectConstruct interface. A good place to create the database connection is in the IObjectConstruct::Construct method, which is called once for each object when the object is first created. You can specify ADO connection string parameters in the construction string.
Your pooled object should implement the IObjectControl interface. You should perform transaction enlistment in the IObjectControl::Activate method and transaction unenlistment in the IObjectControl::Deactivate method.
You must obtain the ITransactionJoin interface from the ADO Connection object to programmatically enlist ADO connections in a COM+ transaction. ITransactionJoin is available through the ADOConnectionConstruction interface. Their use is demonstrated in ObjectPoolingSample.exe. The OLE DB Provider that you are using must support ITransactionJoin and the use of COM+ transactions. The Microsoft OLE DB Providers for SQL Server and Oracle do provide this support.
You cannot programmatically enlist any connections that use the OLE DB Provider for ODBC in a COM+ transaction. You must use a native provider for this.
Your ADO connection string must turn off the OLE DB pooling and automatic transaction enlistment services.
It is not recommended that you pass the ADO Connection object outside the pooled object to client components. This can cause unexpected and undesirable behavior for two reasons:
- First, client objects could set properties on the connection that would render it useless to other clients.
- Second, and more importantly, clients could easily hold on to connection objects while releasing the pooled object. As a result, the pooled object would inadvertently allow other clients make use of the connection, which causes multiple clients to use the same connection simultaneously. This could unexpectedly enlist the connection in various transactions, which could allow work to be done on the wrong transaction. It is safer to have the pooled object run any database queries on behalf of clients and pass any result sets back through disconnected recordsets. The best way to prevent this is to keep the ADO Connection object as a private member variable in your pooled component and not expose it through any public methods or properties.
However, it certainly can be useful to pass the connection to clients. If you do this, the client object must release the connection object before it releases the pooled object.
For the reasons just stated above, the pooled object should also not hand its connections out to client object indirectly through ADO recordset objects, ADO command objects, or any other means.