How to change "journal_mode" of a Sqlite database in C#

Question

Following the instructions of Sqlite's PRAGMA I found that PRAGMA schema.journal_mode; changes the journal_mode and given the options I chose off to increase performance of insert function. I wrote:

SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;PRAGMA Schema.journal_mode=off;");

which opens a database called MyDatabase.sqlite and the command

PRAGMA Schema.journal_mode=off;

which is written in the end, I believe turns off the journaling of the sqlite database, but I don't have a clue on how to do it and if this is the proper way then what am I doing wrong because I see no change in performance after adding the PRAGMA command.

I downloaded the Sqlite Library from a link referred in Tigran's Blog Post on Sqlite

Answer 1

The PRAGMA keyword is not for use in connection strings. The proper connection string syntax would be:

SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;journal mode=Off;");

One way to discover these is to use the SQLiteConnectionStringBuilder object:

SQLiteConnectionStringBuilder lcb = new SQLiteConnectionStringBuilder();
lcb.JournalMode = SQLiteJournalModeEnum.Off;
lcb.DataSource = sqlFile;
lcb.Version = 3;

string myLtConnStr = lcb.ConnectionString;

Result:

"journal mode=Off;data source=\"C:\SQLite Dbs\mydata.db\";version=3"

Some DB providers have many many options -- particularly with regard to DateTime handling and options -- which can be toggled this way. Once you know the syntax, you can elide the ConnectionStringBuilder object.