SQLite in .Net

After using SQLite for a while, a few conclusions that I had were

1. SQLite is quite stable enough to be used in corporate environment.
2. SQLite is faster than SQLCE.
3. For a SQLite admin, use the Firefox addon –  https://addons.mozilla.org/de/firefox/addon/sqlite-manager/
4. SQL Server Compact Toolbox will be useful: https://visualstudiogallery.msdn.microsoft.com/0e313dfd-be80-4afb-b5e9-6e74d369f7a1
5. Both SQLite and SQLCE cannot be used in a condition that requires to write in DB concurrently. To make sure that an application does not access the DB concurrently, ‘lock’ statement was used.
6. Both can be used with Entity Framework, which is nice.

Installation is relatively easy, but set up could be confused as some of tutorial does not work as it should be due to the version difference (?) and NuGet didn’t properly updated config file in my case, so I had to update it manually.

I’ve current set up both SQLite and SQLCE with the versions as below.

Entity Environment 6.0
Data.Dqlite.EF6 1.0.98.0
System.Data.SqlServerCe 4.0

Below is the configuration for SQLite and SQLCE to be used together.

<connectionStrings>
 <add name="SQLiteLogContext" connectionString="Data Source=D:\Stash\Projects\AU\WestPac\CustomWebService\WcfCustomService\Log\RTWS.sqlite" providerName="System.Data.SQLite.EF6" />
 <add name="SQLCELogContext" providerName="System.Data.SqlServerCe.4.0" connectionString="Data Source=D:\Stash\Projects\AU\WestPac\CustomWebService\WcfCustomService\Log\RTWS.sdf" />
</connectionStrings> 
<runtime>
 <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
 <dependentAssembly>
 <assemblyIdentity name="EntityFramework" publicKeyToken="b77a5c561934e089" culture="neutral" />
 <bindingRedirect oldVersion="0.0.0.0-4.4.0.0" newVersion="4.4.0.0" />
 </dependentAssembly>
 </assemblyBinding>
</runtime>
<entityFramework>
 <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
 <parameters>
 <parameter value="System.Data.SqlServerCe.4.0" />
 </parameters>
 </defaultConnectionFactory>
 <providers>
 <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
 <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
 <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.98.0, Culture=neutral" />
 <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
 </providers>
</entityFramework>
<system.data>
 <DbProviderFactories>
 <remove invariant="System.Data.SQLite.EF6" />
 <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
 <remove invariant="System.Data.SqlServerCe.4.0" />
 <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
 </DbProviderFactories>
</system.data>

Useful links regarding SQLite in .Net C#

Tutorial

http://www.tutorialspoint.com/

http://www.codeproject.com/Articles/236918/Using-SQLite-embedded-database-with-entity-framewo

Good and concise article

https://damienbod.wordpress.com/2013/11/14/using-sqlite-with-net/

Best example

https://damienbod.wordpress.com/2013/11/18/using-sqlite-with-entity-framework-6-and-the-repository-pattern/

Get VS13 DB designer with SQLite

http://stackoverflow.com/questions/25089346/database-first-create-entity-framework-6-1-1-model-using-system-data-sqlite-1-0

Setting for Performance

PRAGMA main.page_size = 4096;
PRAGMA main.cache_size=10000;

PRAGMA main.cache_size=5000;

PRAGMA main.locking_mode=EXCLUSIVE;
PRAGMA main.synchronous=NORMAL;
PRAGMA main.journal_mode=WAL;

Pasted from <http://stackoverflow.com/questions/784173/what-are-the-performance-characteristics-of-sqlite-with-very-large-database-file>

Keep the max size of database

http://stackoverflow.com/questions/5987042/how-do-you-efficiently-trim-an-sqlite-database-down-to-a-given-file-size

SQLite Dispose or Close issue

http://stackoverflow.com/questions/8511901/system-data-sqlite-close-not-releasing-database-file

http://stackoverflow.com/questions/12532729/sqlite-keeps-the-database-locked-even-after-the-connection-is-closed/12679840#12679840