Use SQLite with .NET Core

In this tutorial we are going to make use of the database engine SQLite with .NET Core. I am using Visual Studio 2019, but of course later works as well. To use SQLite with .NET Core is a really good idea in my opinion because SQLite is a relational database, and very light and easy to get started with. Also you can even have the whole database running in memory if you’d like. But let’s do the way where you have the database on disk, on your computer.

Let’s play with the idea that we want to create a support case application.

First of all lets create a new project.

I will call it LiteProgram.

Now in our models folder, let’s create a new class called Case, something like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace LiteProgram.Models
{
    public class Case
    {
        public int CaseId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
    }
}

Okay great, we have our first class for the Case. Let’s keep it simple and don’t add too much useless things yet. You might get the popup that you need to download EntityFrameWorkCore, if not do it like this, open up the Nuget Package manager under Tools and find Microsoft.EntityFrameworkCore like this:

We will also download the package Microsoft.EntityFrameworkCore.Sqlite, so find and download that as well:

Then create a new folder called Data, in it create a class you name CaseContext.cs. In this file, have this code which tells the program to use a (SQLite) database file on the local computer. In this case, the path would be C:\Users\myname\AppData\Local\liteprogram.db. However, no db is created yet since the program is just configurated to use the database, so we would get exceptions in the style of : ‘no such table’ and so forth. This means that we should add dotnet-ef so we can run commands such as database update which will create the database for us based on the models we have, because we use DbSet<Case>. So let’s open up Terminal (find under View, to the third top left) and type:

dotnet tool install --global dotnet-ef

We also need one more package to get the database commands to work which is called Microsoft.EntityFrameworkCore.Design

So run the following command, Note that you might need to “step in” to the project folder from your repository folder if you are not inside the project folder already (cd LiteProject):

dotnet add package Microsoft.EntityFrameworkCore.Design

Great we got the EF Core Tools along with the Design package, let’s try out the dotnet ef database update command:

dotnet ef database update

What will happen now is that we will recive a SQLite “.db”-database file! As you can see under your folder at C:\Users\myname\AppData\Local. You can download DB Browser for SQLite and check out your own database there if you want to see how it looks, and all we have is a single table called __EFMigrationsHistory with on data, no migrations.

So let’s add one.

Run the command:

dotnet ef migrations add InitialCreate

Now we have a new folder containing our new migration, and already Core understood that the table Cases is missing, and that this is what we want to add because we have our CaseContext where we defined a DBSet with the class Case, containing our fields. Let’s update our database with the latest migration, meaning, let’s add the new table.

dotnet ef database update

Our database now has a new table along with a new row in the migration table keeping track of the migration history and version for us.

Using the SQLite database with a Controller

First before creating a controller, make sure you have Added your Context to the services to it will be injected like so under the ConfigureServices method in startup.cs.

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<CaseContext>();
            services.AddControllersWithViews();
        }

Now is where the fun start, where we can start using these fields. Let’s right click on the Controllers and create a new Controller, choose the one where we utilize EntityFramework with views.

Choose Case for your Model class, and choose CaseContext for your Data context class. Let’s generate views, and reference script libraries, and check Use a layout page. I’ll go with the name CasesController. You can now check out the generated Views and see how the Controller is working under /Cases, in my case, https://localhost:44330/Cases. While there, try adding some stuff!

And we can see we are now working with the SQLite database.

That’s how you get started with .NET Core and with SQLite in the simplest way possible.

0 0 votes
Article rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments