.NET 7.0 + Dapper - Connect to SQLite Database in ASP.NET Core
Tutorial built with .NET 7.0, Dapper 2.0 and SQLite
This is a quick post to show how to connect a .NET 7 API to a SQLite database using Dapper and ADO.NET.
The example code is from of a .NET 7 CRUD API tutorial I posted recently, the full project and documentation is available at .NET 7.0 + Dapper + SQLite - CRUD API Tutorial in ASP.NET Core.
Dapper ORM (Object Relational Mapper)
Dapper is a micro ORM that supports executing raw SQL queries and mapping results to C# objects and collections, it's implemented as a set of extension methods on top of the ADO.NET IDbConnection interface, so ADO.NET provides the connection to the database when using Dapper.
SQLite Database
SQLite is a local self-contained relational database that doesn't require a server to run, it's a good option for small applications and for testing. The ADO.NET provider for SQLite (Microsoft.Data.Sqlite) is used to connect to the database with Dapper.
Connect to SQLite with Dapper and ADO.NET
Below is the data context class from the example .NET 7 CRUD API, the CreateConnection() method connects to the SQLite database by creating a new instance of the ADO.NET SqliteConnection() class with the specified connection string. If the database doesn't exist, SQLite automatically creates it when the connection is made.
The Init() method calls the Dapper ExecuteAsync() extension method to create the required SQLite database tables if they don't exist, it is executed once on API startup.
namespace WebApi.Helpers;
using System.Data;
using Dapper;
using Microsoft.Data.Sqlite;
public class DataContext
{
protected readonly IConfiguration Configuration;
public DataContext(IConfiguration configuration)
{
Configuration = configuration;
}
public IDbConnection CreateConnection()
{
return new SqliteConnection("Data Source=LocalDatabase.db");
}
public async Task Init()
{
// create database tables if they don't exist
using var connection = CreateConnection();
await _initUsers();
async Task _initUsers()
{
var sql = """
CREATE TABLE IF NOT EXISTS
Users (
Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Title TEXT,
FirstName TEXT,
LastName TEXT,
Email TEXT,
Role INTEGER,
PasswordHash TEXT
);
""";
await connection.ExecuteAsync(sql);
}
}
}
Need Some .NET Help?
Search fiverr for freelance .NET developers.
Follow me for updates
When I'm not coding...
Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!