SQLite is an open-source SQL database engine. It is widely used in iOS apps. The database is available in the form of a file.

Create Database

To create a database with name accounting:

.tables is a command which lists all tables. Add a new table, Company. And insert a few rows.

ALTER TABLE has restricted syntax. It does not have an option to add a primary key. So, if you want to add a primary key, do it at the time of table creation.

LINQ to SQL

SQLite has a NuGet package for .NET. There are two ways to connect to the database, using LINQ to SQL or Entity Framework.

LINQ to SQL has a convenient mapping notation using attributes. Attributes are a convenient way to map the tables and columns in the database to .NET classes.

  1. TableAttribute – maps the table name to a .NET class.
  2. ColumnAttribute – maps a column name to a .NET property.

Create a entity class named Company as follows:

LINQ to SQL by default works with SQL Server. To help LINQ to SQL work with SQLite, pass the connection object to create the DataContext object.

The connection string for SQLite database is simple. It has the format of Data Source=<Path of the file>. The connection string is the only difference from any other LINQ to SQL implementation.

Entity Framework

Other than LINQ to SQL, Entity Framework is available for use. In web.config, the following configuration sections should be available.

The connection string name should be passed to the context class.

The connection string is specified in the config file. It is very similar to the connection string used in LINQ to SQL.

Table and Column attributes are used to map the database table and columns to a .NET class. The additional attribute, DatabaseGenerated, indicates if the primary key is auto-generated or not. By default, the key is auto-generated.

 

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