Force an OleDbConnection to Release a File Handle

Question

Related Question

My code doesn't release a file handle even after I call dispose to an initialized OleDbException. Is there a way to explicitly force the program to release a file handle?

Answer 1

By default, .NET database connections use pooling. Calling Close() and Dispose() just releases the connection back into the pool, it doesn't actually force it to close. Eventually it will time out of the pool, and actually be closed.

After a bit of research, there seem to be two main ways to get it close predictably:

1. Disable pooling in the connection string - try adding OLE DB Services = -2;, which should give you all services except pooling
2. Try to make use of OleDBConnection.ReleaseObjectPool()

For the latter approach you might need to play with timeouts - excerpt from the linked MSDN article:

Note that calling the method alone does not actually release the active connections that exist in the pool.

The following must occur before the pool is finally disposed:

1. Call Close to return the connection object to the pool.
2. Allow each connection object to time out of the pool.
3. Call ReleaseObjectPool.
4. Invoke garbage collection.

I have a use-case for this at work, where some in-house software needs to interact with piece of old, inflexible, flaky and absolutely critical proprietary software. It needs to open a shared MDB database file for as short a time as possible to minimise the window where the other software might "take issue" (a Very Bad Thing).

I'm planning on using the connection string approach, as it looks to be simpler to guarantee the close, and my software doesn't really benefit from the pool.