Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.0 » SQL Anywhere Server - Programming » OLE DB and ADO development


OLE DB Connection Pooling

You should note that the .NET Framework Data Provider for OLE DB automatically pools connections using OLE DB session pooling. When the application closes the connection, it is not actually closed. Instead, the connection is held for a period of time. When your application re-opens a connection, ADO/OLE DB recognizes that the application is using an identical connection string and reuses the open connection. For example, if the application does an Open/Execute/Close 100 times, there is only 1 actual open and 1 actual close. The final close occurs after about 1 minute of idle time.

If a connection is terminated by external means (such as a forced disconnect using an administrative tool such as Sybase Central), ADO/OLE DB does not know that this has occurred until the next interaction with the server. Caution should be exercised before resorting to forcible disconnects.

For more on ADO connection pooling, refer to [external link] SQL Server Connection Pooling (ADO.NET). Also see [external link] Overriding Provider Service Defaults and [external link] OLE DB, ODBC, and Oracle Connection Pooling (ADO.NET).

The flag that controls connection pooling is DBPROPVAL_OS_RESOURCEPOOLING (1). This flag can be turned off using a connection parameter in the connection string.

If you specify OLE DB Services=-2 in your connection string, then connection pooling is disabled. Here is a sample connect string:

Provider=SAOLEDB;OLE DB Services=-2;...

If you specify OLE DB Services=-4 in your connection string, then connection pooling and transaction enlistment are disabled. Here is a sample connect string:

Provider=SAOLEDB;OLE DB Services=-4;...

Note that if you disable connection pooling, there will be a performance penalty if your application frequently opens/closes connections using the same connect string.