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)
Add a comment
|
4 Answers
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();
3 Comments
Nicolas Raoul
+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.
Mark
Your approach is an interesting case study in combinatorics, given the number of parameters allowed in SQLite connection strings :)
Andrew
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.
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;")
1 Comment
Mustafa Özçetin
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.)
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!
1 Comment
Andrew
I believe this is the only correct answer. The above answers setting the PRAGMA in the connection string did not work for me.
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
1 Comment
Sourabh
Commands that demonstrates the persistence: ``` $ sqlite3 demo.db SQLite version 3.43.2 2023-10-10 13:07:24 Enter ".help" for usage hints. sqlite> PRAGMA journal_mode; delete sqlite> PRAGMA journal_mode=WAL; wal sqlite> PRAGMA journal_mode; wal sqlite> .exit $ sqlite3 demo.db SQLite version 3.43.2 2023-10-10 13:08:14 Enter ".help" for usage hints. sqlite> sqlite> PRAGMA journal_mode; wal ```