link between program and data
opening a connection is expensive
hence connection pooling: ADO does not destroy the connection object even after you close it
the connection is kept in a pool
it is destroyed after a time out interval (60 seconds => disconnect in SQL Trace)
reusable connection: which matches the connection details(data store, user name, password)
to turn connection pooling off in OLE DB: append to ConnectionString 'OLE DB SERVICES = -2':
significant differences: 6 seconds for 100 000 connections to ....?
not using this leaves the connection in SQL logged in at the time of the initial logon even after it is closed and reopened in code
the connection disappears from Activity Monitor when the program exits
however, if a connection is closed and the program is still running, after a while it disappears from the Activity Monitor (after the time out)
if the connection is not closed, it stays open in the Activity Monitor
multiple connections are opened if a Connection.Open is issued even if they have the same authentication and data store
setting the connection to null/Nothing clears it from the pool (? does not seem to affect the Activity Monitor)
if the connection is set to nothing without closing it, it shows in Activity Monitor
not closing the connection causes it not to time out even when set to nothing
it is not clear what effect has setting the connection to Nothing/Dispose-ing in OleDb
in ODBC: use the control panel (how do you turn it off???)
using the SqlClient instead of OleDb shows the application in Activity Monitor as .Net SqlClient Data Provider
using SqlClient seems to keep the connection alive even after closed for longer than OleDb (does it ever time out?)
using OleDb shows the application as the exe not the OleDb Data Provider
Which reminds me, each OS should provide some kind of relational/transactional storage service. Unix/Linux/Mac OS already does - SQLite.
