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?

D.Rosado · Accepted Answer · 2013-09-18T15:34:05.673

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
    }

Notice that you don't have to set WAL for every connection - it's enought to set it once during create your database. — Przemysław Michalski, Feb 18 '16 at 13:32

score 4 · Answer 2 · answered May 26 '12 at 09:58

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.

score 1 · Answer 3 · answered Dec 31 '11 at 03:32

1

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.

answered Dec 31 '11 at 03:32

competent_tech

44,465
11
90
113

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? – Mike Rebosse Dec 31 '11 at 04:21
1

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. – competent_tech Dec 31 '11 at 04:47

System.data.sqlite - Activating WAL Journal Mode

3 Answers3