Published: February 14 2023

Dapper + SQL Server - Create database if it doesn't exist on startup in ASP.NET Core

Tutorial built with .NET 7.0, Dapper 2.0 and MS SQL Server

I recently built a .NET 7 API that uses the Dapper micro ORM to connect to a SQL Server 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 SQL Server database and tables if they don't exist.

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

SQL Server Data Context

Path: /Helpers/DataContext.cs

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

The Init() method creates the SQL Server database and 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.SqlClient;
using Microsoft.Extensions.Options;

public class DataContext
{
    private DbSettings _dbSettings;

    public DataContext(IOptions<DbSettings> dbSettings)
    {
        _dbSettings = dbSettings.Value;
    }

    public IDbConnection CreateConnection()
    {
        var connectionString = $"Server={_dbSettings.Server}; Database={_dbSettings.Database}; User Id={_dbSettings.UserId}; Password={_dbSettings.Password};";
        return new SqlConnection(connectionString);
    }

    public async Task Init()
    {
        await _initDatabase();
        await _initTables();
    }

    private async Task _initDatabase()
    {
        // create database if it doesn't exist
        var connectionString = $"Server={_dbSettings.Server}; Database=master; User Id={_dbSettings.UserId}; Password={_dbSettings.Password};";
        using var connection = new SqlConnection(connectionString);
        var sql = $"IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '{_dbSettings.Database}') CREATE DATABASE [{_dbSettings.Database}];";
        await connection.ExecuteAsync(sql);
    }

    private async Task _initTables()
    {
        // create tables if they don't exist
        using var connection = CreateConnection();
        await _initUsers();

        async Task _initUsers()
        {
            var sql = """
                IF OBJECT_ID('Users', 'U') IS NULL
                CREATE TABLE Users (
                    Id INT NOT NULL PRIMARY KEY IDENTITY,
                    Title NVARCHAR(MAX),
                    FirstName NVARCHAR(MAX),
                    LastName NVARCHAR(MAX),
                    Email NVARCHAR(MAX),
                    Role INT,
                    PasswordHash NVARCHAR(MAX)
                );
            """;
            await connection.ExecuteAsync(sql);
        }
    }
}

.NET 7 Program

Path: /Program.cs

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

SQL Server database initialization

The Init() method of the data context is executed once on startup to ensure the SQL Server database and all required tables exist.

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.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 strongly typed settings object
    services.Configure<DbSettings>(builder.Configuration.GetSection("DbSettings"));

    // configure DI for application services
    services.AddSingleton<DataContext>();
    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