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)

score 15 · Accepted Answer · edited May 30 '19 at 23:21

15

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();

edited May 30 '19 at 23:21

Mark Benningfield

2,800
9
31
31

answered Apr 08 '13 at 02:31

Turbot

5,095
1
22
30

+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. – Nicolas Raoul Apr 08 '13 at 03:51
2

Your approach is an interesting case study in combinatorics, given the number of parameters allowed in SQLite connection strings :) – Mark May 30 '14 at 16:49
1

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. – Andrew May 20 '20 at 21:35

score 11 · Answer 2 · answered Apr 08 '13 at 03:48

11

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;")

answered Apr 08 '13 at 03:48

Nicolas Raoul

58,567
58
222
373

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.) – Mustafa Özçetin Mar 03 '23 at 06:54

Nicolas Raoul · Answer 3 · 2013-04-08T03:52:24.180

6

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!

edited Apr 08 '13 at 03:52

answered Apr 08 '13 at 02:02

Nicolas Raoul

58,567
58
222
373

3

I believe this is the only correct answer. The above answers setting the PRAGMA in the connection string did not work for me. – Andrew May 20 '20 at 21:36

score 6 · Answer 4 · answered Jun 15 '13 at 11:47

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

How to open SQLite connection in WAL mode

4 Answers4

Linked

Related