System.data.sqlite - activate WAL log mode

Question

I am using System.data.sqlite.dll in my vb.net program. And for my life, I can’t understand the code for activating the WAL mode.

I activate this command immediately after creating the database or with each new SQLiteConnection.

And if so, what code will need to be used right now, using something like:

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

Is this how the PRAGMA command should be used?

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 } 

Answer 2

This is an example of a 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 do not specify the log mode in the connection string, you can switch it manually by executing the PRAGMA jounal_mode=WAL query to the database.

Answer 3

You need to execute the pragma as a command without a request.

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

While you open your connection, setting this option will suffice.