How to open SQLite connection in WAL mode

Question

In C#, how to open an SQLite connection in WAL mode?

Here is how I open in normal mode:

SQLiteConnection connection = new SQLiteConnection("Data Source=" + file);
connection.Open();
// (Perform my query)

Avisavitaminosis

Answer 1

how about a factory approach to specify in the SQLiteConnection connetion string ?

for e.g

public static class Connection
{
    public abstract SQLiteConnection NewConnection(String file);
}

public class NormalConnection : Connection 
{
  public override SQLiteConnection NewConnection(String file)
  {
     return new SQLiteConnection("Data Source=" + file);
  }
}

public class WALConnection : Connection 
{
  public override SQLiteConnection NewConnection(String file)
  {
    return new SQLiteConnection("Data Source=" + file + ";PRAGMA journal_mode=WAL;"
  }
}

The code is not tested, but I hope you can get the idea, so when you use it you can do like that.

   SQLiteConnection conWal = new WALConnection(file);
    conWAL.Open();

    SQLiteConnection conNormal = new NormalConnection(file);
    conNormal.Open();

Obsequent

+1 The last line of your code is the solution I was looking for, thanks a lot! The factory approach can be interesting, even though I do not need it in my case. – Avisavitaminosis

Your approach is an interesting case study in combinatorics, given the number of parameters allowed in SQLite connection strings :) – Sallysallyann

This solution does not work for me. The only way I could put the DB in journal_mode=WAL was to issue a separate command, after establishing a connection. – Goodoh

Answer 2

The line below is what I was looking for, many thanks to Turbot whose answer includes it:

new SQLiteConnection("Data Source=" + file + ";PRAGMA journal_mode=WAL;")

Avisavitaminosis

Using this throws ArgumentException in Microsoft.Data.Sqlite library. For those using this library, executing the PRAGMA with an SqliteCommand immediately after opening the connection is the way to go. (As with your "less-than-perfect" solution.) – Funest

Answer 3

Here is my less-than-perfect solution:

SQLiteConnection connection = new SQLiteConnection("Data Source=" + file);
connection.Open();
using (var command = new SQLiteCommand(sqliteConnection))
{
    command.CommandText = "PRAGMA journal_mode=WAL";
    command.ExecuteNonQuery();
}
// (Perform my query)

If you know something less verbose, I would be happy to hear about it!

Avisavitaminosis

I believe this is the only correct answer. The above answers setting the PRAGMA in the connection string did not work for me. – Goodoh

Answer 4

Persistence of WAL mode

"Unlike the other journaling modes, PRAGMA journal_mode=WAL is persistent. If a process sets WAL mode, then closes and reopens the database, the database will come back in WAL mode."

http://www.sqlite.org/wal.html

If I understand it correctly, this means that you can set WAL mode for a database once, there's no need to set it on every connection.

You can do it with the command line shell for SQLite: http://www.sqlite.org/sqlite.html

Rosalvarosalyn