Search This Blog

Tuesday, March 12, 2013

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!

  }
}