How to use SQLite in ASP.NET CORE Web API or Web Application?

A simple and easy step-by-step guided article encourages you to get insights from the scratch, of how to create and use SQLite database in ASP.NET Core Web Application, ASP.NET Core Web API Application, Entity Framework Core, Dot Net Core, or .NET 5.

The usage of SQLite either in Web Application or Web API is similar. For this tutorial let's explore ASP.NET Core 5 Web API application and Entity FrameworCore. 

Key scenarios

You may be experienced using SQL Server, SQL Express databases while developing applications, whereas SQLite is a lightweight relational database management system that comes in C file-based storage, and it is embedded into the end program.

Before we continue creating an application, let's catch up  SQLite database.

As SQL Server has Management Studio to do CRUD (Create, Read, Update, Delete) operations, SQLite also has an SQLite Studio where we create a database do all the CRUD operations and export to a file with an extension .db 

Now just grab the database file and add it to the project location. Unlike SQLServer we don't require an installation rather it is a file-based RDBMS.

What are the topics covered?

1.  Installing SQLite Studio, Creating a database and Exporting it.

2.  Adding SQLite Database in Visual Studio 2019  - or add SQL Server Compact Toolbox extension

3.  Add NuGet Packages for - SQLite Entity Framework Core

4.  Add SQLite database file to Project Solution

5.  Add Entity Framework Context - Class Models

6.  Add SQLite Connection String in appSettings.json and Startup. cs 

Let's explore step by step! 

Step 1. Installing SQLite Studio 

Let's now download the SQLite Studio from this link - Click here or visit the SQLite website to download. 

Open the SQLite Studio from the Start menu

This is how the SQLite Studio opens up, please also note what you see in the image is the sample database created with one table name "AuthCodes". 

  

Now, how do I create a query and execute it in SQLite?

Click on the Toolbar "SQL Editor" and type in your SQL Statements Create/Update/Select/Delete. If you see the below image SQL Query in the last has an additional statement for Sequence - What is it?

Remember when you set the Id column as Identity Autoincrement  SQLite provides DML support to reset the incremented field.

What is the identity column?

when creating a table with columns usually the first one is Id which holds a sequence number, and another column(s) holds other important data.

Over a period of insertion of records or deleting all or any of the records, the last maximum number will be considered +1 for the new record. Thus, resetting to 0 would help to reach out in small numbers.

 

Info! SQLite Sequence update reset the value to Identity column *case sensitive

Here you are selecting sequences and updating table AuthCodes identity value to 0.

SELECT * FROM SQLITE_SEQUENCE;

UPDATE SQLITE_SEQUENCE SET seq = 0 WHERE name = 'AuthCodes';

How SQL Server handles the Identity Column Reset?

Info! This is how the SQL Server Identity field is reset

The employee is your table name

DBCC CHECKIDENT ('Employee', RESEED, 0); 

So, until here you have created a database and tables. Now you want to export database files with extension .db to use in the Web API application. The next question is how do I export to the .db file? 

Go to Menu Export and choose File and provide your export file name, filename.DB gets stored in the folder you have chosen.

Step 2: Adding SQLite in Visual Studio 2019

To use SQLite in Visual Studio IDE required to add an extension SQL Server Compact Toolbox 

Before install please make sure you close all the Visual Studio Project files.

Let's download the extension file from    

Step 3: Add NuGet Packages for - SQLite Entity Framework Core

Now let's add NuGet packages Entity Framework Core to the project.

Go to Solution Explorer > Right-click on the projet > Manage Nuget packages... and in browse tab search Microsoft.EntityFrameworkCore.Sqlite and install it.

Step 4: Add SQLite file to Project Solution

In this article, I cover adding SQLite to ASP.NET Core Web API (.NET 5) project

Step 1: In Solution Explorer add a folder Data  you name it anything 

Step 2:  Add the exported SQLite database file with an extension.db to the folder Data  created.  ( if you recall beginning of this article we have exported a database file, or even you can use the db created in Visual Studio Editor)

consider you have installed extension now open back the Visual Studio Project, 

Go to Tools menu > should see "SQLite/SQL Server Compact Toolbox" as below image.

Add your SQLite Connection to start creating the SQLite database from Visual Studio Editor.

If you recall in Step 1 we have created an SQLite database and exported it to a file from SQLite Studio. There are two options either to restore it or create a new database. From the below image add to SQLite Connection opens up the dialog to choose "Browse" to restore and click on  Create for the new database.

This is how SQLite database will be in Visual Studio,

Now the next question be - How do I add SQLite connection or link database to Visual Studio Project? 

Step 5: Add Entity Framework Context - Class Models

 

Add the Class Model  AuthCodes 

AuthCode

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

namespace RCR.Web.ACRA.API.Models
{
    public class AuthCodes
    {
        public int Id { get; set; }
        public string SessionId { get; set; }
        public string AuthCode { get; set; }    
    }
}

The DBContext class file makes the initial constant connection to the SQLite database intact.

In this article, we have a demo table AuthCodes and we are creating a database context class AuthCodeDBContext 

AuthCodeDBContext

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

namespace RCR.Web.Demo.API.Models
{
    public class AuthCodeDBContext : DbContext
    {
        public DbSet<AuthCodes> AuthCodes { get; set; }

        public AuthCodeDBContext(DbContextOptions<AuthCodeDBContext> options) : base(options)
        {
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<AuthCodes>().ToTable("AuthCodes");
        }
    }
}

Now let's add the connections string in the configuration file.

Step 6: Add SQLite Connection String in appSettings.json and Startup. cs

 In Solution Explorer, open appsettings.json  the file and add the connection string as below  Data Source=.\\Data\\AccessSQLiteDB.db 

Now it is time to complete the final steps of implementing database operation using a Web API controller.

I create a Web API controller AuthCodeController.cs to include the AuthCodeDBContext instance to create or update the entry to the SQLite database file.

AuthCodeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using RCR.Web.ACRA.API.Models;

namespace RCR.Web.ACRA.API.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class AuthCodeController : ControllerBase
    {
        private readonly AuthCodeDBContext _authCodeDbContext;

        public AuthCodeController(AuthCodeDBContext authCodeDbContext)
        {
            _authCodeDbContext = authCodeDbContext;
        }

        [HttpGet, ActionName("myauth")]
        public IActionResult Callbackurl(string sessionid, string code)
        {
            var entry = new AuthCodes
            {
                SessionId = sessionid,
                AuthCode = code
            };

            var entity = _authCodeDbContext.AuthCodes
                .Where(x => x.SessionId == entry.SessionId)
                .FirstOrDefault();

            if (entity == null)
            {
                _authCodeDbContext.AuthCodes.Add(entry);
            }
            else
            {
                entity.SessionId = entry.SessionId;
                entity.AuthCode = entry.AuthCode;
            }
            _authCodeDbContext.SaveChanges();

            return Ok();
        }
    }
}

The API endpoint Postman test result 

 Summary

In this article, we have learned thoroughly how to use SQLite in ASP.NET Core Web API, Entity Framework Core.

Starting from SQLite Studio installation, added SQLite Compact Visual Studio Tool extension. And then we added an existing database to the project folder, installed the SQLite Entity Framework Core Nuget package, created a model class, model DBContext class.  Finally included Dbcontext instance in Web API Controller added a logic to insert or a record from the user request. 

We also tested web API in the Postman Tool with response 200 status ok.

 How to implement a distributed cache in ASP.NET Core -Redis Database?

 How to send or upload multipart form data to the server using ASP.NET Core?

 How to solve the ServicePointManager Proxy issue in ASP.NET Core?

How to solve the parameter ConnectionString is null in ASP.NET Core?

How to log exceptions in a text log file ASP.NET Core Web API and Application?