SQLite is an open-source SQL database engine. It is widely used in iOS apps.

Create SQLite Database

SQLite has an executable sqlite3.exe. To create a database with name accounting:

  1. Open the command prompt.
  2. Execute “sqlite3 accounting.sqlite”.
  3. The above command creates an accounting database.
  4. Execute “.tables”.

.tables is a SQLite command which lists all tables. Add a Company table and a few rows with the following SQL:

ALTER TABLE for SQLite does not do alter tables much. So, if you want to add a primary key, do it at the time of table creation.

LINQ to SQL

To use the database from .NET, SQLite has released a NuGet package:System.Data.SQLite. The assemblies from the package are:

  1. System.Data.SQLite.Core
  2. System.Data.SQLite.Linq
  3. Entity Framework (6.1)
  4. System.Data.SQLite.EF6

LINQ to SQL is available in .NET to query databases using SQL. LINQ to SQL has a convenient mapping notation using attributes. To map the tables and columns in the database to .NET classes, two attributes are used:

  1. TableAttribute
  2. ColumnAttribute

The attributes can be found in the System.Data.Linq assembly, and in System.Data.Linq.Mapping namespace.

Create a entity class named Company as follows:

DataContext using SQLiteConnection

LINQ to SQL by default works with SQL Server. To help LINQ to SQL work with other SQL engines like SQLite, used the overloaded method to create the DataContext object. The DataContext object can be created with a connection object. In this case, SQLiteConnection object can be used to create the DataContext object.

The connection string for SQLite database is simple. It has the format of “Data Source=<Path of SQLite file>”. SQLiteConnection object is initialized with the connection string. The DataContext object is created with the SQLiteConnection object.

Once the DataContext object is created, the rest of the code is a straight-forward LINQ to SQL implementation.

Entity Framework

With Entity Framework, the configuration is a bit tricky. In web.config, the following config sections should be proper:

In addition the context class should reference the connection string as follows:

The connection string should reference the right provider as follows:

Entity framework configuration is quirky because the connection string does not respect the providerName property. This is explained in theStackOverflow article.

Summarizing the following changes should be made, after installing System.Data.SQLite:

  1. Adjust the remove property in DbProviderFactories to appear above the System.Data.SQLite invariant.
  2. Fix the providerName as System.Data.SQLite. Any other providerName is not respected in the connection string.
  3. Change the EF provider to be System.Data.SQLite instead of System.Data.SQLite.EF6 (which comes when the package is installed).

Inserting data using SQLite EF provider

Inserting data using the SQLite EF provider is a bit trouble-some. The EF provider assumes that the Key column is auto-generated. By default, it omits the Key value from the INSERT statement. To ensure that the Key column is also inserted, use the DatabaseGenerated attributed as follows:

The DatabaseGeneratedOption.None ensures that the Id value is sent within the SQL query to SQLite. The insert works fine after this adjustment.

Using SQLite database in .NET with LINQ to SQL and Entity Framework 6
Tagged on:     

Leave a Reply

Your email address will not be published. Required fields are marked *