System.data.sqlite - Activating WAL Journal Mode

Question

I am using the System.data.sqlite.dll in my vb.net program. And for the life of me I can't figure out the code to activate WAL mode.

Do I activate this command right after I Create the DB or with every new SQLiteConnection.

And if so what code would need to be used right now im using something like:

cnn As New SQLiteConnection(String.Format("Data Source={0}\{1};PRAGMA jounal_mode=WAL;", Application.StartupPath, DBName))

is this how that PRAGMA command should be used?

Ackerley

Answer 1

You can always use the SQLiteConnectionStringBuilder class to do the job for you:

    SQLiteConnectionStringBuilder connBuilder = new SQLiteConnectionStringBuilder();
    connBuilder.DataSource = filePath;
    connBuilder.Version = 3;
    //Set page size to NTFS cluster size = 4096 bytes
    connBuilder.PageSize = 4096;
    connBuilder.CacheSize = 10000;
    connBuilder.JournalMode = SQLiteJournalModeEnum.Wal;
    connBuilder.Pooling = true;
    connBuilder.LegacyFormat = false;
    connBuilder.DefaultTimeout = 500;
    connBuilder.Password = "yourpass";


    using(SQLiteConnection conn = new SQLiteConnection(connBuilder.ToString()))
    {
    //Database stuff
    }

Mantic

Notice that you don't have to set WAL for every connection - it's enought to set it once during create your database. – Nicodemus

Answer 2

This is a sample connection string from my project (App.config):

  <connectionStrings>
    <add name="SQLiteDb" providerName="System.Data.SQLite" connectionString="Data Source=data.sqlite;Version=3;Pooling=True;Synchronous=Off;journal mode=Memory"/>
  </connectionStrings>

Instead of journal mode=Memory you can specify journal mode=WAL.

If you don't specify journal mode in connection string, you can switch it manually by executing PRAGMA jounal_mode=WAL query to database.

Ultun

Answer 3

You need to execute the pragma as a command nonquery.

Using cmd As SQLiteCommand = cnn.CreateCommand()
   cmd.CommandText = "PRAGMA journal_mode=WAL"
   cmd.ExecuteNonQuery()
End Using

As long as you keep your connection open, setting this once will be enough.

Defeasance

Thanks, much appreciated. However I tend to close the connection often, usually after inserts, Update's Ect.. Does that mean I pretty much have to include that with my Insert, Update, Delete, subs? – Ackerley

Sadly, yes. If it is a local-only database, there shouldn't be any downside to leaving the connection open throughout the life of the application. We do this all the time with mobile and tablet devices that have a local DB. – Defeasance