1. Create a new .Net Core 3.1 (LTS) Web Project
  2. Right click your project and select Manage NuGet Packages
  3. Go to Browse, search for System.Data.SQLite.Core and install the package
  4. Done!

You now have the ability to use SQLite in your web project. You could add other packages like Entity Framework if any others are suitable to your project.

For quickly putting something together to test an idea I like to start off with the repository pattern which can be a very simple way of dealing with data. You will need to create a class of any name you choose and decide that all ‘data’ work happens within the class, so the rest of your project does not know of or use SQLite in any way. This keeps all your data work in one place which is handy if you later decide to move it out to a separate project or even refactor it to work a different way.

using System;
using System.Data.SQLite;


namespace MyIdea.Persistence
{
    public class SongRepository
    {
        string _connectionString = string.Empty;

        public SongRepository(string sqliteConnectionString)
        {
            if (string.IsNullOrWhiteSpace(sqliteConnectionString))
                throw new ArgumentException(nameof(sqliteConnectionString));

            _connectionString = sqliteConnectionString;
        }
    }
}
  • This SongRepository class is in the Persistence folder so Visual Studio has automatically given it that namespace
  • The constructor takes the connection string as a string parameter. This can be picked up from appsettings.json in Startup.cs where this class could be added to the dependency injected services
        public List AllSongs()
        {
            List result = new List();
            using (var db = new SQLiteConnection(_connectionString))
            {
                using (var cmd = db.CreateCommand())
                {
                    db.Open();
                    cmd.CommandText = "SELECT Title, URL FROM Songs ORDER BY Title";
                    var reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            result.Add(new Song() { 
                                Title = reader.GetString(0),
                                URL = reader.GetString(1)
                            });
                        }
                    }
                    db.Close();
                }
            }
            return result;
        }
  • Basic method to get all songs from the table. This is not the approach to use in production but it fine for building an example to show an idea.

Creating the Database

With SQLite it’s actually pretty easy to make the database: Create a blank file and give it a .db extension which works fine if you’re going to generate your database from code. But sometimes it’s useful to have a more visual approach, especially when you’re getting to grips with how it all works. DB Browser for SQLite is great for working with SQLite databases and has a good, clean, interface with all the tools you need right where you need them. Just remember to click Write Changes before closing the program!

The connection string has some options that might be useful in different scenarios but the basic string to use is to specify the path or just the filename if the database will be in the directory next to the running assembly

Data Source=Database.db

NOTE that the way .Net Core projects compile might seem misleading about just where to put your database.

Connecting to the Database

After adding the connection string to your appsettings.json you can add your repository as a singleton service in startup.cs

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "Database": "Data Source=Database.db"
  }
}
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddRazorPages();
            services.AddSingleton(typeof(SongRepository), new SongRepository(Configuration.GetConnectionString("Database")));
        }

Using the Database on a Page

Now that the repository is ready and part of the dependency injection you can add it to your controller or page’s contstructor and it will be made available by the background magic.

Here is my Index.cshtml.cs

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using TableSongRequest.Models;
using TableSongRequest.Persistence;

namespace TableSongRequest.Pages
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        private readonly SongRepository _repository;

        public IndexModel(ILogger<IndexModel> logger, SongRepository repository)
        {
            _logger = logger;
            _repository = repository;
        }

        public List<Song> Songs { get; set; }

        public void OnGet()
        {
            Songs = _repository.AllSongs();
        }
    }
}

This is the Index.cshtml file

@page
@model IndexModel
@{
    ViewData["Title"] = "Home page";
}

<div class="text-center">
    <h1 class="display-4">Welcome</h1>
    <p>
        @if (Model.Songs.Count > 0)
        {
        <ul>
            @foreach (var song in Model.Songs)
            {
                <li>@song.Title</li>
            }
        </ul>
        }
        else
        {
            <p>No songs</p>
        }

    </p>
</div>

This short tutorial is to give a quick rundown of using SQLite with C# in .Net Core 3.1 and hopefully covers things in enough detail that anyone can get their own project working in good time. One problem I find with many other tutorials is they miss which version of Visual Studio or Framework/Core/Standard they’re targetting and many outright ignore the namespaces to include.