Search code examples
c#sqlitesystem.data.sqlite

Enable shared cache mode in System.Data.Sqlite (.net)

I'm looking for a way to enable shared cache mode when using the System.Data.SQLite wrapper for SQLite.

I've looked through the source code for this project, and see that it's exposed internally to the assembly in UnsafeNativeMethods.cs as: 

internal static extern SQLiteErrorCode sqlite3_enable_shared_cache(
    int enable);

Unfortunately, I can't get at this method since it's internal.

Anyone have a solution for this?

Replies were most appreciated. Thanks!

FYI, when using the SQLiteConnectionStringBuilder API, enable the shared cache by:

var builder = new SQLiteConnectionStringBuilder();
...
builder.Add("cache", "shared");
Solution

  • SQLite uses PRAGMA statements to modify the database operations. These statements are specific to SQLite. PRAGMA statements can be anything from enabling Foreign Keys, changing schema versions right through to setting the Shared-Cache options (A full list of pragma commands are available here) With Pragma statements I am aware of two ways to execute them; 1) when the connection string is being instantiated or 2) Loaded as a command

    1) During Instantiation

    new SQLiteConnection("Data Source=c:\mydb.db;Version=3;cache=shared");

    2) Separate Command Pragma statements can be executed like any normal database command sqliteConnection.Open();

    var cmd = new SQLiteCommand("PRAGMA cache=shared",sqliteConnection);
cmd.ExecuteNonQuery();

    Another question worth a look: SQLite SharedCache MultiThread Reads