You can install the driver by using the Windows installer.
After you receive the license key, add it to your connection strings to connect to the data source.
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.
Why dotConnect for SQLite?
Advanced encryption
Provides integrated support for the SQLite Encryption Extension with AES, Blowfish, TripleDES, Cast128, and RC4 encryption algorithms.
Advanced ORM Support
Fully supports EF Core, Dapper, NHibernate, LinqConnect, and more for efficient data management.
Full ADO.NET Compliance
Conforms to ADO.NET standards for seamless integration with .NET applications.
SQLite-specific data types
Includes specific features and fully supports all unique data types for accurate and complete data representation.
IDE integration
Features native integration with Visual Studio and complete design-time support for accelerated development.
Support & frequent updates
Includes priority support, detailed documentation, and regular updates for continuous improvement.
Download and activate dotConnect for SQLite
You can start using dotConnect for SQLite immediately with a 30-day free trial. Choose one of the following options:
30-day free trial version
Check SQLite database objects
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.
Connection setup
Open the appsettings.json file and add your SQLite connection string:
{
"ConnectionStrings": {
"DefaultConnection": "Data Source=..\\sakila.db;License Key=**********"
},
"Logging": {
// ...
}
}Connect and retrieve SQLite data
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.
Create the data model (Actor.cs)
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; }
}Create the data service (ActorService.cs)
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;
}
}Register the service
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();
// ...Create the Actors page (Actors.razor)
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();
}
}Add navigation link
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.
Insert SQLite data
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.
Update the UI (Actors.razor)
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 />
Update the data service (ActorService.cs)
Add the InsertActorAsync method to your ActorService.cs file.
Note the SQLite specificities that are different in comparison to MySQL:
- Parameters use
@param_nameprefix (not:param_name) - The current timestamp is obtained via
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();
}Update the UI logic (Actors.razor)
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.
Update SQLite data
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.
Update the UI (Actors.razor)
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>
}
Update the data service (ActorService.cs)
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 UI logic (Actors.razor)
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.
Delete SQLite data
Finally, we implement the deletion functionality. This involves adding a Delete button and using Blazor's JavaScript Interop feature to show a confirmation dialog.
Update the UI (Actors.razor)
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>
Update the data service (ActorService.cs)
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();
}Update the UI logic (Actors.razor)
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.
Video tutorial
Conclusion
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.
FAQ
How do you install and activate dotConnect for SQLite in a .NET project?
Install dotConnect for SQLite either by running the Windows installer (EXE) or by adding the Devart.Data.SQLite NuGet package to your .NET project. Then, retrieve your activation key from the Devart Customer Portal and specify it in your connection string by using the License Key parameter to activate the provider and connect successfully.
How do you create a connection to SQLite using dotConnect in C#?
Define a connection string that includes host, user ID, password, database, and the License Key value, then create an SQLiteConnection instance with this string and call Open() inside a TRY-CATCH block to test and handle connection errors.
How do you enable encryption for secure SQLite connections with dotConnect?
Use SQLite Native Network Encryption by configuring encryption in the sqlnet.ora file on both client and server sides. No special parameters are required in the connection string.
Can you connect to SQLite using Entity Framework Core and dotConnect?
Yes, you can either use Entity Developer to visually create an EF Core model from the database or run
Scaffold-DbContext with a dotConnect connection string (including License Key) to generate the DbContext and entity classes.
Is it possible to connect to SQLite using Visual Studio Server Explorer with dotConnect?
Yes, add a new Data Connection in Server Explorer, select dotConnect for SQLite as the provider, enter your credentials, test the connection, and browse SQLite data directly in Visual Studio.
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.
Try the 30-day trial of the full product. No limits. No card required.
Start free trial