How to use Dapper with SQLite in ASP.NET Core

SQLite is a perfect choice for such use cases, providing faster data access and a smaller footprint. It typically stores the database as a single file, but can also operate as an in-memory database, enabling efficient querying without the need to load all data into memory.

Installing Necessary NuGet Packages

To get started, you'll need to install the Dapper and Microsoft.Data.Sqlite NuGet packages. You can add them through the NuGet Package Manager or the Package Manager Console:

PM> Install-Package Dapper
PM> Install-Package Microsoft.Data.Sqlite

Set Up the DbContext for SQLite

In Entity Framework Core (EF Core), the DbContext is used to interact with the database. While we won’t use EF Core’s full capabilities here, we will define a custom context to handle the SQLite connection. Create a class called CustomDbContext:

public class CustomDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite("Data Source=demo.db"); // SQLite connection string
    }

    public DbSet<Author> Authors { get; set; }
}

Create SQLite Database and Table

Next, we will create a SQLite database and a table to store Author data. The SqliteConnection class is used to create the database, and the following SQL creates a table for storing author information:

For example:

var connection = new SqliteConnection("Data Source=demo.db");
await connection.ExecuteAsync(
    @"CREATE TABLE IF NOT EXISTS Author (
        Id INTEGER PRIMARY KEY AUTOINCREMENT,
        FirstName TEXT NOT NULL,
        LastName TEXT NOT NULL,
        Address TEXT NOT NULL)");

Create the Data Context Class

Here’s the CustomDataContext class that manages the connection to the SQLite database and executes SQL queries.

public class CustomDataContext
{
    private SqliteConnection CreateConnection(string dbName)
    {
        return new SqliteConnection($"Data Source={dbName}");
    }

    public async Task CreateDatabaseAsync(string dbName)
    {
        using var connection = CreateConnection(dbName);
        await connection.ExecuteAsync(
            @"CREATE TABLE IF NOT EXISTS Author (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                FirstName TEXT NOT NULL,
                LastName TEXT NOT NULL,
                Address TEXT NOT NULL)");
    }
}

Define the Repository and Interface

In ASP.NET Core, the repository pattern is commonly used for data access. Create the IAuthorRepository interface with methods to perform CRUD operations:

public interface IAuthorRepository
{
    Task<IEnumerable<Author>> GetAllAsync();
    Task<Author> GetByIdAsync(int id);
    Task CreateAsync(Author author);
    Task UpdateAsync(Author author);
    Task DeleteAsync(int id);
}

Then, implement the AuthorRepository class that uses Dapper for SQL queries:

public class AuthorRepository : IAuthorRepository
{
    private readonly CustomDataContext _context;
    private readonly string _databaseName = "demo.db";

    public AuthorRepository(CustomDataContext context)
    {
        _context = context;
    }

    public async Task<IEnumerable<Author>> GetAllAsync()
    {
        using var connection = await _context.CreateDatabaseAsync(_databaseName);
        return await connection.QueryAsync<Author>("SELECT * FROM Author");
    }

    public async Task<Author> GetByIdAsync(int id)
    {
        using var connection = await _context.CreateDatabaseAsync(_databaseName);
        return await connection.QueryFirstOrDefaultAsync<Author>("SELECT * FROM Author WHERE Id = @Id", new { Id = id });
    }

    public async Task CreateAsync(Author author)
    {
        using var connection = await _context.CreateDatabaseAsync(_databaseName);
        await connection.ExecuteAsync("INSERT INTO Author (FirstName, LastName, Address) VALUES (@FirstName, @LastName, @Address)", author);
    }

    public async Task UpdateAsync(Author author)
    {
        using var connection = await _context.CreateDatabaseAsync(_databaseName);
        await connection.ExecuteAsync("UPDATE Author SET FirstName = @FirstName, LastName = @LastName, Address = @Address WHERE Id = @Id", author);
    }

    public async Task DeleteAsync(int id)
    {
        using var connection = await _context.CreateDatabaseAsync(_databaseName);
        await connection.ExecuteAsync("DELETE FROM Author WHERE Id = @Id", new { Id = id });
    }
}

Register Dependencies in ASP.NET Core

In the Program.cs file, register the CustomDataContext and AuthorRepository classes with the dependency injection container:

builder.Services.AddScoped<CustomDataContext>();
builder.Services.AddScoped<IAuthorRepository, AuthorRepository>();

Create HTTP Endpoints for CRUD Operations

Finally, you can define HTTP endpoints for the CRUD operations. Use ASP.NET Core’s routing to map HTTP requests to methods in the repository:

app.MapGet("/authors/{id}", async (int id, IAuthorRepository repository) => await repository.GetByIdAsync(id));
app.MapPut("/authors", async (Author author, IAuthorRepository repository) => await repository.UpdateAsync(author));
app.MapPost("/authors", async (Author author, IAuthorRepository repository) => await repository.CreateAsync(author));
app.MapDelete("/authors/{id}", async (int id, IAuthorRepository repository) => await repository.DeleteAsync(id));

Running the Application

Once the setup is complete, you can run the application and test the CRUD operations by making HTTP requests to the endpoints. For example, a GET request to /authors will display all author records stored in the SQLite database.

SQLite is a great development tool due to its lightweight nature, simplicity, and fast performance. It’s particularly suitable for desktop, mobile, IoT, and embedded applications. However, SQLite is not designed for high-volume, high-concurrency environments, so it’s not ideal for large-scale production systems.