Useful things to know when using SQLite and .NET

This is worth knowing when using SQLite and the System.Data.Sqlite .Net library.

1) Various connection strings

Source:  http://www.connectionstrings.com/sqlite

e.g.

using cache>  Data Source=c:\mydb.db;Version=3;Cache Size=3000;

using a memory database> Data Source=:memory:;Version=3;New=True;

read only> Data Source=c:\mydb.db;Version=3;Read Only=True;

Journal files>
Data Source=c:\mydb.db;Version=3;Journal Mode=Off;
Data Source=c:\mydb.db;Version=3;Synchronous=Full;
(means full flush after each write)

Using compression >
Data Source=c:\mydb.db;Version=3;Compress=True;

 

2) DB locking issues and how to get around it

Source:  http://www.lastaddress.net/2010/11/systemdatasqlite-locks-database-even-on.html

using (SqliteConnection sqlCon = new SqliteConnection(connStr))
{

  sqlCon.Open();
  using(SqliteCommand sqlCom = new SqliteCommand(sqlCon, sqlStr))

  {

     using(SqliteDataReader rdr = sqlCom.ExecuteReader())

     {

        //do data stuff

        rdr.Close();

     }

     sqlCon.Close(); //CLOSE sqlCon BEFORE sqlCom IS DESTROYED!

  }
}