Published: February 10 2023

.NET 7.0 + Dapper - Create Database Tables on Startup in ASP.NET Core

Tutorial built with .NET 7.0, Dapper 2.0 and SQLite

I recently built a .NET 7 API that uses the Dapper micro ORM to connect to a SQLite database. Dapper is a lightweight ORM that doesn't include database migrations so I needed to come up with another solution for auto generating the database tables on API startup.

I decided to keep things simple and add a custom Init() method to my DataContext class and call it once on app startup, the method executes some SQL to setup the SQLite database tables if they don't exist.

Below are the pieces of code from the API project that are responsible for creating the database tables on startup. The full project and documentation is available at .NET 7.0 + Dapper + SQLite - CRUD API Tutorial.

SQLite Data Context

Path: /Helpers/DataContext.cs

The data context class is used to connect the SQLite database with ADO.NET and return an IDbConnection instance.

The Init() method creates the SQLite database tables if they don't exist, it is executed once on API startup from the Program.cs file.

The Dapper ExecuteAsync() extension method is called to execute SQL statements on the database.

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(Configuration.GetConnectionString("WebApiDatabase"));
    }

    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);
        }
    }
}

.NET 7 Program

Path: /Program.cs

The .NET 7 Program file configures dependency injection, initializes the SQLite database, configures the HTTP request pipeline and starts the API.

SQLite database initialization

A new SQLite database is automatically created (if it doesn't exist) simply by connecting to it. The Init() method of the data context is executed once on startup to ensure that all the required tables exist inside the SQLite database.

using System.Text.Json.Serialization;
using WebApi.Helpers;
using WebApi.Repositories;
using WebApi.Services;

var builder = WebApplication.CreateBuilder(args);

// add services to DI container
{
    var services = builder.Services;
    var env = builder.Environment;
 
    services.AddSingleton<DataContext>();
    services.AddCors();
    services.AddControllers().AddJsonOptions(x =>
    {
        // serialize enums as strings in api responses (e.g. Role)
        x.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());

        // ignore omitted parameters on models to enable optional params (e.g. User update)
        x.JsonSerializerOptions.DefaultIgnoreCondition = JsonIgnoreCondition.WhenWritingNull;
    });
    services.AddAutoMapper(AppDomain.CurrentDomain.GetAssemblies());

    // configure DI for application services
    services.AddScoped<IUserRepository, UserRepository>();
    services.AddScoped<IUserService, UserService>();
}

var app = builder.Build();

// ensure database and tables exist
{
    using var scope = app.Services.CreateScope();
    var context = scope.ServiceProvider.GetRequiredService<DataContext>();
    await context.Init();
}

// configure HTTP request pipeline
{
    // global cors policy
    app.UseCors(x => x
        .AllowAnyOrigin()
        .AllowAnyMethod()
        .AllowAnyHeader());

    // global error handler
    app.UseMiddleware<ErrorHandlerMiddleware>();

    app.MapControllers();
}

app.Run("http://localhost:4000");

Need Some .NET Help?

Search fiverr for freelance .NET developers.

Follow me for updates

On Twitter or RSS.

When I'm not coding...

Me and Tina are on a motorcycle adventure around Australia.
Come along for the ride!

More .NET Posts