Methods to work with Dapper and SQLite in ASP.NET Core

When growing database-driven .NET and .NET Core, whatever the database we intend to deploy ultimately, we’ll typically need to work with a database engine that’s light-weight and quick, with the intention to execute assessments shortly and pace up improvement. Right here SQLite is a perfect alternative.

You should use SQLite to achieve each quicker information entry and a smaller footprint. The SQLite database is normally saved as a single disk file, though it may additionally work as an in-memory database. Nonetheless, not like an in-memory database, SQLite means that you can question information with out having to load the complete information set in reminiscence.

Dapper—the open-source and light-weight “micro ORM” I’ve written about typically— helps many databases together with SQL Server, MySQL, SQLite, SQL CE, and Firebird. By utilizing Dapper in your purposes, you’ll be able to simplify information entry whereas guaranteeing excessive efficiency. On this article, we’ll look at learn how to work with a SQLite database utilizing Dapper in ASP.NET Core purposes.

To make use of the code examples offered on this article, you need to have Visible Studio 2022 put in in your system. If you happen to don’t have already got a replica, you’ll be able to obtain Visible Studio 2022 right here.

Create an ASP.NET Core Net API venture in Visible Studio 2022

To create an ASP.NET Core Net API venture in Visible Studio 2022, observe the steps outlined under.

1. Launch the Visible Studio 2022 IDE.
2. Click on on “Create new venture.”
3. Within the “Create new venture” window, choose “ASP.NET Core Net API” from the record of templates displayed.
4. Click on Subsequent.
5. Within the “Configure your new venture” window, specify the title and placement for the brand new venture. Optionally verify the “Place answer and venture in the identical listing” verify field, relying in your preferences.
6. Click on Subsequent.
7. Within the “Further Info” window proven subsequent, choose “.NET 8.0 (Lengthy Time period Assist)” because the framework model. Make sure that the verify field that claims “Use controllers” is unchecked since we’ll not be utilizing controllers on this venture.
8. Elsewhere within the “Further Info” window, depart the “Authentication Sort” set to “None” (the default) and ensure the verify packing containers “Allow Open API Assist,” “Configure for HTTPS,” and “Allow Docker” stay unchecked. We gained’t be utilizing any of these options right here.
9. Click on Create.

We’ll use this ASP.NET Core Net API venture to work with the code examples given within the sections under.

Set up the Dapper and SQLite NuGet packages

On this instance, we’ll use Dapper to connect with and work with a SQLite database. Therefore, we’ll want to put in each Dapper and SQLite NuGet packages within the venture. To do that, choose the venture within the Answer Explorer window, then right-click and choose “Handle NuGet Packages.”

Within the NuGet Bundle Supervisor window, seek for the Dapper and Microsoft.Information.Sqlite packages and set up them. Alternatively, you’ll be able to set up the packages by way of the NuGet Bundle Supervisor console by coming into the command under.

PM> Set up-Bundle DapperPM> Set up-Bundle Microsoft.Information.Sqlite

Create a brand new DbContext in EF Core

The DbContext is an integral element of Entity Framework Core that represents a connection session with the database. Create a brand new class named CustomDbContext by extending the DbContext class of EF Core and enter the next code in there.

public class CustomDbContext : DbContext
{
     protected override void OnConfiguring
     (DbContextOptionsBuilder optionsBuilder)
     {
          optionsBuilder.UseInMemoryDatabase(databaseName: "IDGSampleDb");
     }
     public DbSet<Person> Customers { get; set; }
}

Create a SQLite database

To create a brand new SQLite database, all it’s important to do is create a brand new occasion of the SqliteConnection class and cross your title for the database as a parameter to its constructor as proven under.

string databaseName = "demo.db";
_ = new SqliteConnection("Information Supply=" + databaseName);

Notice the utilization of the _ (underscore) literal on the left aspect of the task operator. That is also referred to as a discard and is used to tell the compiler that even when the strategy returns a worth, you do not need to make use of it.

As soon as the SQLite database has been created, you need to use the next code snippet to create a desk within the database.

sqliteConnection.Execute(
    @"create desk Writer
    (
        ID              integer identification main key,
        FirstName       varchar(50) not null,
        LastName        varchar(50) not null,
        Tackle         nvarchar(100) not null
    )");

Determine 1 exhibits the database and its desk created within the DBBrowser of SQLiteStudio.

IDG

The next class named CustomDataContext comprises the entire code for making a SQLite database and a desk inside it.

utilizing Dapper;
utilizing Microsoft.Information.Sqlite;
public class CustomDataContext
{
    personal SqliteConnection CreateDatabaseConnection(string databaseName)
    {
        return new SqliteConnection("Information Supply=" + databaseName);
    }
    public async Activity<SqliteConnection>
    CreateDatabase(string databaseName)
    {
        utilizing (var sqliteConnection =
        CreateDatabaseConnection(databaseName))
        {
            await sqliteConnection.ExecuteAsync(
                @"CREATE TABLE IF NOT EXISTS
                Writer
                (
                    Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    FirstName       TEXT NOT NULL,
                    LastName        TEXT NOT NULL,
                    Tackle         TEXT NOT NULL
                )");
            return sqliteConnection;
        }
    }
}

You possibly can name the CreateDatabase technique of the CustomDataContext class and cross the title of the SQLite database you need to create utilizing the next piece of code.

await new CustomDataContext().CreateDatabase("demo.db");

Create an Writer repository and interface in ASP.NET Core

Allow us to now create a repository, named Writer, that can comprise two varieties: the IAuthorRepository interface and the AuthorRepository class. Every of those varieties will use the Writer class as a mannequin. The supply code of the Writer mannequin class is given under.

public class Writer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Tackle { get; set; }
}

Subsequent, create a brand new interface named IAuthorRepository and exchange the generated code with the next code itemizing.

public interface IAuthorRepository
{
    Activity<IEnumerable<Writer>> GetAll();
    Activity<Writer> GetById(int id);
    Activity Create(Writer Writer);
    Activity Replace(Writer Writer);
    Activity Delete(int id);
}

The AuthorRepository class will implement every of the strategies of the IAuthorRepository interface as proven under.

public class AuthorRepository : IAuthorRepository
{
    personal CustomDataContext _context;
    personal string databaseName = "demo.db";
    public AuthorRepository(CustomDataContext context)
    {
        _context = context;
    }
    public async Activity<IEnumerable<Writer>> GetAll()
    {
        utilizing var connection = await _context.CreateDatabase(databaseName);
        var sql = "SELECT * FROM Writer";
        return await connection.QueryAsync<Writer>(sql);
    }
    public async Activity<Writer> GetById(int id)
    {
        utilizing var sqliteConnection = await
        _context.CreateDatabase(databaseName);
        string sql = "SELECT * FROM Writer WHERE Id = @id";
        return await sqliteConnection.
        QueryFirstOrDefaultAsync<Writer>(sql, new { id });
    }
    public async Activity Create(Writer Writer)
    {
        utilizing var sqliteConnection = await
        _context.CreateDatabase(databaseName);
        string sql = "INSERT INTO Writer (FirstName, LastName, Tackle) " +
            "VALUES (@FirstName, @LastName, @Tackle)";
        await sqliteConnection.ExecuteAsync(sql, Writer);
    }
    public async Activity Replace(Writer Writer)
    {
        utilizing var sqliteConnection = await
        _context.CreateDatabase(databaseName);
        string sql = "UPDATE Writer SET FirstName = @FirstName, " +
            "LastName = @LastName, Tackle = @Tackle WHERE Id = @Id";
        await sqliteConnection.ExecuteAsync(sql, Writer);
    }
    public async Activity Delete(int id)
    {
        utilizing var sqliteConnection = await
        _context.CreateDatabase(databaseName);
        string sql = "DELETE FROM Writer WHERE Id = @id";
        await sqliteConnection.ExecuteAsync(sql, new { id });
    }
}

Register dependencies in ASP.NET Core

Subsequent, you need to register the dependencies with the request processing pipeline with the intention to use dependency injection to create these situations wherever you want them. The next code exhibits how one can register situations of the CustomDataContext and AuthorRepository within the Program.cs file.

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

Create HTTP endpoints in ASP.NET Core

Now you can use the next code to create the endpoints to carry out CRUD (create, learn, replace, delete) operations within the database.

app.MapGet("/authors/{id}", async (int id, IAuthorRepository repository) => await repository.GetById(id));
app.MapPut("/authors", async (Writer creator, IAuthorRepository repository) => await repository.Replace(creator));
app.MapPost("/authors", async (Writer creator, IAuthorRepository repository) => await repository.Create(creator));
app.MapDelete("/authors/{id}", async (int id, IAuthorRepository repository) => await repository.Delete(id));

And that’s all it’s essential to do. Now you can invoke the endpoints as wanted. For instance, you’ll be able to invoke the HTTP Get endpoint /authors to show all creator information within the net browser as proven in Determine 2.

IDG

SQLite is light-weight, quick, and simple to make use of, nevertheless it has sure downsides as nicely. It’s not scalable or appropriate for storing large volumes of knowledge. For giant-scale information storage and high-traffic purposes that serve many concurrent customers, you need to think about a shopper/server database reminiscent of SQL Server or MySQL. However SQLite shines as a improvement companion—and to be used in desktop, cellular, IoT, and embedded purposes.