Blazor is a modern web framework that uses HTML and C# to build interactive web applications. One of its biggest advantages is the ability to create reusable components that make building complex user interfaces easier.
SQLite is a lightweight and fast database engine that requires no separate server installation. Because it is simple, reliable, and portable, it is widely used on mobile devices and desktop applications.
When building applications, connecting the user interface to a database is essential since applications rely on stored data to function properly. This tutorial will demonstrate how to integrate SQLite with a Blazor application and manage databases directly from C# code.
Provides integrated support for the SQLite Encryption Extension with AES, Blowfish, TripleDES, Cast128, and RC4 encryption algorithms.
Fully supports EF Core, Dapper, NHibernate, LinqConnect, and more for efficient data management.
Conforms to ADO.NET standards for seamless integration with .NET applications.
Includes specific features and fully supports all unique data types for accurate and complete data representation.
Features native integration with Visual Studio and complete design-time support for accelerated development.
Includes priority support, detailed documentation, and regular updates for continuous improvement.
You can start using dotConnect for SQLite immediately with a 30-day free trial. Choose one of the following options:
First of all, we connect to SQLite using the built-in Data Explorer in Visual Studio.
1. Move the sakila database to the project folder, then open Tools and select Connect to Database.
2. Choose SQLite as the data source and click Continue. Click Browse to select the database file and click OK.
Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.
Open the appsettings.json file and add your SQLite connection string:
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=..\\sakila.db;License Key=**********"
},
"Logging": {
// ...
}
}In this section, we are going to establish a connection to the SQLite database and display data in a read-only table. This ensures our database connection and data service are correctly configured and provides the base upon which we will build the other CRUD operations.
In your project, create a new folder named Data. Inside it, create a class file named Actor.cs. This class will act as a model to hold the data for each actor record.
// Data/Actor.cs
namespace SQLiteBlazor.Data;
public class Actor
{
public int ActorId { get; set; }
public string FirstName { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;
public DateTime LastUpdate { get; set; }
}In the Data folder, create a new class ActorService.cs. This service will contain the logic to interact with the database. Note that we will use dependency injection to provide this service to our components, so the class is not static.
The connection string uses the Data Source key pointing to the .db file path. The License Key is required for the Devart provider.
// Data/ActorService.cs
using Devart.Data.SQLite;
namespace SQLiteBlazor.Data;
public class ActorService
{
private readonly string _connectionString;
public ActorService(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection")!;
}
public async Task<List<Actor>> GetAllActorsAsync()
{
var actors = new List<Actor>();
using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
using var command = new SQLiteCommand(
"SELECT actor_id, first_name, last_name, last_update FROM actor ORDER BY actor_id",
connection);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
actors.Add(new Actor
{
ActorId = reader.GetInt32(reader.GetOrdinal("actor_id")),
FirstName = reader.GetString(reader.GetOrdinal("first_name")),
LastName = reader.GetString(reader.GetOrdinal("last_name")),
LastUpdate = reader.GetDateTime(reader.GetOrdinal("last_update"))
});
}
return actors;
}
}In Program.cs, register the ActorService as a scoped service so it can be injected into our components.
// Program.cs
using SQLiteBlazor.Components;
using SQLiteBlazor.Data;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddRazorComponents()
.AddInteractiveServerComponents();
// Register the ActorService
builder.Services.AddScoped<ActorService>();
var app = builder.Build();
// ...In the Components/Pages folder, create a new Razor Component named Actors.razor. This page will fetch and display the data.
@page "/actors"
@rendermode InteractiveServer
@using SQLiteBlazor.Data
@inject ActorService ActorSvc
<PageTitle>Actors</PageTitle>
<h1>Actor List</h1>
@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.ToString("yyyy-MM-dd HH:mm")</td>
</tr>
}
</tbody>
</table>
}
@code {
private List<Actor>? actors;
protected override async Task OnInitializedAsync()
{
actors = await ActorSvc.GetAllActorsAsync();
}
}In Components/Layout/NavMenu.razor, add a link to your new page.
<div class="nav-item px-3">
<NavLink class="nav-link" href="actors">
<span class="bi bi-person-fill-gear" aria-hidden="true"></span> Actors
</NavLink>
</div>We have successfully built a read-only Blazor application that connects to a SQLite database and displays the data in a table. This confirms that your core setup, including dependency injection and data retrieval, is working correctly.
Our next task is to add the ability to create new records. This involves adding an EditForm to the UI and implementing the corresponding logic to execute an INSERT SQL command.
SQLite uses strftime('%Y-%m-%d %H:%M:%S', 'now') as the equivalent of MySQL's NOW() for the current timestamp.
Modify Actors.razor to add a dedicated EditForm for creating new actors.
@page "/actors"
@rendermode InteractiveServer
@using SQLiteBlazor.Data
@inject ActorService ActorSvc
@inject IJSRuntime JSRuntime
<PageTitle>Actors</PageTitle>
<h1>Actors</h1>
<h3>Add New Actor</h3>
<EditForm Model="@newActor" OnValidSubmit="HandleInsert" FormName="NewActorForm">
<DataAnnotationsValidator />
<ValidationSummary />
<div class="mb-3">
<label for="newFirstName" class="form-label">First Name</label>
<InputText id="newFirstName" @bind-Value="newActor.FirstName" class="form-control" />
</div>
<div class="mb-3">
<label for="newLastName" class="form-label">Last Name</label>
<InputText id="newLastName" @bind-Value="newActor.LastName" class="form-control" />
</div>
<button type="submit" class="btn btn-primary">Insert Actor</button>
</EditForm>
<hr />
Add the InsertActorAsync method to your ActorService.cs file.
Note the SQLite specificities that are different in comparison to MySQL:
@param_name prefix (not :param_name)strftime('%Y-%m-%d %H:%M:%S', 'now')// Data/ActorService.cs
// ... (add this async method inside the ActorService class)
public async Task InsertActorAsync(Actor actor)
{
using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
using var command = new SQLiteCommand(
"INSERT INTO actor (first_name, last_name, last_update) " +
"VALUES (@first_name, @last_name, strftime('%Y-%m-%d %H:%M:%S', 'now'))",
connection);
command.Parameters.AddWithValue("@first_name", actor.FirstName);
command.Parameters.AddWithValue("@last_name", actor.LastName);
await command.ExecuteNonQueryAsync();
}Modify the @code block in Actors.razor to handle the submission form for adding new actors.
private List<Actor>? actors;
private Actor newActor = new();
private Actor actorToEdit = new();
protected override async Task OnInitializedAsync()
{
await LoadActors();
}
private async Task LoadActors()
{
actors = await ActorSvc.GetAllActorsAsync();
}
private async Task HandleInsert()
{
await ActorSvc.InsertActorAsync(newActor);
newActor = new(); // Clear the form after insert
await LoadActors(); // Refresh the list
}
// HandleUpdate, SetActorForUpdate, and ClearForm methods will be added in Step 3
}
Therefore, our application can add new records to the database. Users can input data into the form, click the Insert Actor button, and see the table update immediately with the new entry.
We also need to implement the ability to edit existing records. This requires adding Edit buttons to our table and setting the logic to populate the form for editing. We will reuse the same EditForm.
Modify Actors.razor to include a conditional EditForm for updating actors and add Edit and Delete buttons to each row in the table.
<!-- ... (Existing 'Add New Actor' form and <hr />) ... -->
@if (actorToEdit.ActorId != 0)
{
<h3>Edit Actor</h3>
<EditForm Model="@actorToEdit" OnValidSubmit="HandleUpdate" FormName="EditActorForm">
<DataAnnotationsValidator />
<ValidationSummary />
<div class="mb-3">
<label for="editFirstName" class="form-label">First Name</label>
<InputText id="editFirstName" @bind-Value="actorToEdit.FirstName" class="form-control" />
</div>
<div class="mb-3">
<label for="editLastName" class="form-label">Last Name</label>
<InputText id="editLastName" @bind-Value="actorToEdit.LastName" class="form-control" />
</div>
<button type="submit" class="btn btn-success">Save</button>
<button type="button" class="btn btn-secondary" @onclick="ClearForm">Cancel</button>
</EditForm>
<hr />
}
@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>
<th></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.ToString("yyyy-MM-dd HH:mm")</td>
<td>
<button class="btn btn-sm btn-warning" @onclick="() => SetActorForUpdate(actor)">Edit</button>
<button class="btn btn-sm btn-danger" @onclick="() => HandleDelete(actor)">Delete</button>
</td>
</tr>
}
</tbody>
</table>
}
Add the UpdateActorAsync method to ActorService.cs.
// Data/ActorService.cs
// ... (add this async method inside the ActorService class)
public async Task UpdateActorAsync(Actor actor)
{
using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
using var command = new SQLiteCommand(
"UPDATE actor SET first_name = @first_name, last_name = @last_name, " +
"last_update = strftime('%Y-%m-%d %H:%M:%S', 'now') " +
"WHERE actor_id = @actor_id",
connection);
command.Parameters.AddWithValue("@first_name", actor.FirstName);
command.Parameters.AddWithValue("@last_name", actor.LastName);
command.Parameters.AddWithValue("@actor_id", actor.ActorId);
await command.ExecuteNonQueryAsync();
}Update the @code block in Actors.razor to manage the state for both inserting and updating.
private List<Actor>? actors;
private Actor newActor = new();
private Actor actorToEdit = new();
protected override async Task OnInitializedAsync()
{
await LoadActors();
}
private async Task LoadActors()
{
actors = await ActorSvc.GetAllActorsAsync();
}
private async Task HandleInsert()
{
await ActorSvc.InsertActorAsync(newActor);
newActor = new();
await LoadActors();
}
private void SetActorForUpdate(Actor actor)
{
// Create a copy to avoid modifying the list directly
actorToEdit = new Actor
{
ActorId = actor.ActorId,
FirstName = actor.FirstName,
LastName = actor.LastName
};
}
private async Task HandleUpdate()
{
await ActorSvc.UpdateActorAsync(actorToEdit);
ClearForm();
await LoadActors();
}
private void ClearForm()
{
actorToEdit = new();
}
// HandleDelete method will be added in Step 4
}
After performing these steps, the application becomes able to support editing data. Users can click Edit on an actor, change their details in the form, and save the changes back to the database.
Finally, we implement the deletion functionality. This involves adding a Delete button and using Blazor's JavaScript Interop feature to show a confirmation dialog.
We have already added the Delete button. It will call HandleDelete(actor) which we are going to implement now.
<!-- ... (Inside the table's foreach loop) ... -->
<td>
<button class="btn btn-sm btn-warning" @onclick="() => SetActorForUpdate(actor)">Edit</button>
<button class="btn btn-sm btn-danger" @onclick="() => HandleDelete(actor)">Delete</button>
</td>
Add the DeleteActorAsync method to ActorService.cs.
// Data/ActorService.cs
// ... (add this async method inside the ActorService class)
public async Task DeleteActorAsync(int actorId)
{
using var connection = new SQLiteConnection(_connectionString);
await connection.OpenAsync();
using var command = new SQLiteCommand(
"DELETE FROM actor WHERE actor_id = @actor_id",
connection);
command.Parameters.AddWithValue("@actor_id", actorId);
await command.ExecuteNonQueryAsync();
}Add the HandleDelete method to the @code block. This method uses JavaScript interop to ask the user for confirmation before deleting.
private List<Actor>? actors;
private Actor newActor = new();
private Actor actorToEdit = new();
protected override async Task OnInitializedAsync()
{
await LoadActors();
}
private async Task LoadActors()
{
actors = await ActorSvc.GetAllActorsAsync();
}
private async Task HandleInsert()
{
await ActorSvc.InsertActorAsync(newActor);
newActor = new();
await LoadActors();
}
private void SetActorForUpdate(Actor actor)
{
actorToEdit = new Actor
{
ActorId = actor.ActorId,
FirstName = actor.FirstName,
LastName = actor.LastName
};
}
private async Task HandleUpdate()
{
await ActorSvc.UpdateActorAsync(actorToEdit);
ClearForm();
await LoadActors();
}
private void ClearForm()
{
actorToEdit = new();
}
private async Task HandleDelete(Actor actor)
{
bool confirmed = await JSRuntime.InvokeAsync<bool>(
"confirm",
$"Are you sure you want to delete {actor.FirstName} {actor.LastName}?");
if (confirmed)
{
await ActorSvc.DeleteActorAsync(actor.ActorId);
await LoadActors();
}
}
}
We have completed the creation of our Blazor application with full CRUD functionality to read, create, update, and delete records in a SQLite database through a modern web interface.
In this tutorial, we demonstrated how to connect a Blazor application to an SQLite database and perform basic data operations, including retrieving, updating, inserting, and deleting records.
From here, you can continue building agile, data-driven applications that integrate with SQLite. dotConnect for SQLite can help by providing direct data access and efficient data manipulation within your application. Try it with a fully functional free trial to see how it can simplify many everyday development tasks.
Scaffold-DbContext with a dotConnect connection string (including License Key) to generate the DbContext and entity classes.
I'm a technical content writer who loves breaking complex tech topics into clear and helpful content that's enjoyable to read. With a solid writing background and growing skill in software development and database tools, I create content that's accurate, easy to follow, and genuinely useful. When I'm not writing, you'll probably find me learning something new or sweating it out at the gym.
