How to use SQLite and Entity Framework Core in Blazor

Blazor is a modern frontend web framework based on HTML and C# that helps build web applications faster. Developers can develop reusable components that interact seamlessly in order to create beautiful web UIs.

SQLite is a small, fast, and self-contained database engine. It is the most popular SQL engine due to its serverless nature and is used on all mobile phones and computers. Database connectivity is crucial because no application or solution is complete without a data store, so it's possible to insert and retrieve information as needed.

In this tutorial, we will use the code-first approach to integrate SQLite with a Blazor application to design and manage a database directly from the C# code.

dotConnect for SQLite is the ideal data provider for all SQLite-related operations. It offers on-the-fly connector creation and flexible configuration, seamless integration into Visual Studio, and enhanced ORM support.

You'll need for this tutorial:

• Visual Studio 2022: Our IDE of choice will be Visual Studio. If you don't have it on your machine, navigate to the official website to download and install it.
• Blazor: This is a feature of ASP.NET for building interactive web UIs using C# instead of JavaScript. It's real .NET running in the browser on WebAssembly.
• dotConnect for SQLite: A feature-rich ADO.NET provider with Entity Framework, NHibernate, and LinqConnect support.
• Sakila database: A sample database for learning and testing. Download the folder and unzip the file to use it.

On the dotConnect for SQLite download page, you can select either the free trial or the full version of dotConnect for SQLite. The free trial version gives you 30 days to explore all features, but it cannot be used for deploying commercial projects and does not require the license key for activation. If you're ready to purchase, click Buy Now to add the full version to the cart.

Simply download and install dotConnect for SQLite on your machine for the trial version. No license key is required and you can start exploring the product immediately.

After buying the full version, go to your profile's Licenses page. You'll find a list of your licenses, including your dotConnect for SQLite purchase. Click Details next to your dotConnect for SQLite license. Here, you can find your license details with the activation key.

Click Download Product Versions to access the list of available product versions. Select the most recent version and download the installer.

Execute the downloaded file and follow the on-screen instructions to install dotConnect for SQLite on your machine. This process installs the software and activates all features associated with your license key.

To create a Blazor project, in Visual Studio, click Create a new project. In the Search Template field, find Blazor Web App.

Name your project, for example, SQLiteBlazor, and select .NET 8 as the target framework. Then, leave all other options by default and click Create.

Install packages

Next, we're going to install dotConnect for SQLite to the project via the NuGet Package Manager in Visual Studio. In the taskbar, navigate to Tools > NuGet Package Manager and then click Manage NuGet Packages for Solution.

The NuGet Package Manager page opens. Click Browse and search for Devart.Data.SQLite.EFCore. Then, select your project and click Install.

Next, open the Package Manager Console and execute this command to install the necessary EF Core tools required for the application:

Install-Package Microsoft.EntityFrameworkCore.Tools

To open the scaffolded web page, run the application by pressing F5.

Set up an SQLite test database

To configure the database, rename the sakila.db file to devartblazor.db. Then, create the devart_blazor_db folder within your project and move the devartblazor.db file into this folder.

Create a connection instance

Firstly, create a new folder called Models and add the Actor class.

public class Actor
{
    [Key]
    public int ActorId { get; set; }
    [Required]
    public string FirstName { get; set; }
    [Required]
    public string LastName { get; set; }
    [Required]
    public DateTime LastUpdate { get; set; }

}

Next, it's required to scaffold our DbContext. In the Models folder, create a new class called ActorDbContext and copy this code into it:

public class ActorDbContext : DbContext
{
    public ActorDbContext(DbContextOptions options) : base(options)
    {

    }   
    public DbSet Actors { get; set; }
}

The project should look as follows.

To create a table based on the defined DbContext, it's necessary to configure a few things first. In your Blazor project, create the DataBaseConfiguration class and copy the following code into it:

public static class DatabaseConfiguration
    {
        public static string GetConnectionString()
        {
            string databasePath = "devartblazor.db";
            string licenseKey = "your-license-key";
            return $"DataSource={databasePath};FailIfMissing=False;
            License Key={licenseKey};";
        }
    }

DatabaseConfiguration has a singular method GetConnectionString, which returns a concatenated string of the file path to your SQLite database and the license key for your dotConnect for SQLite (you can find this on the customer portal). For more details on connection strings, you can refer to .NET SQLite Connection Strings.

This approach is for tutorial purposes only; ideally, you should store your activation key in a more secure manner, such as using Environment Secrets.

Next, create the MyDbContextFactory class and add such a code:

public class MyDbContextFactory : IDesignTimeDbContextFactory
 {
     public ActorDbContext CreateDbContext(string[] args)
     {
         var optionsBuilder = new DbContextOptionsBuilder();
         string connectionString = DatabaseConfiguration.GetConnectionString();
         optionsBuilder.UseSQLite(connectionString);

         return new ActorDbContext(optionsBuilder.Options);
     }
}

MyDbContextFactory is required to implement the IDesignTimeDbContextFactory<ActorDbContext> interface. This interface generates instances of our DbContext (ActorDbContext) during the design stage. So far, your project should look like this.

Add the following two lines of code in Program.cs to ensure your project references the correct database string:

string connectionString = DatabaseConfiguration.GetConnectionString();
builder.Services.AddDbContext(options =>
    options.UseSQLite(connectionString));

Now that everything is set up, let's create the first migration. In the Package Manager Console, run this code to generate the Migrations folder in the project based on the current state of our ActorDbContext:

Add-Migration InitialCreate -Context ActorDbContext

To finish this process and create the Actor table, we'll have to update the database. For this, execute this command in the Package Manager Console as well:

Update-Database

If all goes well, you should receive a response stating, Applying migration '20240903110733_InitialCreate'. It means the migration is applied and the table is created.

Before moving on, let's create the Services folder that will serve as the central hub for most of the business logic. In the folder, create the IActorService interface and paste this code:

public interface IActorService
 {
     Task<List<Actor>> GetActorsAsync();
     Task GetActorByIdAsync(int actorId);
     Task AddActorAsync(Actor actor);
     Task UpdateActorAsync(Actor actor);
     Task DeleteActorAsync(int actorId);
 }

Additionally, copy the following code into the folder to create a new class that will establish the ActorService interface:

public class ActorService : IActorService
{
    private readonly ActorDbContext _context;

    public ActorService(ActorDbContext context)
    {
        _context = context;
    }

    public async Task<List<Actor>> GetActorsAsync()
    {
        return await _context.Actors.ToListAsync();
    }

    public async Task GetActorByIdAsync(int actorId)
    {
        var actor = await _context.Actors.FindAsync(actorId);
        if (actor == null)
        {
            throw new Exception("Actor not found");
        }
        return actor;
    }

    public async Task AddActorAsync(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Actors.Add(actor);
        await _context.SaveChangesAsync();
    }

    public async Task UpdateActorAsync(Actor actor)
    {
        actor.LastUpdate = DateTime.Now;
        _context.Entry(actor).State = EntityState.Modified;
        await _context.SaveChangesAsync();
    }

    public async Task DeleteActorAsync(int actorId)
    {
        var actor = await _context.Actors.FindAsync(actorId);
        if (actor != null)
        {
            _context.Actors.Remove(actor);
            await _context.SaveChangesAsync();
        }
    }
}

Then, ensure to register both the interface and its implementation by adding this code to Program.cs:

builder.Services.AddScoped();

At this stage, your project should appear as follows:

Now, let's try to insert some data into the Actor table. From your project, navigate to the Components > Pages folders and click Razor Component.

Name this component Actor.Razor. On the Actor.Razor page, paste this piece of code to add the button that navigates to the add-actor page:

@page "/actor"
@rendermode InteractiveServer
@inject NavigationManager Navigation

<h3>Actor</h3>

<button class="btn btn-primary" @onclick="OpenAddActorModal">Add New Actor</button>

@code {
    private void OpenAddActorModal()
    {
        Navigation.NavigateTo("/add-actor");
    }
}

After that, we can create the add-actor page. From the Pages folder under components, add another Razor component called AddActor.Razor and copy this piece of code into it:

@page "/add-actor"
@rendermode InteractiveServer
@using SQLiteBlazor.Models
@inject SQLiteBlazor.Services.IActorService ActorService
@inject NavigationManager Navigation

<h3>Add New Actor</h3>

<div class="mb-3">
    <label for="firstName" class="form-label">First Name</label>
    <input type="text" class="form-control" id="firstName" @bind="newFirstName" />
</div>
<div class="mb-3">
    <label for="lastName" class="form-label">Last Name</label>
    <input type="text" class="form-control" id="lastName" @bind="newLastName" />
</div>
<button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
<button class="btn btn-primary" @onclick="InsertActor">Save</button>

@code {
    private string newFirstName;
    private string newLastName;

    private async Task InsertActor()
    {
        var newActor = new Models.Actor
            {
                FirstName = newFirstName,
                LastName = newLastName,
                LastUpdate = DateTime.UtcNow
            };
        await ActorService.AddActorAsync(newActor);

        Navigation.NavigateTo("/actor");
    }

    private void Cancel()
    {
        // Close the modal and navigate back to the actor page
        Navigation.NavigateTo("/actor");
    }
}

Don't forget to add the previous Actor component to the Nav bar. Navigate to the Components > Layouts folders and select NavMenu.Razor. Replace the code with this one:

<div class="top-row ps-3 navbar navbar-dark">
    <div class="container-fluid">
        <a class="navbar-brand" href="">SQLiteBlazor</a>
    </div>
</div>

<input type="checkbox" title="Navigation menu" class="navbar-toggler" />

<div class="nav-scrollable" onclick="document.querySelector('.navbar-toggler').click()">
    <nav class="flex-column">
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="bi bi-house-door-fill-nav-menu" aria-hidden="true"></span> Home
            </NavLink>
        </div>
        <div class="nav-item px-3">
            <NavLink class="nav-link" href="actor">
                <span class="bi bi-list-nested-nav-menu" aria-hidden="true"></span> Actors
            </NavLink>
        </div>

    </nav>
</div>

Run the application and go to Actors > Add New Actor.

After you have added a new actor and clicked Save, you will be redirected to the Actor page.

Let's modify the application to view the created actor's record. Paste the code in Actor.Razor:

@page "/actor"
@rendermode InteractiveServer
@inject NavigationManager Navigation
@inject SQLiteBlazor.Services.IActorService ActorService

<h3>Actor Table</h3>

@if (actors == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <table class="table">
        <thead>
            <tr>
                <th>ID</th>
                <th>First Name</th>
                <th>Last Name</th>
                <th>Last Update</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var actor in actors)
            {
                <tr>
                    <td>@actor.ActorId</td>
                    <td>@actor.FirstName</td>
                    <td>@actor.LastName</td>
                    <td>@actor.LastUpdate</td>
                </tr>
            }
        </tbody>
    </table>
}

<button class="btn btn-primary" @onclick="OpenAddActorModal">Add New Actor</button>

@code {
    private List<Models.Actor> actors;

    protected override async Task OnInitializedAsync()
    {
        // Load the actors when the component is initialized
        actors = await ActorService.GetActorsAsync();
    }


    private void OpenAddActorModal()
    {
        Navigation.NavigateTo("/add-actor");
    }
}

To see the actor's record, run the application and click Actors.

Suppose you want to change Seth's last name. The application should be able to handle this request. From the Pages folder, create a new Razor component called Update.Razor and insert the following code to the file:

@page "/update/{actorId:int}"
@rendermode InteractiveServer
@using SQLiteBlazor.Models
@inject SQLiteBlazor.Services.IActorService ActorService
@inject NavigationManager Navigation

<h3>Update Actor</h3>

@if (actor == null)
{
    <p><em>Loading...</em></p>
}
else
{
    <div class="mb-3">
        <label for="firstName" class="form-label">First Name</label>
        <input type="text" class="form-control" id="firstName" @bind="actor.FirstName" />
    </div>
    <div class="mb-3">
        <label for="lastName" class="form-label">Last Name</label>
        <input type="text" class="form-control" id="lastName" @bind="actor.LastName" />
    </div>
    <button class="btn btn-secondary" @onclick="Cancel">Cancel</button>
    <button class="btn btn-primary" @onclick="UpdateActor">Save</button>
}

@code {
    [Parameter]
    public int actorId { get; set; }

    private Models.Actor actor;

    protected override async Task OnInitializedAsync()
    {
        // Load the actor to be updated
        actor = await ActorService.GetActorByIdAsync(actorId);
    }

    private async Task UpdateActor()
    {
        // Update the actor using the service
        await ActorService.UpdateActorAsync(actor);

        // Redirect back to the actor list page
        Navigation.NavigateTo("/actor");
    }

    private void Cancel()
    {
        // Navigate back to the actor list page
        Navigation.NavigateTo("/actor");
    }
}

To be able to go to the created page with the particular Actor ID, in Actor.Blazor, edit the Table element in the else block with this code:

<table class="table">
    <thead>
        <tr>
            <th>ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Last Update</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var actor in actors)
        {
            <tr>
                <td>@actor.ActorId</td>
                <td>@actor.FirstName</td>
                <td>@actor.LastName</td>
                <td>@actor.LastUpdate</td>
                <td>
                    <button class="btn btn-warning btn-sm" @onclick="() => NavigateToUpdate(actor.ActorId)">Update</button>
                </td>
            </tr>
        }
    </tbody>
</table>
Then in the @code Block, add this method;
private void NavigateToUpdate(int actorId)
{
    Navigation.NavigateTo($"/update/{actorId}");
}

Run the application and navigate to the Actor page.

To modify the actor's data, click Update. After updating, click Save.

Let's try to remove the actor's record from the database. For reference, we added a new actor Johnny Blaze to be deleted. On the Actor.Razor page, add this line of the code to the table under the update button:

<button class="btn btn-danger btn-sm" @onclick="() => DeleteActor(actor.ActorId)">Delete</button>

Then, paste this method to the @code block:

private async Task DeleteActor(int actorId)
{
    await ActorService.DeleteActorAsync(actorId);
    actors = await ActorService.GetActorsAsync();
}

This method takes that particular Actor ID and calls the DeleteActorAsync service. Run the application and go to the
Actor page.

To remove the actor's record, click Delete.

Video tutorial: How to connect .NET Blazor application to SQLite database

In this tutorial, we covered how to connect a Blazor application to an SQLite database using dotConnect for SQLite. Additionally, we demonstrated how to create tables, insert data, read from tables, update records, and delete records. As you can see, SQLite used in combination with Entity Framework Core in a Blazor application offers a powerful and efficient way to manage local databases, especially for small-scale projects or mobile environments. Utilizing SQLite as the database alongside Entity Framework Core makes it easier for developers to handle data and keep it stored in Blazor applications.