LINQ To SQL for Sqlite
Updated: Jun 17, 2019
Today I am trying to get rid of our MS Access database. So I converted the same to SQLite for obvious reasons: SQLite has a small memory footprint and only a single library is required to access databases.SQLite is ACID-compliant, meeting all four criteria - Atomicity, Consistency, Isolation, and Durability.SQLite implements a large subset of the ANSI-92 SQL standard, including views, sub-queries and triggers.No problem of extra database drivers, ODBC configuration required. Just include the library and the data file with your application.First question come in find is provider for SQLite. I found two best provider:System.Data.SQLite originated by Robert Simpson but now in ownershio of the SQLite developemnt team future version will be available from here. It is licenced under public domain.Mono.Data.SQLite ADO.NET data provider for SQLite database version 2 and 3. I prefer first one, as its closely supported by SQLite team. Since we are changing the databse then why not use LINQ to SQL. But no VS IDE support for SQLite so no drag and drop dbml files as for SQL server. So search begins again, that leads to Dblinq. DbLinq is THE LINQ provider that allows to use common databases with an API close to Linq to SQL. It currently supports (by order of appearance): MySQL, Oracle, PostgreSQL, SQLite,Ingres, Firebird... And still SQL Server. DbLinq.Sqlite.dll: SQLite support, using either the System.Data.SQLite or Mono.Data.Sqlite drivers. Also Dblinq has a SQLMetal equvivalent DbMetal tool to genrate dbml or code file, whoe switches can be find here, its under MIT licence. Next step create Dbml file using DbMetal (make sure you have copied the System.Data.SQLite.dll with DbMetal.exe):
DbMetal /provider:Sqlite /conn "Data Source=MyDatabaseFile.db3" /dbml:MyDatabase.dbml
next add this dbml file to your solution ("Add existing"). In designer.cs you may have to change the provider attribute of the class to DbLinq.Data.Linq.SqlClient.SqliteProvider. Now connect to the SQLite.
var connection = new SQLiteConnection( "DbLinqProvider=Sqlite; " + "Data Source=MyDatabaseFile.db3" // + other connection string key/value pairs ); var db = new YourDbmlClass(connection);
And hooray now u have LINQ to SQLite :-).