Hello Devz,

SQLite as a SQL Engine Database became a standard a few years ago already. As it’s name says: it’s lite, and it doesn’t require a dedicated server. Cheap and performant, what else? At the end of this simple tutorial you will be able to setup the minimum requirements to use SQLite with Entity Framework in a small ASP.Net MVC project (or WPF, …), so let’s go !

Step 1: Create your database

I used this tool: SQLiteBrowser I must confess, this is the first one I found on google 😉 But any other tool should do the job, just choose the one you feel more comfortable with.

SQLite Browser

SQLite Browser

After the installation, create a new Database. The file path will be used later. So keep it somewhere. Let’s call our new database: SqliteDatabase.db. Ok, now it’s time to create our first table. Define a small entity called “Calculation”.

Which should look like this in the UI of SQLiteBrowser:

SQLite Browser edit table definition

SQLite Browser edit table definition

Step 2: Create your MVC project

Create your ASP.Net MVC application and install SQLite via the nuget package manager

PM> Install-Package System.Data.SQLite

VisualStudio Solution Explorer

VisualStudio Solution Explorer

In this example, we created a folder named “DataAccess” where we’ll put the code for Step3 and 5.

Step 3: The model

The type of the date is string. Because datatype are limited in SQLite (cfr https://www.sqlite.org/datatype3.html)

Step 4: ConnectionString

This is the connectionString used. Replace the data source by yours. (The one you create via the tool)

Step 5: DatabaseContext

We are almost done! Now we just have to create the context.

  • The database contains only one Table, represented in the code like this: DbSet<Calculation> Calculations
  • The GetConnection retrieves the information from your connection in the Web.Config
  • Remove<PluralizingTableNameConvention> only remove a feature of entity framework trying to pluralize your table name.

This is the mapping between your SQLite Table and your model.

How to Test it?

Finally, it’s time to test what we implemented. To quickly see if your code is working, you can add this piece of code in the default HomeController. It will read the database, add a new row, and read the database again to check it has benn written correctly.

Possible errors:

Issue 1

[ProviderIncompatibleException: CreateDatabase is not supported by the provider.]

Your data source is probably wrong, be sure your path is correct in your config file, e.g : “Data Source=C:\PRJ\SqliteDatabase.db”

Issue 2

[InvalidOperationException: No Entity Framework provider found for the ADO.NET provider with invariant name ‘System.Data.SQLite’. Make sure the provider is registered in the ‘entityFramework’ section of the application config file]

This line could be missing in your configuration file (inside <providers>)

Issue 3

‘An error occurred while executing the command definition. See the inner exception for details.’

Make sure to click on “Write changes” when you create your table. I did the mistake during the review of the post.

SQLite Browser Write changes

SQLite Browser Write changes

Source are available on DevOps !

https://code4noobz@dev.azure.com/code4noobz/DemoSQLite/_git/DemoSQLite

Happy coding!  🙂