Disable/Flush OleDbConnection Cache

Question

I've been fighting with OleDbConnection for a while now trying to get it to not cache. Basically I am accessing a shared Access database, which is being written to from another application, and then I'm reading back values (having checked that it is flushed via the Last Write time and a subsequent 1 second delay).

Unfortunately, this is entirely unreliable.

I've been reading (and going insane) how to disable the connection pooling, and am subsequently, after each possible update, performing the following before reconnecting:

_connection.Close();
_connection.Dispose();
_connection = null;
OleDbConnection.ReleaseObjectPool();
GC.Collect();

In addition to this, the connection string disables connection pooling with OLE DB Services = -2. Finally, I have also changed PageTimeout to '10' in the registry for Jet 4.0.

All of these measures are unfortunately having no effect. Now the only thing I can think of doing is what is mentioned in this Microsoft KB Article, and call JRO.JetEngine.RefreshCache. The only issue with that is that it's argument is an ADODB.Connection. I'd rather not rewrite my whole database layer and where the records are being read by my software to use a legacy COM object just to have this functionality, but it appears that it may well be the only way.

My question is, whilst currently undergoing this task of rewriting to use ADODB (not even ADO.NET!), is it possible to disable the caching of an OleDbConnection?

Suttles

Answer 1

Finally, I found a workaround: Use OdbcConnection instead of OleDbConnection.

This is the old code:

string mdbConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + mdbFile + ";OLE DB Services=-2";
using (OleDbConnection conn = new OleDbConnection(mdbConnectionString)) {
    conn.Open();
    //Do your query
}

And this is new one:

string mdbConnectionString = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=" + mdbFile;
using (OdbcConnection conn = new OdbcConnection(mdbConnectionString)) {
    conn.Open();
    //Do your query
}

All things work fine.

Guardianship

Answer 2

32-bit application

In a 32-bit C# application, I'm doing this since years to refresh the cache:

public static void RefreshDatabaseCache(
    string connectionString)
{
    // The type of the ADODB connection. Used for dynamically creating.
    var adodbType = Type.GetTypeFromProgID(@"ADODB.Connection");

    // The main ADODB connection object.
    var adodbInstance = Activator.CreateInstance(adodbType);

    // --

    // Open the connection.
    adodbType.InvokeMember(
        @"Open",
        BindingFlags.InvokeMethod,
        null,
        adodbInstance,
        new object[]
        {
            connectionString,
            string.Empty,
            string.Empty,
            0
        });

    try
    {
        // The type of the JET engine. Used for dynamically creating.
        var jroType = Type.GetTypeFromProgID(@"JRO.JetEngine");

        // The main JET engine object.
        var jroInstance = Activator.CreateInstance(jroType);

        // Refresh the cache.
        jroType.InvokeMember(
            @"RefreshCache",
            BindingFlags.InvokeMethod,
            null,
            jroInstance,
            new[]
            {
                adodbInstance
            });
    }
    finally
    {
        // Close the connection.
        adodbType.InvokeMember(
            @"Close",
            BindingFlags.InvokeMethod,
            null,
            adodbInstance,
            new object[]
            {
            });
    }
}

I'm using OleDB in my whole application, just inside the above function, I'm using this "ADODB" thing.

64-bit application

In a 64-bit C# application I currently do know no way of doing this.

Irreducible

Answer 3

You might have some luck setting the ";Jet OLEDB:Flush Transaction Timeout" property to 0 or some low number.

See documentation.

Endless