.NET ODBC Connection Pooling

Question

I open a connection like this:

Using conn as New OdbcConnection(connectionString)
    conn.Open()
    //do stuff
End Using

If connection pooling is enabled, the connection is not physically closed but released to the pool and will get reused. If it is disabled, it will be physically closed.

Is there any way of knowing programmatically if connection pooling is enabled or not? and the number of used and unused connections currently open in the pool?

EDIT: I need to get this information from within the program, I can't go and check it manually on every single PC where the program will be deployed.

Answer 1

MSDN as in-depth guidelines on this

Configuring Connection Pooling from the Data Source Administrator

[snip]

Alternatively, you can start the ODBC Data Source Administrator at the Run prompt. On the taskbar, click Start, click Run, and then type Odbcad32.

The tab for managing connection pooling is found in the ODBC Data Source Administrator dialog box in version ODBC 3.5 and later. Configuring Connection Pooling from the Registry

For versions prior to version 3.5 of the ODBC core components, you need to modify the registry directly to control the connection pooling CPTimeout value.

Pooling is always handled by data server software. The whole point being is that in .NET you shouldn't have to worry about it (for example, this is why you should always use the SqlConnection when working with SQL Server - part of it is that it enables the connection pooling).

Update

On Vista, just type "ODBC" in the Start menu and it will find the app for you.

Update Following Clarification from OP

In terms of determining if connection pooling is enabled on each machine, looking at the MSDN guidelines I whould say you would best best if you check the registry values (See this article for pointers on registry access).

TBH though, unless the client machines are really crappy, I would possibly not even bother.. AFAIK it is enabled by default, and opening connections on a client machine (in my experience) has never been a big deal. It only really becomes a big deal when lots are being opened.

Answer 2

Looks like you can just read this registry key:

[HKEYLOCALMACHINE]\SOFTWARE\ODBC\ODBCINST.INI\SQL Server\CPTimeout

(or some variant thereof, depending on your OS and user account). If the value is 0, then connection pooling is disabled. If it's any value above 0, it's enabled.

See:

http://msdn.microsoft.com/en-us/library/ms810829.aspx

I'm not sure about getting the number of open connections. Just curious: why do you need to know the number?

Answer 3

So no one knows how to determine the number of open connections?

I have an intermittent problem - it happens maybe a couple times a month, where ODBC connections are not freed and if left unchecked, will take down the HP3000 (legacy iron) that is serving the data. The solution is to restart the services that are requesting the data. (Yes, I have checked all the data access code to make sure connections are being closed after use.)

I would like to program a monitor thread that will check open connections and if a certain threshold is reached, Support would be notified so they could take actions proactively instead of waiting for the HP3000 to die.

Answer 4

To determine the number of open connections on each db, try this sql - I got it from a document on internet

select db_name(dbid) , count(*) 'connections count' from master..sysprocesses where spid > 50 and spid @@spid group by db_name(dbid) order by count(*) desc

Spids <=50 are used by sqlserver. So the above sql would tell you the connection used by your programs.